November 9, 2009 at 2:13 am
I have a Store Procedure being called to fill one table whenever we receive incoming files. Sometimes we receive more than one file and the procedure will be called simultaneously. Inside the Procedure the statements are quite simple as given below
IF NOT EXISTS (SELECT.... WHERE A=1 B=2)
INSERT ...
ELSE
UPDATE ... WHERE A=1 and B=2
END
doing this I started getting a duplicate records error, I assume that 2 same records tried to INSERT. To avoid this I put these staements inside a Transaction with SERIALIZABLE ISOLATION LEVEL.
things got even worse and I started getting deadlock error!!!
I tried with Isolation level Read Committed, which gave me a duplicate record error
Is there something wrong I am doing here??
November 9, 2009 at 3:01 am
There are 2 ways that I can thing about to make sure that this code will be executed by 1 process each time. The first way it to use an applicative lock using sp_getapplock (you can read about it in BOL – Books On Line, the help file that is installed with SQL Server’s client tools). Another way is to set isolation level to SERIALIZABLE, begin a transaction and first run the update statement. Then you can check the value of @@rowcount and if it is 0, run the insert statement. At that point you should commit the transaction. Notice that in both ways if 2 processes will try running this code on the same values for A and B, both will run, but they won’t run simultaneously.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 9, 2009 at 4:43 am
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Default
BEGIN TRANSACTION;
INSERT dbo.Table1 (A, B)
SELECT 1, 2
WHERE NOT EXISTS (SELECT * FROM dbo.Table1 WITH (UPDLOCK) WHERE A=1 AND B=2);
IF @@ROWCOUNT = 0
BEGIN
UPDATE ... WHERE A=1 AND B=2
END
COMMIT TRANSACTION;
Be sure to have a useful index in place to locate the A=1 AND B=2 row quickly.
November 10, 2009 at 7:44 am
... or use merge.
November 10, 2009 at 2:17 pm
david.wright-948385 (11/10/2009)
... or use merge.
...2008 only. (This is the 2005 forum!)
November 11, 2009 at 3:03 am
Oops, my bad... 2005 seems so far away now 🙂
November 12, 2009 at 8:00 am
How about simply stepping outside the box (litterally and figuratively) and simply prevent the loading of more than one file of data at a time?? VOILA!! No more blocking, deadlocks, code changes, etc.
P.S. Solutions like this are why I get paid the BIG bucks!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 12, 2009 at 6:48 pm
Thanks. I will try this.
November 12, 2009 at 6:51 pm
TheSQLGuru (11/12/2009)
How about simply stepping outside the box (litterally and figuratively) and simply prevent the loading of more than one file of data at a time?? VOILA!! No more blocking, deadlocks, code changes, etc.P.S. Solutions like this are why I get paid the BIG bucks!! 😀
Shhhh.. dont let your manager see this. 🙂
November 13, 2009 at 7:10 am
sajid.mohd (11/12/2009)
TheSQLGuru (11/12/2009)
How about simply stepping outside the box (litterally and figuratively) and simply prevent the loading of more than one file of data at a time?? VOILA!! No more blocking, deadlocks, code changes, etc.P.S. Solutions like this are why I get paid the BIG bucks!! 😀
Shhhh.. dont let your manager see this. 🙂
HAH! I AM the manager - and chief, cook and bottle washer too. 😀 I have been a very successful independent SQL Server consultant for over a decade now. And comparing what I charge to what the consulting firms charge for their staffers I am probably the best value in the consulting world! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply