August 25, 2011 at 7:56 am
Hi everyone
Could someone explain me why in my code deadlock occurs.
SET TRANSACTION ISOLATION LEVEL Repeatable Read;
BEGIN TRANSACTION;
select top 1 @Count=amount from table WHERE primaryKey=@Reference
if ISNULL(@COUNT,0)!=0
begin
-- Increment the Item Count --
set @Count = @Count + 1
UPDATE table SET amount =@COUNT WHERE primaryKey=@Reference
end
else
begin
set @Count = 1
INSERT INTO table(primaryKey,amount) VALUES (@Reference, @COUNT)
end
COMMIT TRANSACTION;
DeadLock occurs when two processes in the same time tries to do Update on table, here is log from profiler:
for spid 91:
SP:StmtCompleted select top 1 @Count=LastReference from LastDelReferenceNo WHERE RefDate=@Reference
SP:StmtCompleted if ISNULL(@COUNT,0)!=0
SP:StmtCompleted set @Count = @Count + 1
for spid 113
SP:StmtCompleted select top 1 @Count=LastReference from LastDelReferenceNo WHERE RefDate=@Reference
SP:StmtCompleted if ISNULL(@COUNT,0)!=0
SP:StmtCompleted set @Count = @Count + 1
And next is deadlock:
Lock:Deadlock ChainDeadlock Chain SPID = 91
Lock:Deadlock ChainDeadlock Chain SPID = 113
Lock:Deadlock (8000a7cbfb0e)
Great thanks for any helps
August 25, 2011 at 8:22 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 1:33 am
Here is deadlock graph:
2011-08-26 08:21:03.95 spid16s deadlock-list
2011-08-26 08:21:03.96 spid16s deadlock victim=process989c48
2011-08-26 08:21:03.96 spid16s process-list
2011-08-26 08:21:03.96 spid16s process id=process8fb4c8 taskpriority=0 logused=0 waitresource=KEY: 15:72057594199408640 (8000a7cbfb0e) waittime=375 ownerId=138663890 transactionname=user_transaction lasttranstarted=2011-08-26T08:21:03.050 XDES=0xffffffff854b6250 lockMode=X schedulerid=1 kpid=7896 status=suspended spid=63 sbid=0 ecid=0 priority=0 transcount=3 lastbatchstarted=2011-08-26T08:21:03.050 lastbatchcompleted=2011-08-26T08:21:03.050 clientapp=.Net SqlClient Data Provider hostname=HOSTA hostpid=2896 loginname=user isolationlevel=serializable (4) xactid=138663890 currentdb=15 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2011-08-26 08:21:03.96 spid16s executionStack
2011-08-26 08:21:03.96 spid16s frame procname=Dev.dbo.SCALAR_CreateDeliveryReference line=37 stmtstart=1638 stmtend=1794 sqlhandle=0x03000f0034c0ec26a6feb4004a9f00000100000000000000
2011-08-26 08:21:03.96 spid16s UPDATE TABLE SET Amount=@COUNT WHERE primaryKey=@Reference
2011-08-26 08:21:03.96 spid16s frame procname=Dev.dbo.INSERT_DeliveryConsignment line=30 stmtstart=1404 stmtend=1524 sqlhandle=0x03000f00e97e854152ebeb00499f00000100000000000000
2011-08-26 08:21:03.96 spid16s EXEC dbo.SCALAR_CreateDeliveryReference @Out=@Out OUTPUT
2011-08-26 08:21:03.96 spid16s inputbuf
2011-08-26 08:21:03.96 spid16s Proc [Database Id = 15 Object Id = 1099267817]
2011-08-26 08:21:03.96 spid16s process id=process989c48 taskpriority=0 logused=0 waitresource=KEY: 15:72057594199408640 (8000a7cbfb0e) waittime=375 ownerId=138663311 transactionname=user_transaction lasttranstarted=2011-08-26T08:21:00.243 XDES=0xffffffff80474450 lockMode=X schedulerid=2 kpid=9476 status=suspended spid=62 sbid=0 ecid=0 priority=0 transcount=3 lastbatchstarted=2011-08-26T08:21:00.247 lastbatchcompleted=2011-08-26T08:21:00.243 clientapp=.Net SqlClient Data Provider hostname=HOSTA hostpid=2896 loginname=user isolationlevel=serializable (4) xactid=138663311 currentdb=15 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2011-08-26 08:21:03.96 spid16s executionStack
2011-08-26 08:21:03.96 spid16s frame procname=Dev.dbo.SCALAR_CreateDeliveryReference line=37 stmtstart=1638 stmtend=1794 sqlhandle=0x03000f0034c0ec26a6feb4004a9f00000100000000000000
2011-08-26 08:21:03.96 spid16s UPDATE TABLE SET Amount=@COUNT WHERE primaryKey=@Reference
2011-08-26 08:21:03.96 spid16s frame procname=Dev.dbo.INSERT_DeliveryConsignment line=30 stmtstart=1404 stmtend=1524 sqlhandle=0x03000f00e97e854152ebeb00499f00000100000000000000
2011-08-26 08:21:03.96 spid16s EXEC dbo.SCALAR_CreateDeliveryReference @Out=@Out OUTPUT
2011-08-26 08:21:03.96 spid16s inputbuf
2011-08-26 08:21:03.96 spid16s Proc [Database Id = 15 Object Id = 1099267817]
2011-08-26 08:21:03.96 spid16s resource-list
2011-08-26 08:21:03.96 spid16s keylock hobtid=72057594199408640 dbid=15 objectname=Dev.dbo.TABLE indexname=PK_TABLE id=lockffffffffa2241400 mode=S associatedObjectId=72057594199408640
2011-08-26 08:21:03.96 spid16s owner-list
2011-08-26 08:21:03.96 spid16s owner id=process8fb4c8 mode=S
2011-08-26 08:21:03.96 spid16s owner id=process989c48 mode=S
2011-08-26 08:21:03.96 spid16s waiter-list
2011-08-26 08:21:03.96 spid16s waiter id=process989c48 mode=X requestType=convert
2011-08-26 08:21:03.96 spid16s waiter id=process8fb4c8 mode=X requestType=convert
Many thanks for any helps
August 26, 2011 at 2:34 am
The deadlock is between the select @Count and the update... Two threads are both taking a shared lock on a row that the select is reading and then, when the update starts, trying to convert that lock to exclusive.
To fix this, put a UPDLOCK hint into the select.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 4:43 am
Now everything works perfectly. Many thanks.
Can you explain me one thing. I thought if I set transaction with isolation as serializable then only one proccess can get into trasaction body, and every next process must wait untill the first one has left transaction. I didn't expect that two processes will be able to read against the same row in transaction (Why wasn't the row locked?). Could you say me how it came that with UPDLOCK hint everything works fine? How does this hint work ?
Many thanks one again/
August 26, 2011 at 4:52 am
titan 90903 (8/26/2011)
Can you explain me one thing. I thought if I set transaction with isolation as serializable then only one proccess can get into trasaction body, and every next process must wait untill the first one has left transaction.
Not at all. (besides, you're setting repeatable read in the code you posted, not serialisable). Serialisable is about range locks on reads, not serialising procedure entry
I didn't expect that two processes will be able to read against the same row in transaction (Why wasn't the row locked?).
The row was locked (if it hadn't been, there would have been no deadlock). It was locked with a shared lock because that's what a read takes. Shared locks are compatible with other shared locks, so multiple processes can take a shared lock on the same row at the same time.
Could you say me how it came that with UPDLOCK hint everything works fine? How does this hint work ?
UPDLock means SQL takes a U lock rather than the S lock that it would have otherwise. U locks are compatible with S locks, but not with other U locks, so only one process at a time can get that first lock
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 5:52 am
Many thanks one again for advice and explanation.
Many thanks one again for advice and explanation. Yeah you are correct i code isolation is reapetable, it was beacause I tested both level. Finnally I've set serializable level. Does it matter if I use serializable or reapetable level in my cause?
August 26, 2011 at 5:54 am
Repeatable read and serialisable have different behaviours. You should read up on what they do and use the one that's appropriate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply