DB lock

/*
****************************************
************************************
****************************************
*
2005-04-22 11:34:40.78 spid4 Wait-for graph
2005-04-22 11:34:40.78 spid4
2005-04-22 11:34:40.78 spid4 ...
2005-04-22 11:34:45.78 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:64
ECID:0 Ec:(0x44603570) Value:0x435
2005-04-22 11:34:45.78 spid4 Victim Resource Owner:
2005-04-22 11:34:45.78 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:64
ECID:0 Ec:(0x44603570) Value:0x435
2005-04-22 11:34:45.78 spid4 Requested By:
2005-04-22 11:34:45.78 spid4 Input Buf: RPC Event: uspGet ;1
2005-04-22 11:34:45.78 spid4 SPID: 63 ECID: 0 Statement Type: INSERT Line
#: 122
2005-04-22 11:34:45.78 spid4 Owner:0x43732860 Mode: X Flg:0x0 Ref:0
Life:02000000 SPID:63 ECID:0
2005-04-22 11:34:45.78 spid4 Grant List 1::
2005-04-22 11:34:45.78 spid4 KEY: 9:1890105774:1 (0900696fb3a3) CleanCnt:1
Mode: X Flags: 0x0
2005-04-22 11:34:45.78 spid4 Node:3
2005-04-22 11:34:45.78 spid4
2005-04-22 11:34:45.78 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:66
ECID:0 Ec:(0x45DEB570) Value:0x435
2005-04-22 11:34:45.78 spid4 Requested By:
2005-04-22 11:34:45.78 spid4 Input Buf: RPC Event: uspNext ;1
2005-04-22 11:34:45.78 spid4 SPID: 64 ECID: 0 Statement Type: INSERT Line
#: 122
2005-04-22 11:34:45.78 spid4 Owner:0x4354e420 Mode: U Flg:0x0 Ref:1
Life:02000000 SPID:64 ECID:0
2005-04-22 11:34:45.78 spid4 Grant List 0::
2005-04-22 11:34:45.78 spid4 KEY: 9:1890105774:1 (07005a186c43) CleanCnt:2
Mode: U Flags: 0x0
2005-04-22 11:34:45.78 spid4 Node:2
2005-04-22 11:34:45.78 spid4
2005-04-22 11:34:45.78 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:63
ECID:0 Ec:(0x4309D570) Value:0x42c
2005-04-22 11:34:45.78 spid4 Requested By:
2005-04-22 11:34:45.78 spid4 Input Buf: RPC Event: uspNext ;1
2005-04-22 11:34:45.78 spid4 SPID: 66 ECID: 0 Statement Type: INSERT Line
#: 122
2005-04-22 11:34:45.78 spid4 Owner:0x4354f3e0 Mode: U Flg:0x0 Ref:1
Life:02000000 SPID:66 ECID:0
2005-04-22 11:34:45.78 spid4 Wait List:
2005-04-22 11:34:45.78 spid4 KEY: 9:1890105774:1 (07005a186c43) CleanCnt:2
Mode: U Flags: 0x0
2005-04-22 11:34:45.78 spid4 Node:1
****************************************
************************************
****************************************
* */
As I see in the log, i see that node 2 and node 3 are both holding locks on
KEY: 9:1890105774:1, with one X and another U. How can this be possible sinc
e
X is not compatible with U? What is the bracket (0900696fb3a3) next to it
means?
uspGet (node3) and uspNext (node2) would call a same stored proc, uspSNR, in
their code.
Can anyone point me to any resources that explain these kind of log? So I
can read up on it to better understand, thanks.
//eugeneHi
That is a deadlock trace that SQL Server does based on the traceflag 1204
bieng set.
As you said, they are incompatible. They are requests, and the 2nd request
would not have been granted.
Lookup "Troubleshooting Deadlocks" in BOL.
Regards
Mike
"Eugene" wrote:

