simon
2012-01-06 11:34:17 UTC
I have problems with blocked processes.
I have fact table, which is calculated (dropped and recreated) every
time, user clicks some button.
Calculation takes about 1 second.
So, if user clicks on button, the following happened:
DECLARE @result INT
BEGIN TRANSACTION
EXEC @result = sp_getapplock @Resource = 'Fact', @LockMode =
'Exclusive';
--here I check if more users clicks on this button at the same time
--if so, only first user recalculate fact table(execute calculateFact
table), others waits on result
if @result=0
BEGIN
EXEC dbo.p_calculateFact
EXEC @result = sp_releaseapplock @Resource = 'Fact';
END
COMMIT TRANSACTION
SELECT ...... FROM dbo.tblFact WHERE ....
In procedure dbo.p_recalculateFact I do the following:
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE xtype='U' AND
NAME='fact_tmp')
DROP TABLE rdr.fact_tmp
SELECT col1, col2, ..... INTO rdr.fact_tmp
FROM tbl1 JOIN tbl2 JOIN tbl3 ......
WHERE ................
--I'm using drop and select into some fact_tmp table to achieve
minimal logging
--and also while fact_tmp is calculating all users from other
processes can do the reading from tblFact table
When fact_tmp is created, I drop fact table:
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE xtype='U' AND
NAME='tblFact')
DROP TABLE rdr.tblFact
Than I rename tmp table to fact table:
DECLARE @sql NVARCHAR(100)
SET @sql=N'sp_RENAME ''rdr.fact_tmp'' , ''tblFact'''
EXEC(@sql)
When I test this button with many clicks, everything works. But when
on production I get blocked processes every day on peak-times.
What should I change here? Or any suggestion how to remove blocked
processes?
I have also READ_COMMITTED_SNAPSHOT ON in this database to reduce
locks. Database is SQL2008.
br, Simon
I have fact table, which is calculated (dropped and recreated) every
time, user clicks some button.
Calculation takes about 1 second.
So, if user clicks on button, the following happened:
DECLARE @result INT
BEGIN TRANSACTION
EXEC @result = sp_getapplock @Resource = 'Fact', @LockMode =
'Exclusive';
--here I check if more users clicks on this button at the same time
--if so, only first user recalculate fact table(execute calculateFact
table), others waits on result
if @result=0
BEGIN
EXEC dbo.p_calculateFact
EXEC @result = sp_releaseapplock @Resource = 'Fact';
END
COMMIT TRANSACTION
SELECT ...... FROM dbo.tblFact WHERE ....
In procedure dbo.p_recalculateFact I do the following:
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE xtype='U' AND
NAME='fact_tmp')
DROP TABLE rdr.fact_tmp
SELECT col1, col2, ..... INTO rdr.fact_tmp
FROM tbl1 JOIN tbl2 JOIN tbl3 ......
WHERE ................
--I'm using drop and select into some fact_tmp table to achieve
minimal logging
--and also while fact_tmp is calculating all users from other
processes can do the reading from tblFact table
When fact_tmp is created, I drop fact table:
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE xtype='U' AND
NAME='tblFact')
DROP TABLE rdr.tblFact
Than I rename tmp table to fact table:
DECLARE @sql NVARCHAR(100)
SET @sql=N'sp_RENAME ''rdr.fact_tmp'' , ''tblFact'''
EXEC(@sql)
When I test this button with many clicks, everything works. But when
on production I get blocked processes every day on peak-times.
What should I change here? Or any suggestion how to remove blocked
processes?
I have also READ_COMMITTED_SNAPSHOT ON in this database to reduce
locks. Database is SQL2008.
br, Simon