Deadlock on single UPDATE
David Shepherd
I wonder if an expert is able to help with a deadlocking problem I am
seeing.
I have a stored procedure which executes a single UPDATE statement within a
transaction running at SERIALIZABLE isolation level. The stored procedure
operates on a table which has a single row and 2 columns. The table is very
simple and does not contain any indexes, constraints, or keys.
The actual stored procedure will eventually be more complicated than shown,
but the rest of the code is commented out and the deadlock still remains.
As a temporary fix I currently use WITH(TABLOCKX) on the UPDATE statement,
but I would like to know why it fails without this hint as I may be missing
some important information regarding locking. There is a dump of the
deadlock from the logs if that might help too.
I appreciate any advice that can be offered.
Thanks
Dave
Stored procedure. . .
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
/* Update the user count */
UPDATE [Statistics]
SET [UserCount] = [UserCount] + 1
IF @@ERROR <> 0
GOTO ErrorHandler
/* Committ the transaction */
COMMIT
GOTO ExitHandler
ErrorHandler:
/* Rollback the transaction */
ROLLBACK
ExitHandler:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
Dump. . .
2006-10-03 17:59:31.34 spid4 ----------------------------------
2006-10-03 17:59:31.34 spid4 Starting deadlock search 1430
2006-10-03 17:59:31.34 spid4 Target Resource Owner:
2006-10-03 17:59:31.34 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:51 ECID:0 Ec:(0x440f7578) Value:0x42be1800
2006-10-03 17:59:31.34 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
IX SPID:51 ECID:0 Ec:(0x440f7578) Value:0x42be1800
2006-10-03 17:59:31.34 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:60 ECID:0 Ec:(0x42fff578) Value:0x42bc2380
2006-10-03 17:59:31.34 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
X SPID:56 ECID:0 Ec:(0x42e31578) Value:0x42bc3da0
2006-10-03 17:59:31.34 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
X SPID:60 ECID:0 Ec:(0x42fff578) Value:0x42bc2380
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4 End deadlock search 1430 ... a deadlock was
not found.
2006-10-03 17:59:31.34 spid4 ----------------------------------
2006-10-03 17:59:31.34 spid4 ----------------------------------
2006-10-03 17:59:31.34 spid4 Starting deadlock search 1431
2006-10-03 17:59:31.34 spid4 Target Resource Owner:
2006-10-03 17:59:31.34 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:56 ECID:0 Ec:(0x42e31578) Value:0x42bc3da0
2006-10-03 17:59:31.34 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
X SPID:56 ECID:0 Ec:(0x42e31578) Value:0x42bc3da0
2006-10-03 17:59:31.34 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:60 ECID:0 Ec:(0x42fff578) Value:0x42bc2380
2006-10-03 17:59:31.34 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
X SPID:56 ECID:0 Ec:(0x42e31578) Value:0x42bc3da0
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4 Deadlock cycle was encountered ....
verifying cycle
2006-10-03 17:59:31.34 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
X SPID:56 ECID:0 Ec:(0x42e31578) Value:0x42bc3da0 Cost:(0/0)
2006-10-03 17:59:31.34 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:60 ECID:0 Ec:(0x42fff578) Value:0x42bc2380 Cost:(0/0)
2006-10-03 17:59:31.34 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
X SPID:56 ECID:0 Ec:(0x42e31578) Value:0x42bc3da0 Cost:(0/0)
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4
Deadlock encountered .... Printing deadlock information
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4 Wait-for graph
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4 Node:1
2006-10-03 17:59:31.34 spid4 TAB: 7:2073058421 [] CleanCnt:2
Mode: IX Flags: 0x0
2006-10-03 17:59:31.34 spid4 Grant List::
2006-10-03 17:59:31.34 spid4 Owner:0x48daea00 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:60 ECID:0
2006-10-03 17:59:31.34 spid4 SPID: 60 ECID: 0 Statement Type: UPDATE
Line #: 29
2006-10-03 17:59:31.34 spid4 Input Buf: RPC Event: Advisor_AddUser;1
2006-10-03 17:59:31.34 spid4 Requested By:
2006-10-03 17:59:31.34 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:56 ECID:0 Ec:(0x42e31578) Value:0x42bc3da0 Cost:(0/0)
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4 Node:2
2006-10-03 17:59:31.34 spid4 TAB: 7:2073058421 [] CleanCnt:2
Mode: IX Flags: 0x0
2006-10-03 17:59:31.34 spid4 Grant List::
2006-10-03 17:59:31.34 spid4 Owner:0x42bc3640 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:56 ECID:0
2006-10-03 17:59:31.34 spid4 SPID: 56 ECID: 0 Statement Type: UPDATE
Line #: 29
2006-10-03 17:59:31.34 spid4 Input Buf: RPC Event: Advisor_AddUser;1
2006-10-03 17:59:31.34 spid4 Requested By:
2006-10-03 17:59:31.34 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:60 ECID:0 Ec:(0x42fff578) Value:0x42bc2380 Cost:(0/0)
2006-10-03 17:59:31.34 spid4 Victim Resource Owner:
2006-10-03 17:59:31.34 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:60 ECID:0 Ec:(0x42fff578) Value:0x42bc2380 Cost:(0/0)
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4 End deadlock search 1431 ... a deadlock was
found.
2006-10-03 17:59:31.34 spid4 ----------------------------------
2006-10-03 17:59:31.34 spid4 ----------------------------------
2006-10-03 17:59:31.34 spid4 Starting deadlock search 1432
2006-10-03 17:59:31.34 spid4 Target Resource Owner:
2006-10-03 17:59:31.34 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:59 ECID:0 Ec:(0x42f49578) Value:0x42bc3b20
2006-10-03 17:59:31.34 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
IX SPID:59 ECID:0 Ec:(0x42f49578) Value:0x42bc3b20
2006-10-03 17:59:31.34 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
IX SPID:51 ECID:0 Ec:(0x440f7578) Value:0x42be1800
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4 Previous victim encountered ... aborting
search
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4 End deadlock search 1432 ... a deadlock was
not found.
2006-10-03 17:59:31.34 spid4 ----------------------------------
2006-10-03 17:59:31.34 spid4 ----------------------------------
2006-10-03 17:59:31.34 spid4 Starting deadlock search 1433
2006-10-03 17:59:31.34 spid4 Target Resource Owner:
2006-10-03 17:59:31.34 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:60 ECID:0 Ec:(0x42fff578) Value:0x42bc2380
2006-10-03 17:59:31.34 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
X SPID:60 ECID:0 Ec:(0x42fff578) Value:0x42bc2380
2006-10-03 17:59:31.34 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:56 ECID:0 Ec:(0x42e31578) Value:0x42bc3da0
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4 Previous victim encountered ... aborting
search
2006-10-03 17:59:31.34 spid4
2006-10-03 17:59:31.34 spid4 End deadlock search 1433 ... a deadlock was
not found.
2006-10-03 17:59:31.34 spid4 ----------------------------------
2006-10-03 17:59:36.47 spid4 ----------------------------------
2006-10-03 17:59:36.47 spid4 Starting deadlock search 1434
2006-10-03 17:59:36.47 spid4 Target Resource Owner:
2006-10-03 17:59:36.47 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:51 ECID:0 Ec:(0x440f7578) Value:0x48daea00
2006-10-03 17:59:36.47 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
X SPID:51 ECID:0 Ec:(0x440f7578) Value:0x48daea00
2006-10-03 17:59:36.47 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:59 ECID:0 Ec:(0x42f49578) Value:0x42bc3640
2006-10-03 17:59:36.47 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
X SPID:51 ECID:0 Ec:(0x440f7578) Value:0x48daea00
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4 Deadlock cycle was encountered ....
verifying cycle
2006-10-03 17:59:36.47 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
X SPID:51 ECID:0 Ec:(0x440f7578) Value:0x48daea00 Cost:(0/0)
2006-10-03 17:59:36.47 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:59 ECID:0 Ec:(0x42f49578) Value:0x42bc3640 Cost:(0/0)
2006-10-03 17:59:36.47 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
X SPID:51 ECID:0 Ec:(0x440f7578) Value:0x48daea00 Cost:(0/0)
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4
Deadlock encountered .... Printing deadlock information
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4 Wait-for graph
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4 Node:1
2006-10-03 17:59:36.47 spid4 TAB: 7:2073058421 [] CleanCnt:2
Mode: IX Flags: 0x0
2006-10-03 17:59:36.47 spid4 Grant List::
2006-10-03 17:59:36.47 spid4 Owner:0x42bc3b20 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:59 ECID:0
2006-10-03 17:59:36.47 spid4 SPID: 59 ECID: 0 Statement Type: UPDATE
Line #: 29
2006-10-03 17:59:36.47 spid4 Input Buf: RPC Event: Advisor_AddUser;1
2006-10-03 17:59:36.47 spid4 Requested By:
2006-10-03 17:59:36.47 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:51 ECID:0 Ec:(0x440f7578) Value:0x48daea00 Cost:(0/0)
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4 Node:2
2006-10-03 17:59:36.47 spid4 TAB: 7:2073058421 [] CleanCnt:2
Mode: IX Flags: 0x0
2006-10-03 17:59:36.47 spid4 Grant List::
2006-10-03 17:59:36.47 spid4 Owner:0x42be1800 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:51 ECID:0
2006-10-03 17:59:36.47 spid4 SPID: 51 ECID: 0 Statement Type: UPDATE
Line #: 29
2006-10-03 17:59:36.47 spid4 Input Buf: RPC Event: Advisor_AddUser;1
2006-10-03 17:59:36.47 spid4 Requested By:
2006-10-03 17:59:36.47 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:59 ECID:0 Ec:(0x42f49578) Value:0x42bc3640 Cost:(0/0)
2006-10-03 17:59:36.47 spid4 Victim Resource Owner:
2006-10-03 17:59:36.47 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:59 ECID:0 Ec:(0x42f49578) Value:0x42bc3640 Cost:(0/0)
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4 End deadlock search 1434 ... a deadlock was
found.
2006-10-03 17:59:36.47 spid4 ----------------------------------
2006-10-03 17:59:36.47 spid4 ----------------------------------
2006-10-03 17:59:36.47 spid4 Starting deadlock search 1435
2006-10-03 17:59:36.47 spid4 Target Resource Owner:
2006-10-03 17:59:36.47 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:56 ECID:0 Ec:(0x42e31578) Value:0x42bc3100
2006-10-03 17:59:36.47 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
IX SPID:56 ECID:0 Ec:(0x42e31578) Value:0x42bc3100
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4 Previous victim encountered ... aborting
search
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4 End deadlock search 1435 ... a deadlock was
not found.
2006-10-03 17:59:36.47 spid4 ----------------------------------
2006-10-03 17:59:36.47 spid4 ----------------------------------
2006-10-03 17:59:36.47 spid4 Starting deadlock search 1436
2006-10-03 17:59:36.47 spid4 Target Resource Owner:
2006-10-03 17:59:36.47 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:59 ECID:0 Ec:(0x42f49578) Value:0x42bc3640
2006-10-03 17:59:36.47 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
X SPID:59 ECID:0 Ec:(0x42f49578) Value:0x42bc3640
2006-10-03 17:59:36.47 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
X SPID:51 ECID:0 Ec:(0x440f7578) Value:0x48daea00
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4 Previous victim encountered ... aborting
search
2006-10-03 17:59:36.47 spid4
2006-10-03 17:59:36.47 spid4 End deadlock search 1436 ... a deadlock was
not found.
2006-10-03 17:59:36.47 spid4 ----------------------------------
Itzik Ben-Gan
Here's what I think that is happening by observing the locks that are
acquired with Profiler... (my interpretation)...
Technically the deadlock happens because each transaction first acquires an
Intent Exclusive lock (IX) on the table and keeps it. Typically an IX lock
indicates the intent to modify rows at a lower granularity level. Then the
transaction attempts to acquire an Exclusive table lock (X); I believe that
it attempts to acquire an X table lock and not go through Intent Update (IU)
and Update (U) locks first because you requested to work with a serializable
isolation level, and since there's no index, the way to guaranty
serializable is to lock the whole table. An IX lock is compatible with an IX
lock, therefore two different transactions can acquire IX locks at the same
time. But an X lock is incompatible with an IX lock, so when the timing is
such that both transactions managed to acquire IX locks and then ask for an
X lock, they're blocking eachother and you have your deadlock.
When you added the TABLOCKX hint, you told SQL Server that you want to
exclusively lock the whole table to begin with, so there was no need for IX
locks, hence no deadlocks.
The deadlock doesn't happen in read committed isolation because the
transaction requests the following sequence of locks:
1. IX table
2. IU page
3. U row
4. IX page
4. X row
The key in the deadlock prevention here is that U lock is incompatible with
U lock (not with previously acquired locks, so one transaction blocks the
other as opposed to both blocking eachother.
So a couple of ways around the deadlocks are:
1. Use the hint
2. Use read committed isolation
A third option that I tested that seems to work is to create a clustered
index on the other column. I have to start class so I can't check the
sequence of lock requests that helps preventing the deadlock... I'll leave
it to you... ;-)
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"David Shepherd" <dave.nospam.@daveshep.com> wrote in message
news:ftGdneSP_cN-K7_Y...@bt.com...
David Shepherd
Thank you for such a detailed explanation of the problem. I'm using your
recommendation and using a transaction isolation level of READ COMMITTED now
(using hits where necessary) and everything appears to be working fine.
Thanks again
Dave
"Itzik Ben-Gan" <it...@REMOVETHIS.SolidQualityLearning.com> wrote in message
news:Ol8S3mz5...@TK2MSFTNGP05.phx.gbl...