> /*
> ****************************************
**********************************
****************************************
***
> 2005-04-22 11:34:40.78 spid4 Wait-for graph
> 2005-04-22 11:34:40.78 spid4
> 2005-04-22 11:34:40.78 spid4 ...
> 2005-04-22 11:34:45.78 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:64
> ECID:0 Ec:(0x44603570) Value:0x435
> 2005-04-22 11:34:45.78 spid4 Victim Resource Owner:
> 2005-04-22 11:34:45.78 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:64
> ECID:0 Ec:(0x44603570) Value:0x435
> 2005-04-22 11:34:45.78 spid4 Requested By:
> 2005-04-22 11:34:45.78 spid4 Input Buf: RPC Event: uspGet ;1
> 2005-04-22 11:34:45.78 spid4 SPID: 63 ECID: 0 Statement Type: INSERT Line
> #: 122
> 2005-04-22 11:34:45.78 spid4 Owner:0x43732860 Mode: X Flg:0x0 Ref:
0
> Life:02000000 SPID:63 ECID:0
> 2005-04-22 11:34:45.78 spid4 Grant List 1::
> 2005-04-22 11:34:45.78 spid4 KEY: 9:1890105774:1 (0900696fb3a3) CleanCnt:
1
> Mode: X Flags: 0x0
> 2005-04-22 11:34:45.78 spid4 Node:3
> 2005-04-22 11:34:45.78 spid4
> 2005-04-22 11:34:45.78 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:66
> ECID:0 Ec:(0x45DEB570) Value:0x435
> 2005-04-22 11:34:45.78 spid4 Requested By:
> 2005-04-22 11:34:45.78 spid4 Input Buf: RPC Event: uspNext ;1
> 2005-04-22 11:34:45.78 spid4 SPID: 64 ECID: 0 Statement Type: INSERT Line
> #: 122
> 2005-04-22 11:34:45.78 spid4 Owner:0x4354e420 Mode: U Flg:0x0 Ref:
1
> Life:02000000 SPID:64 ECID:0
> 2005-04-22 11:34:45.78 spid4 Grant List 0::
> 2005-04-22 11:34:45.78 spid4 KEY: 9:1890105774:1 (07005a186c43) CleanCnt:
2
> Mode: U Flags: 0x0
> 2005-04-22 11:34:45.78 spid4 Node:2
> 2005-04-22 11:34:45.78 spid4
> 2005-04-22 11:34:45.78 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:63
> ECID:0 Ec:(0x4309D570) Value:0x42c
> 2005-04-22 11:34:45.78 spid4 Requested By:
> 2005-04-22 11:34:45.78 spid4 Input Buf: RPC Event: uspNext ;1
> 2005-04-22 11:34:45.78 spid4 SPID: 66 ECID: 0 Statement Type: INSERT Line
> #: 122
> 2005-04-22 11:34:45.78 spid4 Owner:0x4354f3e0 Mode: U Flg:0x0 Ref:
1
> Life:02000000 SPID:66 ECID:0
> 2005-04-22 11:34:45.78 spid4 Wait List:
> 2005-04-22 11:34:45.78 spid4 KEY: 9:1890105774:1 (07005a186c43) CleanCnt:
2
> Mode: U Flags: 0x0
> 2005-04-22 11:34:45.78 spid4 Node:1
> ****************************************
**********************************
****************************************
*** */
> As I see in the log, i see that node 2 and node 3 are both holding locks o
n
> KEY: 9:1890105774:1, with one X and another U. How can this be possible si
nce
> X is not compatible with U? What is the bracket (0900696fb3a3) next to it
> means?
> uspGet (node3) and uspNext (node2) would call a same stored proc, uspSNR,
in
> their code.
> Can anyone point me to any resources that explain these kind of log? So I
> can read up on it to better understand, thanks.
>
> //eugene|||Doesnt seems to help...... I'm interested in knowing the outcome of this
issue. Any expert out there to help out ?
Cheers,
Jason X
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> That is a deadlock trace that SQL Server does based on the traceflag 1204
> bieng set.
> As you said, they are incompatible. They are requests, and the 2nd request
> would not have been granted.
> Lookup "Troubleshooting Deadlocks" in BOL.
> Regards
> Mike
> "Eugene" wrote:
>|||Hi Mike, thanks for helping. Can you please guide me on the log? coz this is
really my first time to use the 1204 traceflag.
You mentioned 2nd request would not be granted, and they are request, but
how do you determine they are requests?
My concern is in the node3 and node2 (if I can identify them correctly from
the log).
node3
Grant List 1::
KEY: 9:1890105774:1 (0900696fb3a3) CleanCnt:1 Mode: X Flags: 0x0
node2
Grant List 0::
KEY: 9:1890105774:1 (07005a186c43) CleanCnt:2 Mode: U Flags: 0x0
while for node1, as you said, it is in Wait List. so my concern is why node3
and node2 got X and U respectively for KEY: 9:1890105774:1
many thanks
Eugene
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> That is a deadlock trace that SQL Server does based on the traceflag 1204
> bieng set.
> As you said, they are incompatible. They are requests, and the 2nd request
> would not have been granted.
> Lookup "Troubleshooting Deadlocks" in BOL.
> Regards
> Mike
> "Eugene" wrote:
>|||Node 2 and 3 are for different keys.
For key locks the content after the string "KEY" corresponds to the
following: <dbid>:<object-id>:<index-id> (<hash-based-on-the-key-columns> ).
For all keys in the same index only the hash value (in the parenthesis) will
differ. Since the two hash values you have are different, the nodes
represent different keys in the index and so the X and U locks are fine.
The deadlock you have is the following:
Spid 64 holds an U lock on KEY: 9:1890105774:1 (07005a186c43). (From the
info for Node 2)
Spid 64 is waiting for an U lock on KEY: 9:1890105774:1 (0900696fb3a3).
(From the info for Node 3)
Spid 63 holds an X lock on KEY: 9:1890105774:1 (0900696fb3a3) which blocks
spid 64. (From the info for Node 3)
Spid 63 is waiting for an U lock on KEY: 9:1890105774:1 (0900696fb3a3).
(From the info for Node 1)
Spid 63 is blocked by spid 66 because spid 66 is ahead of it in the wait
list (locks are granted in FIFO order)
Spid 66 is also waiting for an U lock on KEY: 9:1890105774:1 (0900696fb3a3).
(From the info for Node 1 and 2)
Spid 66 is blocked by the U lock held by spid 64 (two U locks are not
compatible)
Thus the deadlock graph is: 64 blocked by 63, 63 blocked by 66, 66 blocked
by 64, ...
Santeri (Santtu) Voutilainen
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:F5AAEC23-C972-465E-BDD8-B83B07C743FC@.microsoft.com...
> Hi Mike, thanks for helping. Can you please guide me on the log? coz this
> is
> really my first time to use the 1204 traceflag.
> You mentioned 2nd request would not be granted, and they are request, but
> how do you determine they are requests?
> My concern is in the node3 and node2 (if I can identify them correctly
> from
> the log).
> node3
> Grant List 1::
> KEY: 9:1890105774:1 (0900696fb3a3) CleanCnt:1 Mode: X Flags: 0x0
> node2
> Grant List 0::
> KEY: 9:1890105774:1 (07005a186c43) CleanCnt:2 Mode: U Flags: 0x0
> while for node1, as you said, it is in Wait List. so my concern is why
> node3
> and node2 got X and U respectively for KEY: 9:1890105774:1
> many thanks
> Eugene
> "Mike Epprecht (SQL MVP)" wrote:
>|||thank you very much :)
but i can't really understand the whole explanation. let me brief you on my
table structure, maybe then you can help me better.
Table C
ID int identity (Primary Key)
SNFormatID int (Nullable)
LastUsed varchar 200 (Nullable)
Order tinyint (Nullable)
ID is being referenced by another table A, whereas SNFormatID is referencing
yet another table B.
The only index/key is a clustered index on ID.
from my trace, I found that the statement that causes the deadlock is in a
SELECT statement, joining this table and table B.
insert @.T_SN (SID, LastUsed)
select s.[ID], s.LastUsed
from C s with (updlock) left outer join
B r with (updlock) on r.SNSectionID = s.[ID] and r.StatusID = 1
where s.SNFormatID = @.SNFormatID
order by s.[order]
========================================
==================
The purpose for this is to generate the next number in the sequence, hence
there's the LastUsed column. So, we have a problem, when more than one
connection is asking for the next number. As the tables were locked with
updlock, and before it completes, the connection ask for it as well, and
can't read out the LastUsed.
As you mentioned, it is different key, can you let me know how i can find
out which key is at fault as i don't how to derive the key from the hash.
"Santeri Voutilainen [MSFT]" wrote:

> Node 2 and 3 are for different keys.
> For key locks the content after the string "KEY" corresponds to the
> following: <dbid>:<object-id>:<index-id> (<hash-based-on-the-key-columns> )
.
> For all keys in the same index only the hash value (in the parenthesis) wi
ll
> differ. Since the two hash values you have are different, the nodes
> represent different keys in the index and so the X and U locks are fine.
> The deadlock you have is the following:
> Spid 64 holds an U lock on KEY: 9:1890105774:1 (07005a186c43). (From the
> info for Node 2)
> Spid 64 is waiting for an U lock on KEY: 9:1890105774:1 (0900696fb3a3).
> (From the info for Node 3)
> Spid 63 holds an X lock on KEY: 9:1890105774:1 (0900696fb3a3) which blocks
> spid 64. (From the info for Node 3)
> Spid 63 is waiting for an U lock on KEY: 9:1890105774:1 (0900696fb3a3).
> (From the info for Node 1)
> Spid 63 is blocked by spid 66 because spid 66 is ahead of it in the wait
> list (locks are granted in FIFO order)
> Spid 66 is also waiting for an U lock on KEY: 9:1890105774:1 (0900696fb3a3
).
> (From the info for Node 1 and 2)
> Spid 66 is blocked by the U lock held by spid 64 (two U locks are not
> compatible)
> Thus the deadlock graph is: 64 blocked by 63, 63 blocked by 66, 66 blocked
> by 64, ...
> --
> Santeri (Santtu) Voutilainen
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Eugene" <Eugene@.discussions.microsoft.com> wrote in message
> news:F5AAEC23-C972-465E-BDD8-B83B07C743FC@.microsoft.com...
>
>|||I would need to look at the other statements involved in the deadlock -- if
any of the spids were in a multi-statement transaction then I would also
need all the statements in the transactions that refer to any of the tables
in deadlock.
However, there are a couple things you may want to look at:
You'll want to take a look at the plan for the select statement you posted
below. Depending on the table size and statistics, the plan may be doing a
full table scan on C due to the where clause which access a column that is
not included in any index. I assume B has an index on SNSectionID? Also,
in an insert-select the "order by" serves no purpose.
Do you have cascading deletes for the B.SNSectionID - C.SNFormatID
relationship? If so, any delete in B will result in a full table scan on C
due to the lack of index on C.SNFormatID.
Mixing index ss and table scans can cause deadlocks because the rows are
accessed and locked in differing orders.
Santeri (Santtu) Voutilainen
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eugene" <Eugene@.discussions.microsoft.com> wrote in message
news:4AFAEEEA-6938-4ED7-9EA9-06625BC1FC24@.microsoft.com...
> thank you very much :)
> but i can't really understand the whole explanation. let me brief you on
> my
> table structure, maybe then you can help me better.
> Table C
> ID int identity (Primary Key)
> SNFormatID int (Nullable)
> LastUsed varchar 200 (Nullable)
> Order tinyint (Nullable)
> ID is being referenced by another table A, whereas SNFormatID is
> referencing
> yet another table B.
> The only index/key is a clustered index on ID.
> from my trace, I found that the statement that causes the deadlock is in a
> SELECT statement, joining this table and table B.
> insert @.T_SN (SID, LastUsed)
> select s.[ID], s.LastUsed
> from C s with (updlock) left outer join
> B r with (updlock) on r.SNSectionID = s.[ID] and r.StatusID = 1
> where s.SNFormatID = @.SNFormatID
> order by s.[order]
> ========================================
==================
> The purpose for this is to generate the next number in the sequence, hence
> there's the LastUsed column. So, we have a problem, when more than one
> connection is asking for the next number. As the tables were locked with
> updlock, and before it completes, the connection ask for it as well, and
> can't read out the LastUsed.
> As you mentioned, it is different key, can you let me know how i can find
> out which key is at fault as i don't how to derive the key from the hash.
>
> "Santeri Voutilainen [MSFT]" wrote:
>