Can anyone explain to me, even hypothetically, how 2 SELECT statements on th
e
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apfWhat isolation level are you using?
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||The default - Read Committed|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed|||Can you post the deadlock trace?
"apf" wrote:
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > o
n the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.|||If you can also post the two actual select statements causing the deadlocks
.
Are there any indexes supporting the where clause and if so how selective ar
e
they?
"FredG" wrote:
[vbcol=seagreen]
> Can you post the deadlock trace?
>
> "apf" wrote:
>
Showing posts with label trace. Show all posts
Showing posts with label trace. Show all posts
Thursday, March 29, 2012
Deadlocks on SELECT statements?
Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apf
What isolation level are you using?
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf
|||The default - Read Committed
|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed
|||Can you post the deadlock trace?
"apf" wrote:
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf
|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > on the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
|||If you can also post the two actual select statements causing the deadlocks.
Are there any indexes supporting the where clause and if so how selective are
they?
"FredG" wrote:
[vbcol=seagreen]
> Can you post the deadlock trace?
>
> "apf" wrote:
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apf
What isolation level are you using?
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf
|||The default - Read Committed
|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed
|||Can you post the deadlock trace?
"apf" wrote:
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf
|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > on the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
|||If you can also post the two actual select statements causing the deadlocks.
Are there any indexes supporting the where clause and if so how selective are
they?
"FredG" wrote:
[vbcol=seagreen]
> Can you post the deadlock trace?
>
> "apf" wrote:
Deadlocks on SELECT statements?
Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apfWhat isolation level are you using?
--
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||The default - Read Committed|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
--
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed|||Can you post the deadlock trace?
"apf" wrote:
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > on the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.|||If you can also post the two actual select statements causing the deadlocks.
Are there any indexes supporting the where clause and if so how selective are
they?
"FredG" wrote:
> Can you post the deadlock trace?
>
> "apf" wrote:
> > Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> > same table can cause a deadlock?
> >
> > The output to trace flag 1204 indicates that deadlocking in occuring on 2
> > statments that look like this:
> >
> > select count(*) from table_1 where ...
> >
> > The where criteria is different for the 2 SELECTS.
> >
> > Thanks in advance for any ideas or guesses!!
> >
> > apf
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apfWhat isolation level are you using?
--
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||The default - Read Committed|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
--
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed|||Can you post the deadlock trace?
"apf" wrote:
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > on the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.|||If you can also post the two actual select statements causing the deadlocks.
Are there any indexes supporting the where clause and if so how selective are
they?
"FredG" wrote:
> Can you post the deadlock trace?
>
> "apf" wrote:
> > Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> > same table can cause a deadlock?
> >
> > The output to trace flag 1204 indicates that deadlocking in occuring on 2
> > statments that look like this:
> >
> > select count(*) from table_1 where ...
> >
> > The where criteria is different for the 2 SELECTS.
> >
> > Thanks in advance for any ideas or guesses!!
> >
> > apf
Deadlocks and trace 1204
We had a deadlock the other day and would like to identify the offending
process. Is it too late if there were no traces?
If we set trace 1204 to capture future deadlocks, will it write to the sql
error log? Against which database do you run the DBCC trace (master or the
user database)?
Thanks.
RonHi Ron
run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
graph reports in the sql error log when deadlocks occur.
If you have any trouble interpreting them, post the output here & I'm sure
you'll get some help..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> We had a deadlock the other day and would like to identify the offending
> process. Is it too late if there were no traces?
> If we set trace 1204 to capture future deadlocks, will it write to the sql
> error log? Against which database do you run the DBCC trace (master or
> the
> user database)?
> Thanks.
> Ron|||Hi Greg, thanks for the info. Here's what's getting written to the error
log. I don't find it all that helpful. Do I need something else turned on?
Is there a way to convert the SPIDs to users or logins?
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec
0x79A63A30)
Value:0x701
2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
ECID:0 Ec0x0E589528) Value:0x781
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
usp_JC_PrintCycle_UpdatePrintedClaims;1
2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
#: 14
2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
Life:00000001 SPID:57 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 3::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Mode: U Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:2
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
ECID:0 Ec0x79A63A30) Value:0x701
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
#: 1
2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
Life:02000000 SPID:63 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 1::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1
Mode: X Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:1
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 Wait-for graph
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ...
Thanks
Ron
"Greg Linwood" wrote:
> Hi Ron
> run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadloc
k
> graph reports in the sql error log when deadlocks occur.
> If you have any trouble interpreting them, post the output here & I'm sure
> you'll get some help..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
>
>|||Hi Ron
Firstly, it's better to read these by opening up the error log with a text
editor in the file system than via the Enterprise Manager as the Enterprise
Manager reverses the order of display. Here's what the output should look
like:
Node:1
KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
Value:0x701
Node:2
Mode: U Flags: 0x0
KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Grant List 3::
Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:57 ECID:0
SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 14
Input Buf: RPC Event: usp_JC_PrintCycle_UpdatePrintedClaims;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec0x0E589528)
Value:0x781
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
Value:0x701
Note that there are 2 "nodes". Each node represents a resource being locked
& includes information about which connection was "granted" a lock & which
connection has "requested" a lock on the same resource. In this case, Node 1
is an index key lock (ie, an index b-tree page) from database 11, objectid
251199995 & index 27. Node 2 is also a lock on an index key from the same
index. To work out what these numbers represent, you can use the following
queries:
select name from master..sysdatabases where dbid = 11 --gives the database
name
--from within that database
select name from sysobjects where id = 251199995 --gives the table name
select name from sysindexes where id = 251199995 and indid = 27 --gives the
index name
Now you should have the index which is being locked. You can also see the
commands which have acquired the locks from each Node's 'Input Buf' section.
From this, you will see the commands which are taking the respective Node
locks & from here, you probably need to look at each update statement &
determine whether good indexes exist for the filter predicates of the
queries as it often happens with deadlock resolution that the updates are
locking more rows than they need to complete their work etc..
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E8F6BF4-8664-4C82-9907-D9A54CEF5B0B@.microsoft.com...[vbcol=seagreen]
> Hi Greg, thanks for the info. Here's what's getting written to the error
> log. I don't find it all that helpful. Do I need something else turned
> on?
> Is there a way to convert the SPIDs to users or logins?
> ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec0x79A63A30)
> Value:0x701
> 2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
> ECID:0 Ec0x0E589528) Value:0x781
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
> usp_JC_PrintCycle_UpdatePrintedClaims;1
> 2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
> #: 14
> 2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
> Life:00000001 SPID:57 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 3::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3)
> CleanCnt:1
> Mode: U Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:2
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
> ECID:0 Ec0x79A63A30) Value:0x701
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
> VooDoo.dbo.tblClaims_Processing_Professional
> 2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
> #: 1
> 2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
> Life:02000000 SPID:63 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 1::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c)
> CleanCnt:1
> Mode: X Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:1
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 Wait-for graph
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ...
> Thanks
> Ron
> "Greg Linwood" wrote:
>
process. Is it too late if there were no traces?
If we set trace 1204 to capture future deadlocks, will it write to the sql
error log? Against which database do you run the DBCC trace (master or the
user database)?
Thanks.
RonHi Ron
run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
graph reports in the sql error log when deadlocks occur.
If you have any trouble interpreting them, post the output here & I'm sure
you'll get some help..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> We had a deadlock the other day and would like to identify the offending
> process. Is it too late if there were no traces?
> If we set trace 1204 to capture future deadlocks, will it write to the sql
> error log? Against which database do you run the DBCC trace (master or
> the
> user database)?
> Thanks.
> Ron|||Hi Greg, thanks for the info. Here's what's getting written to the error
log. I don't find it all that helpful. Do I need something else turned on?
Is there a way to convert the SPIDs to users or logins?
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec
0x79A63A30)Value:0x701
2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
ECID:0 Ec
0x0E589528) Value:0x7812006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
usp_JC_PrintCycle_UpdatePrintedClaims;1
2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
#: 14
2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
Life:00000001 SPID:57 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 3::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Mode: U Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:2
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
ECID:0 Ec
0x79A63A30) Value:0x7012006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
#: 1
2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
Life:02000000 SPID:63 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 1::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1
Mode: X Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:1
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 Wait-for graph
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ...
Thanks
Ron
"Greg Linwood" wrote:
> Hi Ron
> run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadloc
k
> graph reports in the sql error log when deadlocks occur.
> If you have any trouble interpreting them, post the output here & I'm sure
> you'll get some help..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
>
>|||Hi Ron
Firstly, it's better to read these by opening up the error log with a text
editor in the file system than via the Enterprise Manager as the Enterprise
Manager reverses the order of display. Here's what the output should look
like:
Node:1
KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec
0x79A63A30)Value:0x701
Node:2
Mode: U Flags: 0x0
KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Grant List 3::
Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:57 ECID:0
SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 14
Input Buf: RPC Event: usp_JC_PrintCycle_UpdatePrintedClaims;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec
0x0E589528)Value:0x781
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec
0x79A63A30)Value:0x701
Note that there are 2 "nodes". Each node represents a resource being locked
& includes information about which connection was "granted" a lock & which
connection has "requested" a lock on the same resource. In this case, Node 1
is an index key lock (ie, an index b-tree page) from database 11, objectid
251199995 & index 27. Node 2 is also a lock on an index key from the same
index. To work out what these numbers represent, you can use the following
queries:
select name from master..sysdatabases where dbid = 11 --gives the database
name
--from within that database
select name from sysobjects where id = 251199995 --gives the table name
select name from sysindexes where id = 251199995 and indid = 27 --gives the
index name
Now you should have the index which is being locked. You can also see the
commands which have acquired the locks from each Node's 'Input Buf' section.
From this, you will see the commands which are taking the respective Node
locks & from here, you probably need to look at each update statement &
determine whether good indexes exist for the filter predicates of the
queries as it often happens with deadlock resolution that the updates are
locking more rows than they need to complete their work etc..
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E8F6BF4-8664-4C82-9907-D9A54CEF5B0B@.microsoft.com...[vbcol=seagreen]
> Hi Greg, thanks for the info. Here's what's getting written to the error
> log. I don't find it all that helpful. Do I need something else turned
> on?
> Is there a way to convert the SPIDs to users or logins?
> ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec
0x79A63A30)> Value:0x701
> 2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
> ECID:0 Ec
0x0E589528) Value:0x781> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
> usp_JC_PrintCycle_UpdatePrintedClaims;1
> 2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
> #: 14
> 2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
> Life:00000001 SPID:57 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 3::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3)
> CleanCnt:1
> Mode: U Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:2
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
> ECID:0 Ec
0x79A63A30) Value:0x701> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
> VooDoo.dbo.tblClaims_Processing_Professional
> 2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
> #: 1
> 2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
> Life:02000000 SPID:63 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 1::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c)
> CleanCnt:1
> Mode: X Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:1
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 Wait-for graph
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ...
> Thanks
> Ron
> "Greg Linwood" wrote:
>
Deadlocks and trace 1204
We had a deadlock the other day and would like to identify the offending
process. Is it too late if there were no traces?
If we set trace 1204 to capture future deadlocks, will it write to the sql
error log? Against which database do you run the DBCC trace (master or the
user database)?
Thanks.
RonHi Ron
run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
graph reports in the sql error log when deadlocks occur.
If you have any trouble interpreting them, post the output here & I'm sure
you'll get some help..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> We had a deadlock the other day and would like to identify the offending
> process. Is it too late if there were no traces?
> If we set trace 1204 to capture future deadlocks, will it write to the sql
> error log? Against which database do you run the DBCC trace (master or
> the
> user database)?
> Thanks.
> Ron|||Hi Greg, thanks for the info. Here's what's getting written to the error
log. I don't find it all that helpful. Do I need something else turned on?
Is there a way to convert the SPIDs to users or logins?
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
ECID:0 Ec:(0x0E589528) Value:0x781
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
usp_JC_PrintCycle_UpdatePrintedClaims;1
2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
#: 14
2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
Life:00000001 SPID:57 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 3::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Mode: U Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:2
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
ECID:0 Ec:(0x79A63A30) Value:0x701
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
#: 1
2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
Life:02000000 SPID:63 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 1::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1
Mode: X Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:1
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 Wait-for graph
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ...
Thanks
Ron
"Greg Linwood" wrote:
> Hi Ron
> run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
> graph reports in the sql error log when deadlocks occur.
> If you have any trouble interpreting them, post the output here & I'm sure
> you'll get some help..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> > We had a deadlock the other day and would like to identify the offending
> > process. Is it too late if there were no traces?
> >
> > If we set trace 1204 to capture future deadlocks, will it write to the sql
> > error log? Against which database do you run the DBCC trace (master or
> > the
> > user database)?
> >
> > Thanks.
> >
> > Ron
>
>|||Hi Ron
Firstly, it's better to read these by opening up the error log with a text
editor in the file system than via the Enterprise Manager as the Enterprise
Manager reverses the order of display. Here's what the output should look
like:
Node:1
KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
Node:2
Mode: U Flags: 0x0
KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Grant List 3::
Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:57 ECID:0
SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 14
Input Buf: RPC Event: usp_JC_PrintCycle_UpdatePrintedClaims;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x0E589528)
Value:0x781
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
Note that there are 2 "nodes". Each node represents a resource being locked
& includes information about which connection was "granted" a lock & which
connection has "requested" a lock on the same resource. In this case, Node 1
is an index key lock (ie, an index b-tree page) from database 11, objectid
251199995 & index 27. Node 2 is also a lock on an index key from the same
index. To work out what these numbers represent, you can use the following
queries:
select name from master..sysdatabases where dbid = 11 --gives the database
name
--from within that database
select name from sysobjects where id = 251199995 --gives the table name
select name from sysindexes where id = 251199995 and indid = 27 --gives the
index name
Now you should have the index which is being locked. You can also see the
commands which have acquired the locks from each Node's 'Input Buf' section.
From this, you will see the commands which are taking the respective Node
locks & from here, you probably need to look at each update statement &
determine whether good indexes exist for the filter predicates of the
queries as it often happens with deadlock resolution that the updates are
locking more rows than they need to complete their work etc..
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E8F6BF4-8664-4C82-9907-D9A54CEF5B0B@.microsoft.com...
> Hi Greg, thanks for the info. Here's what's getting written to the error
> log. I don't find it all that helpful. Do I need something else turned
> on?
> Is there a way to convert the SPIDs to users or logins?
> ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
> Value:0x701
> 2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
> ECID:0 Ec:(0x0E589528) Value:0x781
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
> usp_JC_PrintCycle_UpdatePrintedClaims;1
> 2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
> #: 14
> 2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
> Life:00000001 SPID:57 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 3::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3)
> CleanCnt:1
> Mode: U Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:2
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
> ECID:0 Ec:(0x79A63A30) Value:0x701
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
> VooDoo.dbo.tblClaims_Processing_Professional
> 2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
> #: 1
> 2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
> Life:02000000 SPID:63 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 1::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c)
> CleanCnt:1
> Mode: X Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:1
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 Wait-for graph
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ...
> Thanks
> Ron
> "Greg Linwood" wrote:
>> Hi Ron
>> run dbcc traceon (1204, 3605, -1) in any database. You'll then get
>> deadlock
>> graph reports in the sql error log when deadlocks occur.
>> If you have any trouble interpreting them, post the output here & I'm
>> sure
>> you'll get some help..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
>> > We had a deadlock the other day and would like to identify the
>> > offending
>> > process. Is it too late if there were no traces?
>> >
>> > If we set trace 1204 to capture future deadlocks, will it write to the
>> > sql
>> > error log? Against which database do you run the DBCC trace (master or
>> > the
>> > user database)?
>> >
>> > Thanks.
>> >
>> > Ron
>>
process. Is it too late if there were no traces?
If we set trace 1204 to capture future deadlocks, will it write to the sql
error log? Against which database do you run the DBCC trace (master or the
user database)?
Thanks.
RonHi Ron
run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
graph reports in the sql error log when deadlocks occur.
If you have any trouble interpreting them, post the output here & I'm sure
you'll get some help..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> We had a deadlock the other day and would like to identify the offending
> process. Is it too late if there were no traces?
> If we set trace 1204 to capture future deadlocks, will it write to the sql
> error log? Against which database do you run the DBCC trace (master or
> the
> user database)?
> Thanks.
> Ron|||Hi Greg, thanks for the info. Here's what's getting written to the error
log. I don't find it all that helpful. Do I need something else turned on?
Is there a way to convert the SPIDs to users or logins?
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
ECID:0 Ec:(0x0E589528) Value:0x781
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
usp_JC_PrintCycle_UpdatePrintedClaims;1
2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
#: 14
2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
Life:00000001 SPID:57 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 3::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Mode: U Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:2
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
ECID:0 Ec:(0x79A63A30) Value:0x701
2006-11-01 18:04:47.26 spid3 Requested By:
2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
#: 1
2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
Life:02000000 SPID:63 ECID:0
2006-11-01 18:04:47.26 spid3 Grant List 1::
2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1
Mode: X Flags: 0x0
2006-11-01 18:04:47.26 spid3 Node:1
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 Wait-for graph
2006-11-01 18:04:47.26 spid3
2006-11-01 18:04:47.26 spid3 ...
Thanks
Ron
"Greg Linwood" wrote:
> Hi Ron
> run dbcc traceon (1204, 3605, -1) in any database. You'll then get deadlock
> graph reports in the sql error log when deadlocks occur.
> If you have any trouble interpreting them, post the output here & I'm sure
> you'll get some help..
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
> > We had a deadlock the other day and would like to identify the offending
> > process. Is it too late if there were no traces?
> >
> > If we set trace 1204 to capture future deadlocks, will it write to the sql
> > error log? Against which database do you run the DBCC trace (master or
> > the
> > user database)?
> >
> > Thanks.
> >
> > Ron
>
>|||Hi Ron
Firstly, it's better to read these by opening up the error log with a text
editor in the file system than via the Enterprise Manager as the Enterprise
Manager reverses the order of display. Here's what the output should look
like:
Node:1
KEY: 11:251199995:27 (0c017686e61c) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: UPDATE
VooDoo.dbo.tblClaims_Processing_Professional
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
Node:2
Mode: U Flags: 0x0
KEY: 11:251199995:27 (e2002cc969d3) CleanCnt:1
Grant List 3::
Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:57 ECID:0
SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 14
Input Buf: RPC Event: usp_JC_PrintCycle_UpdatePrintedClaims;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x0E589528)
Value:0x781
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
Value:0x701
Note that there are 2 "nodes". Each node represents a resource being locked
& includes information about which connection was "granted" a lock & which
connection has "requested" a lock on the same resource. In this case, Node 1
is an index key lock (ie, an index b-tree page) from database 11, objectid
251199995 & index 27. Node 2 is also a lock on an index key from the same
index. To work out what these numbers represent, you can use the following
queries:
select name from master..sysdatabases where dbid = 11 --gives the database
name
--from within that database
select name from sysobjects where id = 251199995 --gives the table name
select name from sysindexes where id = 251199995 and indid = 27 --gives the
index name
Now you should have the index which is being locked. You can also see the
commands which have acquired the locks from each Node's 'Input Buf' section.
From this, you will see the commands which are taking the respective Node
locks & from here, you probably need to look at each update statement &
determine whether good indexes exist for the filter predicates of the
queries as it often happens with deadlock resolution that the updates are
locking more rows than they need to complete their work etc..
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:0E8F6BF4-8664-4C82-9907-D9A54CEF5B0B@.microsoft.com...
> Hi Greg, thanks for the info. Here's what's getting written to the error
> log. I don't find it all that helpful. Do I need something else turned
> on?
> Is there a way to convert the SPIDs to users or logins?
> ResType:LockOwner Stype:'OR' Mode: U SPID:57 ECID:0 Ec:(0x79A63A30)
> Value:0x701
> 2006-11-01 18:04:47.26 spid3 Victim Resource Owner:
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: X SPID:63
> ECID:0 Ec:(0x0E589528) Value:0x781
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: RPC Event:
> usp_JC_PrintCycle_UpdatePrintedClaims;1
> 2006-11-01 18:04:47.26 spid3 SPID: 57 ECID: 0 Statement Type: UPDATE Line
> #: 14
> 2006-11-01 18:04:47.26 spid3 Owner:0x70103cc0 Mode: U Flg:0x0 Ref:0
> Life:00000001 SPID:57 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 3::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (e2002cc969d3)
> CleanCnt:1
> Mode: U Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:2
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:57
> ECID:0 Ec:(0x79A63A30) Value:0x701
> 2006-11-01 18:04:47.26 spid3 Requested By:
> 2006-11-01 18:04:47.26 spid3 Input Buf: Language Event: UPDATE
> VooDoo.dbo.tblClaims_Processing_Professional
> 2006-11-01 18:04:47.26 spid3 SPID: 63 ECID: 0 Statement Type: UPDATE Line
> #: 1
> 2006-11-01 18:04:47.26 spid3 Owner:0xfc4d900 Mode: X Flg:0x0 Ref:0
> Life:02000000 SPID:63 ECID:0
> 2006-11-01 18:04:47.26 spid3 Grant List 1::
> 2006-11-01 18:04:47.26 spid3 KEY: 11:251199995:27 (0c017686e61c)
> CleanCnt:1
> Mode: X Flags: 0x0
> 2006-11-01 18:04:47.26 spid3 Node:1
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 Wait-for graph
> 2006-11-01 18:04:47.26 spid3
> 2006-11-01 18:04:47.26 spid3 ...
> Thanks
> Ron
> "Greg Linwood" wrote:
>> Hi Ron
>> run dbcc traceon (1204, 3605, -1) in any database. You'll then get
>> deadlock
>> graph reports in the sql error log when deadlocks occur.
>> If you have any trouble interpreting them, post the output here & I'm
>> sure
>> you'll get some help..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:43C58E88-D125-4DE3-9244-72CB1968BD0D@.microsoft.com...
>> > We had a deadlock the other day and would like to identify the
>> > offending
>> > process. Is it too late if there were no traces?
>> >
>> > If we set trace 1204 to capture future deadlocks, will it write to the
>> > sql
>> > error log? Against which database do you run the DBCC trace (master or
>> > the
>> > user database)?
>> >
>> > Thanks.
>> >
>> > Ron
>>
Tuesday, March 27, 2012
DeadLocking
I need help.
We keep having deadlocking. The deadlocking trace points me to a statistic
update. The KEY: 5:242972092:25 index lock it points to is a SQL Server
automatically created statistic. It is on a foreign key column.
I have tried turning autoUpdate Stats off and we still get the deadlock.
Trace Listed below. Does anyone have any ideas? I have never seen a deadlock
on a statistic.
01/12/2006 13:36:30,spid4,Unknown,Node:1
01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:1 (de001a40a963)
CleanCnt:1 Mode: X Flags: 0x0
01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
01/12/2006 13:36:30,spid4,Unknown,Owner:0x2e84d360 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:61 ECID:0
01/12/2006 13:36:30,spid4,Unknown,SPID: 61 ECID: 0 Statement Type: UPDATE
Line #: 82
01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
up_updateShipmentRequestLine;1
01/12/2006 13:36:30,spid4,Unknown,Requested By:
01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)
01/12/2006 13:36:30,spid4,Unknown,
01/12/2006 13:36:30,spid4,Unknown,Node:2
01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:25 (be027cd3b404)
CleanCnt:1 Mode: S Flags: 0x0
01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
01/12/2006 13:36:30,spid4,Unknown,Owner:0x4e241860 Mode: S Flg:0x0
Ref:0 Life:02000000 SPID:56 ECID:0
01/12/2006 13:36:30,spid4,Unknown,SPID: 56 ECID: 0 Statement Type: SELECT
Line #: 9
01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
01/12/2006 13:36:30,spid4,Unknown,Requested By:
01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: X
SPID:61 ECID:0 Ec:(0x235D5528) Value:0x5bc13d40 Cost:(0/C4)
01/12/2006 13:36:30,spid4,Unknown,Victim Resource Owner:
01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)I see an exclusive lock generated by:
UPDATE Line #: 82 in up_updateShipmentRequestLine;1
and a shared lock generated by
SELECT Line #: 9 in
up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
You may want to look in the code in these two stored procedures (?). You may
be accessing tables in reverse order.
Probably the fix should go into the [up_updateShipmentRequestLine].
If you have
SELECT @.bExists = Field1 FROM Table1
and then
IF @.bExists = someVal
UPDATE Table1 ...
Instead do first:
UPDATE Table1 SET Field1 = @.Val1
IF @.@.ROWCOUNT == 0
INSERT ...
Ok. I'm making assumptions here since I do not know your code but the rule
is that you want to get the highest lock since the beginning of the sproc an
d
there are many ways you can do that. One is above.
If you do not want to change the logic of the code, place a Locking Hints
using
WITH( ... )
for example WITH(UPDLOCK).
If you want more details then you need to post some code so I can point you
exactly to code that generates the deadlock.
"JI" wrote:
> I need help.
> We keep having deadlocking. The deadlocking trace points me to a statistic
> update. The KEY: 5:242972092:25 index lock it points to is a SQL Server
> automatically created statistic. It is on a foreign key column.
> I have tried turning autoUpdate Stats off and we still get the deadlock.
> Trace Listed below. Does anyone have any ideas? I have never seen a deadlo
ck
> on a statistic.
> 01/12/2006 13:36:30,spid4,Unknown,Node:1
> 01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:1 (de001a40a963)
> CleanCnt:1 Mode: X Flags: 0x0
> 01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
> 01/12/2006 13:36:30,spid4,Unknown,Owner:0x2e84d360 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:61 ECID:0
> 01/12/2006 13:36:30,spid4,Unknown,SPID: 61 ECID: 0 Statement Type: UPDATE
> Line #: 82
> 01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
> up_updateShipmentRequestLine;1
> 01/12/2006 13:36:30,spid4,Unknown,Requested By:
> 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
> SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)
> 01/12/2006 13:36:30,spid4,Unknown,
> 01/12/2006 13:36:30,spid4,Unknown,Node:2
> 01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:25 (be027cd3b404)
> CleanCnt:1 Mode: S Flags: 0x0
> 01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
> 01/12/2006 13:36:30,spid4,Unknown,Owner:0x4e241860 Mode: S Flg:0x0
> Ref:0 Life:02000000 SPID:56 ECID:0
> 01/12/2006 13:36:30,spid4,Unknown,SPID: 56 ECID: 0 Statement Type: SELECT
> Line #: 9
> 01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
> up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
> 01/12/2006 13:36:30,spid4,Unknown,Requested By:
> 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: X
> SPID:61 ECID:0 Ec:(0x235D5528) Value:0x5bc13d40 Cost:(0/C4)
> 01/12/2006 13:36:30,spid4,Unknown,Victim Resource Owner:
> 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
> SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)
>
>|||The update proc is one that I wrote a proc generator to create. It does a
simple update...it does not access any other or the same table before the
update. The interesting thing with the deadlock trace information is the
index that is says deadlocks is a statistic. One created by SQL Server...
I will post the update shipment request line proc below anyway.
alter proc [dbo].[up_updateShipmentRequestLine]
@.iError int OUTPUT
,@.guidShipmentRequestLineId uniqueidentifier
,@.guidShipmentRequestId uniqueidentifier
,@.iLineNumber int
,@.guidLotId uniqueidentifier
,@.sPurchaseOrderNumber char(50)
,@.sFullLotInd char(1)
,@.iMinimumCount int
,@.daDateNeeded datetime
,@.dcQuantity decimal(18,0)
,@.guidDestinationPlantId uniqueidentifier
,@.guidShipmentStatusId uniqueidentifier
,@.sShippingGroup char(3)
,@.sLineCreateUserName char(50)
,@.daLineCreateDate datetime
,@.sLineModifyUserName char(50)
,@.daLineModifyDate datetime
,@.daModifyDateTime datetime
,@.guidModifyUserId uniqueidentifier
,@.guidReferenceId uniqueidentifier
,@.useBitMap char(1) = 'F'
as
begin
Set NoCount On
Declare @.iCnt int
,@.bitMap varbinary(10)
,@.bitMapByte1 int
,@.bitMapByte2 int
,@.bitMapByte3 int
,@.bitMapByte4 int
,@.bitMapByte5 int
,@.bitMapByte6 int
,@.bitMapByte7 int
,@.bitMapByte8 int
,@.bitMapByte9 int
,@.bitMapByte10 int
If @.useBitMap = 'T' Begin
Select @.bitMapByte1 = Case When @.guidShipmentRequestLineId is null Then 0
Else Power(2,0) End
+ Case When @.guidShipmentRequestId is null Then 0 Else Power(2,1) End
+ Case When @.iLineNumber is null Then 0 Else Power(2,2) End
+ Case When @.guidLotId is null Then 0 Else Power(2,3) End
+ Case When @.sPurchaseOrderNumber is null Then 0 Else Power(2,4) End
+ Case When @.sFullLotInd is null Then 0 Else Power(2,5) End
+ Case When @.iMinimumCount is null Then 0 Else Power(2,6) End
+ Case When @.daDateNeeded is null Then 0 Else Power(2,7) End
Select @.bitMapByte2 = Case When @.dcQuantity is null Then 0 Else Power(2,0)
End
+ Case When @.guidDestinationPlantId is null Then 0 Else Power(2,1) End
+ Case When @.guidShipmentStatusId is null Then 0 Else Power(2,2) End
+ Case When @.sShippingGroup is null Then 0 Else Power(2,3) End
+ Case When @.sLineCreateUserName is null Then 0 Else Power(2,4) End
+ Case When @.daLineCreateDate is null Then 0 Else Power(2,5) End
+ Case When @.sLineModifyUserName is null Then 0 Else Power(2,6) End
+ Case When @.daLineModifyDate is null Then 0 Else Power(2,7) End
Select @.bitMapByte3 = Case When @.daModifyDateTime is null Then 0 Else
Power(2,2) End
+ Case When @.guidModifyUserId is null Then 0 Else Power(2,3) End
+ Case When @.guidReferenceId is null Then 0 Else Power(2,6) End
select @.bitmap = convert(binary(1),isNull(@.bitMapByte1,0)
)
+convert(binary(1),isNull(@.bitMapByte2,0
))
+convert(binary(1),isNull(@.bitMapByte3,0
))
+convert(binary(1),isNull(@.bitMapByte4,0
))
+convert(binary(1),isNull(@.bitMapByte5,0
))
+convert(binary(1),isNull(@.bitMapByte6,0
))
+convert(binary(1),isNull(@.bitMapByte7,0
))
+convert(binary(1),isNull(@.bitMapByte8,0
))
+convert(binary(1),isNull(@.bitMapByte9,0
))
+convert(binary(1),isNull(@.bitMapByte10,
0))
End
begin transaction
Update ShipmentRequestLine
Set [ShipmentRequestLineId] = Case isNull(substring(@.bitmap,1,1),1) & 1 when
1 Then @.guidShipmentRequestLineId Else [ShipmentRequestLineId] End
,[ShipmentRequestId] = Case isNull(substring(@.bitmap,1,1),2) & 2 when 2 Then
@.guidShipmentRequestId Else [ShipmentRequestId] End
,[LineNumber] = Case isNull(substring(@.bitmap,1,1),4) & 4 when 4 Then
@.iLineNumber Else [LineNumber] End
,[LotId] = Case isNull(substring(@.bitmap,1,1),8) & 8 when 8 Then @.guidLotId
Else [LotId] End
,[PurchaseOrderNumber] = Case isNull(substring(@.bitmap,1,1),16) & 16 when 16
Then @.sPurchaseOrderNumber Else [PurchaseOrderNumber] End
,[FullLotInd] = Case isNull(substring(@.bitmap,1,1),32) & 32 when 32 Then
@.sFullLotInd Else [FullLotInd] End
,[MinimumCount] = Case isNull(substring(@.bitmap,1,1),64) & 64 when 64 Then
@.iMinimumCount Else [MinimumCount] End
,[DateNeeded] = Case isNull(substring(@.bitmap,1,1),128) & 128 when 128 Then
@.daDateNeeded Else [DateNeeded] End
,[Quantity] = Case isNull(substring(@.bitmap,2,1),1) & 1 when 1 Then
@.dcQuantity Else [Quantity] End
,[DestinationPlantId] = Case isNull(substring(@.bitmap,2,1),2) & 2 when 2
Then @.guidDestinationPlantId Else [DestinationPlantId] End
,[ShipmentStatusId] = Case isNull(substring(@.bitmap,2,1),4) & 4 when 4 Then
@.guidShipmentStatusId Else [ShipmentStatusId] End
,[ShippingGroup] = Case isNull(substring(@.bitmap,2,1),8) & 8 when 8 Then
@.sShippingGroup Else [ShippingGroup] End
,[LineCreateUserName] = Case isNull(substring(@.bitmap,2,1),16) & 16 when 16
Then @.sLineCreateUserName Else [LineCreateUserName] End
,[LineCreateDate] = Case isNull(substring(@.bitmap,2,1),32) & 32 when 32 Then
@.daLineCreateDate Else [LineCreateDate] End
,[LineModifyUserName] = Case isNull(substring(@.bitmap,2,1),64) & 64 when 64
Then @.sLineModifyUserName Else [LineModifyUserName] End
,[LineModifyDate] = Case isNull(substring(@.bitmap,2,1),128) & 128 when 128
Then @.daLineModifyDate Else [LineModifyDate] End
,[ModifyDateTime] = isNull(@.daModifyDateTime,getDate())
,[ModifyUserId] = Case isNull(substring(@.bitmap,3,1),8) & 8 when 8 Then
@.guidModifyUserId Else [ModifyUserId] End
,[ReferenceId] = Case isNull(substring(@.bitmap,3,1),64) & 64 when 64 Then
@.guidReferenceId Else [ReferenceId] End
where ShipmentRequestLineId = @.guidShipmentRequestLineId
SELECT @.iError=@.@.ERROR, @.iCnt = @.@.rowCount
If @.iError <> 0 begin
Rollback Transaction
End
Else Begin
Commit Transaction
End
Return @.iCnt
End
"Daniel P." <DanielP@.discussions.microsoft.com> wrote in message
news:6FC61F2E-A1FD-43F1-917A-9A3BD6A7E782@.microsoft.com...
>I see an exclusive lock generated by:
> UPDATE Line #: 82 in up_updateShipmentRequestLine;1
> and a shared lock generated by
> SELECT Line #: 9 in
> up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
> You may want to look in the code in these two stored procedures (?). You
> may
> be accessing tables in reverse order.
> Probably the fix should go into the [up_updateShipmentRequestLine].
> If you have
> SELECT @.bExists = Field1 FROM Table1
> and then
> IF @.bExists = someVal
> UPDATE Table1 ...
> Instead do first:
> UPDATE Table1 SET Field1 = @.Val1
> IF @.@.ROWCOUNT == 0
> INSERT ...
> Ok. I'm making assumptions here since I do not know your code but the rule
> is that you want to get the highest lock since the beginning of the sproc
> and
> there are many ways you can do that. One is above.
> If you do not want to change the logic of the code, place a Locking Hints
> using
> WITH( ... )
> for example WITH(UPDLOCK).
> If you want more details then you need to post some code so I can point
> you
> exactly to code that generates the deadlock.
>
> "JI" wrote:
>|||Set the transaction isolation level as serializable or add the hint
WITH(TABLOCKX) and see if you still get the deadlock.
"JI" wrote:
> The update proc is one that I wrote a proc generator to create. It does a
> simple update...it does not access any other or the same table before the
> update. The interesting thing with the deadlock trace information is the
> index that is says deadlocks is a statistic. One created by SQL Server...
> I will post the update shipment request line proc below anyway.
> alter proc [dbo].[up_updateShipmentRequestLine]
> @.iError int OUTPUT
> ,@.guidShipmentRequestLineId uniqueidentifier
> ,@.guidShipmentRequestId uniqueidentifier
> ,@.iLineNumber int
> ,@.guidLotId uniqueidentifier
> ,@.sPurchaseOrderNumber char(50)
> ,@.sFullLotInd char(1)
> ,@.iMinimumCount int
> ,@.daDateNeeded datetime
> ,@.dcQuantity decimal(18,0)
> ,@.guidDestinationPlantId uniqueidentifier
> ,@.guidShipmentStatusId uniqueidentifier
> ,@.sShippingGroup char(3)
> ,@.sLineCreateUserName char(50)
> ,@.daLineCreateDate datetime
> ,@.sLineModifyUserName char(50)
> ,@.daLineModifyDate datetime
> ,@.daModifyDateTime datetime
> ,@.guidModifyUserId uniqueidentifier
> ,@.guidReferenceId uniqueidentifier
> ,@.useBitMap char(1) = 'F'
> as
> begin
> Set NoCount On
> Declare @.iCnt int
> ,@.bitMap varbinary(10)
> ,@.bitMapByte1 int
> ,@.bitMapByte2 int
> ,@.bitMapByte3 int
> ,@.bitMapByte4 int
> ,@.bitMapByte5 int
> ,@.bitMapByte6 int
> ,@.bitMapByte7 int
> ,@.bitMapByte8 int
> ,@.bitMapByte9 int
> ,@.bitMapByte10 int
> If @.useBitMap = 'T' Begin
> Select @.bitMapByte1 = Case When @.guidShipmentRequestLineId is null Then 0
> Else Power(2,0) End
> + Case When @.guidShipmentRequestId is null Then 0 Else Power(2,1) End
> + Case When @.iLineNumber is null Then 0 Else Power(2,2) End
> + Case When @.guidLotId is null Then 0 Else Power(2,3) End
> + Case When @.sPurchaseOrderNumber is null Then 0 Else Power(2,4) End
> + Case When @.sFullLotInd is null Then 0 Else Power(2,5) End
> + Case When @.iMinimumCount is null Then 0 Else Power(2,6) End
> + Case When @.daDateNeeded is null Then 0 Else Power(2,7) End
> Select @.bitMapByte2 = Case When @.dcQuantity is null Then 0 Else Power(2,0)
> End
> + Case When @.guidDestinationPlantId is null Then 0 Else Power(2,1) End
> + Case When @.guidShipmentStatusId is null Then 0 Else Power(2,2) End
> + Case When @.sShippingGroup is null Then 0 Else Power(2,3) End
> + Case When @.sLineCreateUserName is null Then 0 Else Power(2,4) End
> + Case When @.daLineCreateDate is null Then 0 Else Power(2,5) End
> + Case When @.sLineModifyUserName is null Then 0 Else Power(2,6) End
> + Case When @.daLineModifyDate is null Then 0 Else Power(2,7) End
> Select @.bitMapByte3 = Case When @.daModifyDateTime is null Then 0 Else
> Power(2,2) End
> + Case When @.guidModifyUserId is null Then 0 Else Power(2,3) End
> + Case When @.guidReferenceId is null Then 0 Else Power(2,6) End
> select @.bitmap = convert(binary(1),isNull(@.bitMapByte1,0)
)
> +convert(binary(1),isNull(@.bitMapByte2,0
))
> +convert(binary(1),isNull(@.bitMapByte3,0
))
> +convert(binary(1),isNull(@.bitMapByte4,0
))
> +convert(binary(1),isNull(@.bitMapByte5,0
))
> +convert(binary(1),isNull(@.bitMapByte6,0
))
> +convert(binary(1),isNull(@.bitMapByte7,0
))
> +convert(binary(1),isNull(@.bitMapByte8,0
))
> +convert(binary(1),isNull(@.bitMapByte9,0
))
> +convert(binary(1),isNull(@.bitMapByte10,
0))
> End
>
> begin transaction
> Update ShipmentRequestLine
> Set [ShipmentRequestLineId] = Case isNull(substring(@.bitmap,1,1),1) & 1 when
> 1 Then @.guidShipmentRequestLineId Else [ShipmentRequestLineId] End
> ,[ShipmentRequestId] = Case isNull(substring(@.bitmap,1,1),2) & 2 when 2 Then
> @.guidShipmentRequestId Else [ShipmentRequestId] End
> ,[LineNumber] = Case isNull(substring(@.bitmap,1,1),4) & 4 when 4 Then
> @.iLineNumber Else [LineNumber] End
> ,[LotId] = Case isNull(substring(@.bitmap,1,1),8) & 8 when 8 Then @.guidLotId
> Else [LotId] End
> ,[PurchaseOrderNumber] = Case isNull(substring(@.bitmap,1,1),16) & 16 when 16
> Then @.sPurchaseOrderNumber Else [PurchaseOrderNumber] End
> ,[FullLotInd] = Case isNull(substring(@.bitmap,1,1),32) & 32 when 32 Then
> @.sFullLotInd Else [FullLotInd] End
> ,[MinimumCount] = Case isNull(substring(@.bitmap,1,1),64) & 64 when 64 Then
> @.iMinimumCount Else [MinimumCount] End
> ,[DateNeeded] = Case isNull(substring(@.bitmap,1,1),128) & 128 when 128 Then
> @.daDateNeeded Else [DateNeeded] End
> ,[Quantity] = Case isNull(substring(@.bitmap,2,1),1) & 1 when 1 Then
> @.dcQuantity Else [Quantity] End
> ,[DestinationPlantId] = Case isNull(substring(@.bitmap,2,1),2) & 2 when 2
> Then @.guidDestinationPlantId Else [DestinationPlantId] End
> ,[ShipmentStatusId] = Case isNull(substring(@.bitmap,2,1),4) & 4 when 4 Then
> @.guidShipmentStatusId Else [ShipmentStatusId] End
> ,[ShippingGroup] = Case isNull(substring(@.bitmap,2,1),8) & 8 when 8 Then
> @.sShippingGroup Else [ShippingGroup] End
> ,[LineCreateUserName] = Case isNull(substring(@.bitmap,2,1),16) & 16 when 16
> Then @.sLineCreateUserName Else [LineCreateUserName] End
> ,[LineCreateDate] = Case isNull(substring(@.bitmap,2,1),32) & 32 when 32 Then
> @.daLineCreateDate Else [LineCreateDate] End
> ,[LineModifyUserName] = Case isNull(substring(@.bitmap,2,1),64) & 64 when 64
> Then @.sLineModifyUserName Else [LineModifyUserName] End
> ,[LineModifyDate] = Case isNull(substring(@.bitmap,2,1),128) & 128 when 128
> Then @.daLineModifyDate Else [LineModifyDate] End
> ,[ModifyDateTime] = isNull(@.daModifyDateTime,getDate())
> ,[ModifyUserId] = Case isNull(substring(@.bitmap,3,1),8) & 8 when 8 Then
> @.guidModifyUserId Else [ModifyUserId] End
> ,[ReferenceId] = Case isNull(substring(@.bitmap,3,1),64) & 64 when 64 Then
> @.guidReferenceId Else [ReferenceId] End
> where ShipmentRequestLineId = @.guidShipmentRequestLineId
> SELECT @.iError=@.@.ERROR, @.iCnt = @.@.rowCount
>
> If @.iError <> 0 begin
> Rollback Transaction
> End
> Else Begin
> Commit Transaction
> End
> Return @.iCnt
> End
> "Daniel P." <DanielP@.discussions.microsoft.com> wrote in message
> news:6FC61F2E-A1FD-43F1-917A-9A3BD6A7E782@.microsoft.com...
>
>
We keep having deadlocking. The deadlocking trace points me to a statistic
update. The KEY: 5:242972092:25 index lock it points to is a SQL Server
automatically created statistic. It is on a foreign key column.
I have tried turning autoUpdate Stats off and we still get the deadlock.
Trace Listed below. Does anyone have any ideas? I have never seen a deadlock
on a statistic.
01/12/2006 13:36:30,spid4,Unknown,Node:1
01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:1 (de001a40a963)
CleanCnt:1 Mode: X Flags: 0x0
01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
01/12/2006 13:36:30,spid4,Unknown,Owner:0x2e84d360 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:61 ECID:0
01/12/2006 13:36:30,spid4,Unknown,SPID: 61 ECID: 0 Statement Type: UPDATE
Line #: 82
01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
up_updateShipmentRequestLine;1
01/12/2006 13:36:30,spid4,Unknown,Requested By:
01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)
01/12/2006 13:36:30,spid4,Unknown,
01/12/2006 13:36:30,spid4,Unknown,Node:2
01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:25 (be027cd3b404)
CleanCnt:1 Mode: S Flags: 0x0
01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
01/12/2006 13:36:30,spid4,Unknown,Owner:0x4e241860 Mode: S Flg:0x0
Ref:0 Life:02000000 SPID:56 ECID:0
01/12/2006 13:36:30,spid4,Unknown,SPID: 56 ECID: 0 Statement Type: SELECT
Line #: 9
01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
01/12/2006 13:36:30,spid4,Unknown,Requested By:
01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: X
SPID:61 ECID:0 Ec:(0x235D5528) Value:0x5bc13d40 Cost:(0/C4)
01/12/2006 13:36:30,spid4,Unknown,Victim Resource Owner:
01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)I see an exclusive lock generated by:
UPDATE Line #: 82 in up_updateShipmentRequestLine;1
and a shared lock generated by
SELECT Line #: 9 in
up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
You may want to look in the code in these two stored procedures (?). You may
be accessing tables in reverse order.
Probably the fix should go into the [up_updateShipmentRequestLine].
If you have
SELECT @.bExists = Field1 FROM Table1
and then
IF @.bExists = someVal
UPDATE Table1 ...
Instead do first:
UPDATE Table1 SET Field1 = @.Val1
IF @.@.ROWCOUNT == 0
INSERT ...
Ok. I'm making assumptions here since I do not know your code but the rule
is that you want to get the highest lock since the beginning of the sproc an
d
there are many ways you can do that. One is above.
If you do not want to change the logic of the code, place a Locking Hints
using
WITH( ... )
for example WITH(UPDLOCK).
If you want more details then you need to post some code so I can point you
exactly to code that generates the deadlock.
"JI" wrote:
> I need help.
> We keep having deadlocking. The deadlocking trace points me to a statistic
> update. The KEY: 5:242972092:25 index lock it points to is a SQL Server
> automatically created statistic. It is on a foreign key column.
> I have tried turning autoUpdate Stats off and we still get the deadlock.
> Trace Listed below. Does anyone have any ideas? I have never seen a deadlo
ck
> on a statistic.
> 01/12/2006 13:36:30,spid4,Unknown,Node:1
> 01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:1 (de001a40a963)
> CleanCnt:1 Mode: X Flags: 0x0
> 01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
> 01/12/2006 13:36:30,spid4,Unknown,Owner:0x2e84d360 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:61 ECID:0
> 01/12/2006 13:36:30,spid4,Unknown,SPID: 61 ECID: 0 Statement Type: UPDATE
> Line #: 82
> 01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
> up_updateShipmentRequestLine;1
> 01/12/2006 13:36:30,spid4,Unknown,Requested By:
> 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
> SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)
> 01/12/2006 13:36:30,spid4,Unknown,
> 01/12/2006 13:36:30,spid4,Unknown,Node:2
> 01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:25 (be027cd3b404)
> CleanCnt:1 Mode: S Flags: 0x0
> 01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
> 01/12/2006 13:36:30,spid4,Unknown,Owner:0x4e241860 Mode: S Flg:0x0
> Ref:0 Life:02000000 SPID:56 ECID:0
> 01/12/2006 13:36:30,spid4,Unknown,SPID: 56 ECID: 0 Statement Type: SELECT
> Line #: 9
> 01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
> up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
> 01/12/2006 13:36:30,spid4,Unknown,Requested By:
> 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: X
> SPID:61 ECID:0 Ec:(0x235D5528) Value:0x5bc13d40 Cost:(0/C4)
> 01/12/2006 13:36:30,spid4,Unknown,Victim Resource Owner:
> 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
> SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)
>
>|||The update proc is one that I wrote a proc generator to create. It does a
simple update...it does not access any other or the same table before the
update. The interesting thing with the deadlock trace information is the
index that is says deadlocks is a statistic. One created by SQL Server...
I will post the update shipment request line proc below anyway.
alter proc [dbo].[up_updateShipmentRequestLine]
@.iError int OUTPUT
,@.guidShipmentRequestLineId uniqueidentifier
,@.guidShipmentRequestId uniqueidentifier
,@.iLineNumber int
,@.guidLotId uniqueidentifier
,@.sPurchaseOrderNumber char(50)
,@.sFullLotInd char(1)
,@.iMinimumCount int
,@.daDateNeeded datetime
,@.dcQuantity decimal(18,0)
,@.guidDestinationPlantId uniqueidentifier
,@.guidShipmentStatusId uniqueidentifier
,@.sShippingGroup char(3)
,@.sLineCreateUserName char(50)
,@.daLineCreateDate datetime
,@.sLineModifyUserName char(50)
,@.daLineModifyDate datetime
,@.daModifyDateTime datetime
,@.guidModifyUserId uniqueidentifier
,@.guidReferenceId uniqueidentifier
,@.useBitMap char(1) = 'F'
as
begin
Set NoCount On
Declare @.iCnt int
,@.bitMap varbinary(10)
,@.bitMapByte1 int
,@.bitMapByte2 int
,@.bitMapByte3 int
,@.bitMapByte4 int
,@.bitMapByte5 int
,@.bitMapByte6 int
,@.bitMapByte7 int
,@.bitMapByte8 int
,@.bitMapByte9 int
,@.bitMapByte10 int
If @.useBitMap = 'T' Begin
Select @.bitMapByte1 = Case When @.guidShipmentRequestLineId is null Then 0
Else Power(2,0) End
+ Case When @.guidShipmentRequestId is null Then 0 Else Power(2,1) End
+ Case When @.iLineNumber is null Then 0 Else Power(2,2) End
+ Case When @.guidLotId is null Then 0 Else Power(2,3) End
+ Case When @.sPurchaseOrderNumber is null Then 0 Else Power(2,4) End
+ Case When @.sFullLotInd is null Then 0 Else Power(2,5) End
+ Case When @.iMinimumCount is null Then 0 Else Power(2,6) End
+ Case When @.daDateNeeded is null Then 0 Else Power(2,7) End
Select @.bitMapByte2 = Case When @.dcQuantity is null Then 0 Else Power(2,0)
End
+ Case When @.guidDestinationPlantId is null Then 0 Else Power(2,1) End
+ Case When @.guidShipmentStatusId is null Then 0 Else Power(2,2) End
+ Case When @.sShippingGroup is null Then 0 Else Power(2,3) End
+ Case When @.sLineCreateUserName is null Then 0 Else Power(2,4) End
+ Case When @.daLineCreateDate is null Then 0 Else Power(2,5) End
+ Case When @.sLineModifyUserName is null Then 0 Else Power(2,6) End
+ Case When @.daLineModifyDate is null Then 0 Else Power(2,7) End
Select @.bitMapByte3 = Case When @.daModifyDateTime is null Then 0 Else
Power(2,2) End
+ Case When @.guidModifyUserId is null Then 0 Else Power(2,3) End
+ Case When @.guidReferenceId is null Then 0 Else Power(2,6) End
select @.bitmap = convert(binary(1),isNull(@.bitMapByte1,0)
)
+convert(binary(1),isNull(@.bitMapByte2,0
))
+convert(binary(1),isNull(@.bitMapByte3,0
))
+convert(binary(1),isNull(@.bitMapByte4,0
))
+convert(binary(1),isNull(@.bitMapByte5,0
))
+convert(binary(1),isNull(@.bitMapByte6,0
))
+convert(binary(1),isNull(@.bitMapByte7,0
))
+convert(binary(1),isNull(@.bitMapByte8,0
))
+convert(binary(1),isNull(@.bitMapByte9,0
))
+convert(binary(1),isNull(@.bitMapByte10,
0))
End
begin transaction
Update ShipmentRequestLine
Set [ShipmentRequestLineId] = Case isNull(substring(@.bitmap,1,1),1) & 1 when
1 Then @.guidShipmentRequestLineId Else [ShipmentRequestLineId] End
,[ShipmentRequestId] = Case isNull(substring(@.bitmap,1,1),2) & 2 when 2 Then
@.guidShipmentRequestId Else [ShipmentRequestId] End
,[LineNumber] = Case isNull(substring(@.bitmap,1,1),4) & 4 when 4 Then
@.iLineNumber Else [LineNumber] End
,[LotId] = Case isNull(substring(@.bitmap,1,1),8) & 8 when 8 Then @.guidLotId
Else [LotId] End
,[PurchaseOrderNumber] = Case isNull(substring(@.bitmap,1,1),16) & 16 when 16
Then @.sPurchaseOrderNumber Else [PurchaseOrderNumber] End
,[FullLotInd] = Case isNull(substring(@.bitmap,1,1),32) & 32 when 32 Then
@.sFullLotInd Else [FullLotInd] End
,[MinimumCount] = Case isNull(substring(@.bitmap,1,1),64) & 64 when 64 Then
@.iMinimumCount Else [MinimumCount] End
,[DateNeeded] = Case isNull(substring(@.bitmap,1,1),128) & 128 when 128 Then
@.daDateNeeded Else [DateNeeded] End
,[Quantity] = Case isNull(substring(@.bitmap,2,1),1) & 1 when 1 Then
@.dcQuantity Else [Quantity] End
,[DestinationPlantId] = Case isNull(substring(@.bitmap,2,1),2) & 2 when 2
Then @.guidDestinationPlantId Else [DestinationPlantId] End
,[ShipmentStatusId] = Case isNull(substring(@.bitmap,2,1),4) & 4 when 4 Then
@.guidShipmentStatusId Else [ShipmentStatusId] End
,[ShippingGroup] = Case isNull(substring(@.bitmap,2,1),8) & 8 when 8 Then
@.sShippingGroup Else [ShippingGroup] End
,[LineCreateUserName] = Case isNull(substring(@.bitmap,2,1),16) & 16 when 16
Then @.sLineCreateUserName Else [LineCreateUserName] End
,[LineCreateDate] = Case isNull(substring(@.bitmap,2,1),32) & 32 when 32 Then
@.daLineCreateDate Else [LineCreateDate] End
,[LineModifyUserName] = Case isNull(substring(@.bitmap,2,1),64) & 64 when 64
Then @.sLineModifyUserName Else [LineModifyUserName] End
,[LineModifyDate] = Case isNull(substring(@.bitmap,2,1),128) & 128 when 128
Then @.daLineModifyDate Else [LineModifyDate] End
,[ModifyDateTime] = isNull(@.daModifyDateTime,getDate())
,[ModifyUserId] = Case isNull(substring(@.bitmap,3,1),8) & 8 when 8 Then
@.guidModifyUserId Else [ModifyUserId] End
,[ReferenceId] = Case isNull(substring(@.bitmap,3,1),64) & 64 when 64 Then
@.guidReferenceId Else [ReferenceId] End
where ShipmentRequestLineId = @.guidShipmentRequestLineId
SELECT @.iError=@.@.ERROR, @.iCnt = @.@.rowCount
If @.iError <> 0 begin
Rollback Transaction
End
Else Begin
Commit Transaction
End
Return @.iCnt
End
"Daniel P." <DanielP@.discussions.microsoft.com> wrote in message
news:6FC61F2E-A1FD-43F1-917A-9A3BD6A7E782@.microsoft.com...
>I see an exclusive lock generated by:
> UPDATE Line #: 82 in up_updateShipmentRequestLine;1
> and a shared lock generated by
> SELECT Line #: 9 in
> up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
> You may want to look in the code in these two stored procedures (?). You
> may
> be accessing tables in reverse order.
> Probably the fix should go into the [up_updateShipmentRequestLine].
> If you have
> SELECT @.bExists = Field1 FROM Table1
> and then
> IF @.bExists = someVal
> UPDATE Table1 ...
> Instead do first:
> UPDATE Table1 SET Field1 = @.Val1
> IF @.@.ROWCOUNT == 0
> INSERT ...
> Ok. I'm making assumptions here since I do not know your code but the rule
> is that you want to get the highest lock since the beginning of the sproc
> and
> there are many ways you can do that. One is above.
> If you do not want to change the logic of the code, place a Locking Hints
> using
> WITH( ... )
> for example WITH(UPDLOCK).
> If you want more details then you need to post some code so I can point
> you
> exactly to code that generates the deadlock.
>
> "JI" wrote:
>|||Set the transaction isolation level as serializable or add the hint
WITH(TABLOCKX) and see if you still get the deadlock.
"JI" wrote:
> The update proc is one that I wrote a proc generator to create. It does a
> simple update...it does not access any other or the same table before the
> update. The interesting thing with the deadlock trace information is the
> index that is says deadlocks is a statistic. One created by SQL Server...
> I will post the update shipment request line proc below anyway.
> alter proc [dbo].[up_updateShipmentRequestLine]
> @.iError int OUTPUT
> ,@.guidShipmentRequestLineId uniqueidentifier
> ,@.guidShipmentRequestId uniqueidentifier
> ,@.iLineNumber int
> ,@.guidLotId uniqueidentifier
> ,@.sPurchaseOrderNumber char(50)
> ,@.sFullLotInd char(1)
> ,@.iMinimumCount int
> ,@.daDateNeeded datetime
> ,@.dcQuantity decimal(18,0)
> ,@.guidDestinationPlantId uniqueidentifier
> ,@.guidShipmentStatusId uniqueidentifier
> ,@.sShippingGroup char(3)
> ,@.sLineCreateUserName char(50)
> ,@.daLineCreateDate datetime
> ,@.sLineModifyUserName char(50)
> ,@.daLineModifyDate datetime
> ,@.daModifyDateTime datetime
> ,@.guidModifyUserId uniqueidentifier
> ,@.guidReferenceId uniqueidentifier
> ,@.useBitMap char(1) = 'F'
> as
> begin
> Set NoCount On
> Declare @.iCnt int
> ,@.bitMap varbinary(10)
> ,@.bitMapByte1 int
> ,@.bitMapByte2 int
> ,@.bitMapByte3 int
> ,@.bitMapByte4 int
> ,@.bitMapByte5 int
> ,@.bitMapByte6 int
> ,@.bitMapByte7 int
> ,@.bitMapByte8 int
> ,@.bitMapByte9 int
> ,@.bitMapByte10 int
> If @.useBitMap = 'T' Begin
> Select @.bitMapByte1 = Case When @.guidShipmentRequestLineId is null Then 0
> Else Power(2,0) End
> + Case When @.guidShipmentRequestId is null Then 0 Else Power(2,1) End
> + Case When @.iLineNumber is null Then 0 Else Power(2,2) End
> + Case When @.guidLotId is null Then 0 Else Power(2,3) End
> + Case When @.sPurchaseOrderNumber is null Then 0 Else Power(2,4) End
> + Case When @.sFullLotInd is null Then 0 Else Power(2,5) End
> + Case When @.iMinimumCount is null Then 0 Else Power(2,6) End
> + Case When @.daDateNeeded is null Then 0 Else Power(2,7) End
> Select @.bitMapByte2 = Case When @.dcQuantity is null Then 0 Else Power(2,0)
> End
> + Case When @.guidDestinationPlantId is null Then 0 Else Power(2,1) End
> + Case When @.guidShipmentStatusId is null Then 0 Else Power(2,2) End
> + Case When @.sShippingGroup is null Then 0 Else Power(2,3) End
> + Case When @.sLineCreateUserName is null Then 0 Else Power(2,4) End
> + Case When @.daLineCreateDate is null Then 0 Else Power(2,5) End
> + Case When @.sLineModifyUserName is null Then 0 Else Power(2,6) End
> + Case When @.daLineModifyDate is null Then 0 Else Power(2,7) End
> Select @.bitMapByte3 = Case When @.daModifyDateTime is null Then 0 Else
> Power(2,2) End
> + Case When @.guidModifyUserId is null Then 0 Else Power(2,3) End
> + Case When @.guidReferenceId is null Then 0 Else Power(2,6) End
> select @.bitmap = convert(binary(1),isNull(@.bitMapByte1,0)
)
> +convert(binary(1),isNull(@.bitMapByte2,0
))
> +convert(binary(1),isNull(@.bitMapByte3,0
))
> +convert(binary(1),isNull(@.bitMapByte4,0
))
> +convert(binary(1),isNull(@.bitMapByte5,0
))
> +convert(binary(1),isNull(@.bitMapByte6,0
))
> +convert(binary(1),isNull(@.bitMapByte7,0
))
> +convert(binary(1),isNull(@.bitMapByte8,0
))
> +convert(binary(1),isNull(@.bitMapByte9,0
))
> +convert(binary(1),isNull(@.bitMapByte10,
0))
> End
>
> begin transaction
> Update ShipmentRequestLine
> Set [ShipmentRequestLineId] = Case isNull(substring(@.bitmap,1,1),1) & 1 when
> 1 Then @.guidShipmentRequestLineId Else [ShipmentRequestLineId] End
> ,[ShipmentRequestId] = Case isNull(substring(@.bitmap,1,1),2) & 2 when 2 Then
> @.guidShipmentRequestId Else [ShipmentRequestId] End
> ,[LineNumber] = Case isNull(substring(@.bitmap,1,1),4) & 4 when 4 Then
> @.iLineNumber Else [LineNumber] End
> ,[LotId] = Case isNull(substring(@.bitmap,1,1),8) & 8 when 8 Then @.guidLotId
> Else [LotId] End
> ,[PurchaseOrderNumber] = Case isNull(substring(@.bitmap,1,1),16) & 16 when 16
> Then @.sPurchaseOrderNumber Else [PurchaseOrderNumber] End
> ,[FullLotInd] = Case isNull(substring(@.bitmap,1,1),32) & 32 when 32 Then
> @.sFullLotInd Else [FullLotInd] End
> ,[MinimumCount] = Case isNull(substring(@.bitmap,1,1),64) & 64 when 64 Then
> @.iMinimumCount Else [MinimumCount] End
> ,[DateNeeded] = Case isNull(substring(@.bitmap,1,1),128) & 128 when 128 Then
> @.daDateNeeded Else [DateNeeded] End
> ,[Quantity] = Case isNull(substring(@.bitmap,2,1),1) & 1 when 1 Then
> @.dcQuantity Else [Quantity] End
> ,[DestinationPlantId] = Case isNull(substring(@.bitmap,2,1),2) & 2 when 2
> Then @.guidDestinationPlantId Else [DestinationPlantId] End
> ,[ShipmentStatusId] = Case isNull(substring(@.bitmap,2,1),4) & 4 when 4 Then
> @.guidShipmentStatusId Else [ShipmentStatusId] End
> ,[ShippingGroup] = Case isNull(substring(@.bitmap,2,1),8) & 8 when 8 Then
> @.sShippingGroup Else [ShippingGroup] End
> ,[LineCreateUserName] = Case isNull(substring(@.bitmap,2,1),16) & 16 when 16
> Then @.sLineCreateUserName Else [LineCreateUserName] End
> ,[LineCreateDate] = Case isNull(substring(@.bitmap,2,1),32) & 32 when 32 Then
> @.daLineCreateDate Else [LineCreateDate] End
> ,[LineModifyUserName] = Case isNull(substring(@.bitmap,2,1),64) & 64 when 64
> Then @.sLineModifyUserName Else [LineModifyUserName] End
> ,[LineModifyDate] = Case isNull(substring(@.bitmap,2,1),128) & 128 when 128
> Then @.daLineModifyDate Else [LineModifyDate] End
> ,[ModifyDateTime] = isNull(@.daModifyDateTime,getDate())
> ,[ModifyUserId] = Case isNull(substring(@.bitmap,3,1),8) & 8 when 8 Then
> @.guidModifyUserId Else [ModifyUserId] End
> ,[ReferenceId] = Case isNull(substring(@.bitmap,3,1),64) & 64 when 64 Then
> @.guidReferenceId Else [ReferenceId] End
> where ShipmentRequestLineId = @.guidShipmentRequestLineId
> SELECT @.iError=@.@.ERROR, @.iCnt = @.@.rowCount
>
> If @.iError <> 0 begin
> Rollback Transaction
> End
> Else Begin
> Commit Transaction
> End
> Return @.iCnt
> End
> "Daniel P." <DanielP@.discussions.microsoft.com> wrote in message
> news:6FC61F2E-A1FD-43F1-917A-9A3BD6A7E782@.microsoft.com...
>
>
Sunday, March 25, 2012
Deadlock: Trace flag 1205, 1204
I want to log deadlocks. In query analyzer I ran dbcc
traceon(1205, 1204) on two different SQL Server 2000, SP3a
boxes. Stopped SQL Services and restarted on each box.
Created deadlocks on both boxes via the problem
application. Deadlocks are being written to sql server
logs on one box but not the other.
What is the difference and how can I tell if 1205 and 1204
trace flags are active?dbcc tracestatus(-1)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Mike Mullane" <mike.mullane@.hpinc.com> wrote in message
news:082101c38915$62471510$a301280a@.phx.gbl...
> I want to log deadlocks. In query analyzer I ran dbcc
> traceon(1205, 1204) on two different SQL Server 2000, SP3a
> boxes. Stopped SQL Services and restarted on each box.
> Created deadlocks on both boxes via the problem
> application. Deadlocks are being written to sql server
> logs on one box but not the other.
> What is the difference and how can I tell if 1205 and 1204
> trace flags are active?
>|||Hi Mike,
Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays the status of all
currently enabled trace flags by specifying a value of -1.
Please make sure that you problem application can make deadlock every time
when you execute it. Here is a deadlock example, please to perform the on
both SQL Server using Query Analyzer and check to see if the deadlock is
recorded in both SQL Server's log.
Create a simple deadlock in pubs in two Query Analyzer windows.
Window 1:
dbcc traceon(3605)
dbcc traceon(1204)
begin tran update authors set contract = contract
Window 2: begin tran update titles set ytd_sales = ytd_sales
Window 1: update titles set ytd_sales = ytd_sales
Window 2: update authors set contract = contract
It works on my side and I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello Michael,
Perfect advice. I am able to recreate locks using your
example and validate the they are being written to the
log. However, I'm was having issues getting DBCC
TRACESTATUS(-1) or DBCC TRACESTATUS(1204) to behave as
described. When I run it I get: "Trace option(s) not
enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator."
So I ran "DBCC TRACEON" and then aftter running that I
ran "DBCC TRACESTATUS(-1)" and I get "TraceFlag Status
-- --
1204 1" which is what I want. So, it seems that the
order needed is "DBCC TRACEON(1204)" then "DBCC TRACEON"
must be run before "DBCC TRACESTATUS(-1)" will list.
Thanks for your help. I've learned a bit.
Mike
>--Original Message--
>Hi Mike,
>Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays
the status of all
>currently enabled trace flags by specifying a value of -1.
>Please make sure that you problem application can make
deadlock every time
>when you execute it. Here is a deadlock example, please
to perform the on
>both SQL Server using Query Analyzer and check to see if
the deadlock is
>recorded in both SQL Server's log.
>Create a simple deadlock in pubs in two Query Analyzer
windows.
>Window 1:
>dbcc traceon(3605)
>dbcc traceon(1204)
>begin tran update authors set contract = contract
>Window 2: begin tran update titles set ytd_sales =ytd_sales
>Window 1: update titles set ytd_sales = ytd_sales
>Window 2: update authors set contract = contract
>It works on my side and I am standing by for your
response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>
traceon(1205, 1204) on two different SQL Server 2000, SP3a
boxes. Stopped SQL Services and restarted on each box.
Created deadlocks on both boxes via the problem
application. Deadlocks are being written to sql server
logs on one box but not the other.
What is the difference and how can I tell if 1205 and 1204
trace flags are active?dbcc tracestatus(-1)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Mike Mullane" <mike.mullane@.hpinc.com> wrote in message
news:082101c38915$62471510$a301280a@.phx.gbl...
> I want to log deadlocks. In query analyzer I ran dbcc
> traceon(1205, 1204) on two different SQL Server 2000, SP3a
> boxes. Stopped SQL Services and restarted on each box.
> Created deadlocks on both boxes via the problem
> application. Deadlocks are being written to sql server
> logs on one box but not the other.
> What is the difference and how can I tell if 1205 and 1204
> trace flags are active?
>|||Hi Mike,
Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays the status of all
currently enabled trace flags by specifying a value of -1.
Please make sure that you problem application can make deadlock every time
when you execute it. Here is a deadlock example, please to perform the on
both SQL Server using Query Analyzer and check to see if the deadlock is
recorded in both SQL Server's log.
Create a simple deadlock in pubs in two Query Analyzer windows.
Window 1:
dbcc traceon(3605)
dbcc traceon(1204)
begin tran update authors set contract = contract
Window 2: begin tran update titles set ytd_sales = ytd_sales
Window 1: update titles set ytd_sales = ytd_sales
Window 2: update authors set contract = contract
It works on my side and I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello Michael,
Perfect advice. I am able to recreate locks using your
example and validate the they are being written to the
log. However, I'm was having issues getting DBCC
TRACESTATUS(-1) or DBCC TRACESTATUS(1204) to behave as
described. When I run it I get: "Trace option(s) not
enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator."
So I ran "DBCC TRACEON" and then aftter running that I
ran "DBCC TRACESTATUS(-1)" and I get "TraceFlag Status
-- --
1204 1" which is what I want. So, it seems that the
order needed is "DBCC TRACEON(1204)" then "DBCC TRACEON"
must be run before "DBCC TRACESTATUS(-1)" will list.
Thanks for your help. I've learned a bit.
Mike
>--Original Message--
>Hi Mike,
>Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays
the status of all
>currently enabled trace flags by specifying a value of -1.
>Please make sure that you problem application can make
deadlock every time
>when you execute it. Here is a deadlock example, please
to perform the on
>both SQL Server using Query Analyzer and check to see if
the deadlock is
>recorded in both SQL Server's log.
>Create a simple deadlock in pubs in two Query Analyzer
windows.
>Window 1:
>dbcc traceon(3605)
>dbcc traceon(1204)
>begin tran update authors set contract = contract
>Window 2: begin tran update titles set ytd_sales =ytd_sales
>Window 1: update titles set ytd_sales = ytd_sales
>Window 2: update authors set contract = contract
>It works on my side and I am standing by for your
response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>
Deadlock trace interpretation
I have the following from a DBCC Trace:
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:1
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:216 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: Range-
Insert-Null SPID:215 ECID:0 Ec0x9017B5B0) Value:0x1dec9180 Cost0/1E4)
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:2
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:215 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec0x9014D5B0) Value:0x1e31b200 Cost0/1D4)
06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec0x9014D5B0) Value:0x1e31b200 Cost0/1D4)
06/08/2006 16:29:02,spid4,Unknown,
Since there are range locks, it looks as if the transaction isolation level
SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
insert into a user table based on a select statement against a temporary
table. Prior to this insert that is being deadlocked, the temporary table is
populated and then updated by joining on several user tables, including the
table being used in the deadlock insert. Should I be focusing on the update
to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
insert upon which the deadlock is occurring and putting a HOLDLOCK on the
select statement of the temporary table that populates the user table?
Message posted via http://www.droptable.comHi cbrichards
Without the code (and probably the DDL) it's impossible to say.
You are right that there must be SERIALIZABLE isolation in order to get the
range locks. And if you are already in SERIALIZABLE isolation, adding
HOLDLOCK would be redundant.
Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
statement, so there is no chance of another process also reading it and
holding onto the locks, until the first process is done. But again, without
any more details from you, there's little else to say.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:617d199ca3401@.uwe...
>I have the following from a DBCC Trace:
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:1
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:216 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode:
> Range-
> Insert-Null SPID:215 ECID:0 Ec0x9017B5B0) Value:0x1dec9180 Cost0/1E4)
>
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:2
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:215 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec0x9014D5B0) Value:0x1e31b200 Cost0/1D4)
>
> 06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec0x9014D5B0) Value:0x1e31b200 Cost0/1D4)
> 06/08/2006 16:29:02,spid4,Unknown,
>
> Since there are range locks, it looks as if the transaction isolation
> level
> SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
> insert into a user table based on a select statement against a temporary
> table. Prior to this insert that is being deadlocked, the temporary table
> is
> populated and then updated by joining on several user tables, including
> the
> table being used in the deadlock insert. Should I be focusing on the
> update
> to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
> select statement of the temporary table that populates the user table?
> --
> Message posted via http://www.droptable.com|||Hi Karen,
The stored proc code and DDL is included. Thanks for your help.
ALTER PROCEDURE [dbo].[MyStoredProc]
@.E_UID INT OUTPUT,
@.LKey Int,
@.RFID Int,
@.CID Int = Null,
@.CName varchar(255),
@.CCmID VARCHAR(30),
@.PID varchar(255),
@.CmS varchar(3),
@.VID varchar(38),
@.CmPtAm money
AS
DECLARE @.sVIDScrub varchar(38)
-- Determine the value to stuff in the VID field. First get rid of any non-
numerics
SET @.sVIDScrub = dbo.udf_Alpha(@.VID, 1)
CREATE TABLE #TRes (
LKey int,
RFID int,
CID int,
CName varchar(255),
CCmID varchar(30),
PID varchar(255),
CmS varchar(3),
VID varchar(38),
CmPtAm money,
CFID int,
CCode char(8),
PRIMARY KEY (LKey))
INSERT #TRes
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
CmPtAm,
CFID,
CCode )
SELECT @.LKey,
@.RFID,
@.CID,
@.CName,
@.CCmID,
@.PID,
@.CmS,
@.VID,
@.CmPtAm,
null,
null
/* Determine what value to stick into CFID
In order to match up car:
1) try for an exact CPID match; if none, then
2) see if the E CPID is contained in any car CPID or vice versa.
*/
-- Get car information based on CID
UPDATE TR
SET TR.CFID = Cast(Car.CUID AS varchar(10)),
TR.CCode = RTrim(Car.CCode),
TR.CName = Car.CName
FROM #TRes TR
JOIN dbo.RCm RCm
ON RCm.RFID = @.RFID
AND RCm.LKey = @.LKey
JOIN dbo.CarHist CBH
ON CBH.CmID = RCm.CmID
AND CBH.LKey = RCm.LKey
JOIN dbo.Cars Car
ON Car.CUID = CBH.CFID
AND Car.LKey = CBH.LKey
WHERE RCm.LKey = @.LKey
AND CBH.LKey = @.LKey
AND Car.LKey = @.LKey
--Insert the record
INSERT dbo.RCm
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
CmID,
VID,
CmPtAm,
CFID,
CCode )
SELECT LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
-- Only fill the VID column if the size fits the column data type
CASE
WHEN Len(@.sVIDScrub) < 10 THEN Cast(@.sVIDScrub AS int)
ELSE 0
END,
CmPtAm,
CFID,
CCode
FROM #TRes
-- Return the new id
SET @.@.E_UID = SCOPE_IDENTITY()
-- ****************************************
**********************************
**
CREATE TABLE [dbo].[Cars](
[LKey] [int] NOT NULL,
[CUID] [int] IDENTITY(1,1) NOT NULL,
[CCode] [varchar](8) NOT NULL,
[CName] [varchar](35) NOT NULL,
[CUser] [char](12) NOT NULL,
[CDate] [datetime] NOT NULL
CONSTRAINT [PK_Cars] PRIMARY KEY NONCLUSTERED
(
[CUID] ASC,
[LKey] ASC
) ON [PRIMARY],
CONSTRAINT [Unique_CCode] UNIQUE NONCLUSTERED
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_CUID] ON [dbo].[Cars]
(
[LKey] ASC,
[CUID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CCode] ON [dbo].[Cars]
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
-- ****************************************
*******************************
CREATE TABLE [dbo].[RCm](
[E_UID] [int] IDENTITY(1,1) NOT NULL,
[LKey] [int] NOT NULL,
[RFID] [int] NOT NULL,
[CID] [int] NULL,
[CName] [varchar](100) NULL,
[CCmID] [varchar](30) NULL,
[PID] [varchar](255) NULL,
[CmS] [varchar](3) NULL,
[CmID] [varchar](38) NOT NULL,
[VID] [int] NOT NULL DEFAULT ((-1)),
[CmPtAm] [money] NULL,
[CFID] [int] NULL,
[CCode] [char](8) NULL,
CONSTRAINT [PK_RCm] PRIMARY KEY NONCLUSTERED
(
[E_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_E_UID] ON [dbo].[RCm]
(
[Key] ASC,
[E_UID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_RCm_RFID] ON [dbo].[RCm]
(
[RFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CarHist](
[LKey] [int] NOT NULL,
[CarHist_UID] [int] IDENTITY(1,1) NOT NULL,
[CDetFID] [int] NOT NULL CONSTRAINT [DF__CarCharge] DEFAULT (1)
,
[CFID] [int] NOT NULL,
[CCode] [varchar](8) NOT NULL,
[VFID] [int] NULL,
[CmID] AS (convert(varchar(10),[VisitFID]) + ltrim([ClaimIDSuff
ix])),
CONSTRAINT [PK_CarHist] PRIMARY KEY NONCLUSTERED
(
[CarHist_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX [IDX1_CBH_CDetFID] ON [dbo].[CarHist]
(
[CDetFID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CBH_VFID] ON [dbo].[CarHist]
(
[VFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
Kalen Delaney wrote:[vbcol=seagreen]
>Hi cbrichards
>Without the code (and probably the DDL) it's impossible to say.
>You are right that there must be SERIALIZABLE isolation in order to get the
>range locks. And if you are already in SERIALIZABLE isolation, adding
>HOLDLOCK would be redundant.
>Sometimes you can avoid deadlock by requesting an X lock on data in a SELEC
T
>statement, so there is no chance of another process also reading it and
>holding onto the locks, until the first process is done. But again, without
>any more details from you, there's little else to say.
>[quoted text clipped - 51 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:1
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:216 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: Range-
Insert-Null SPID:215 ECID:0 Ec
0x9017B5B0) Value:0x1dec9180 Cost0/1E4)06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:2
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:215 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec
0x9014D5B0) Value:0x1e31b200 Cost0/1D4)06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec
0x9014D5B0) Value:0x1e31b200 Cost0/1D4)06/08/2006 16:29:02,spid4,Unknown,
Since there are range locks, it looks as if the transaction isolation level
SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
insert into a user table based on a select statement against a temporary
table. Prior to this insert that is being deadlocked, the temporary table is
populated and then updated by joining on several user tables, including the
table being used in the deadlock insert. Should I be focusing on the update
to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
insert upon which the deadlock is occurring and putting a HOLDLOCK on the
select statement of the temporary table that populates the user table?
Message posted via http://www.droptable.comHi cbrichards
Without the code (and probably the DDL) it's impossible to say.
You are right that there must be SERIALIZABLE isolation in order to get the
range locks. And if you are already in SERIALIZABLE isolation, adding
HOLDLOCK would be redundant.
Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
statement, so there is no chance of another process also reading it and
holding onto the locks, until the first process is done. But again, without
any more details from you, there's little else to say.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:617d199ca3401@.uwe...
>I have the following from a DBCC Trace:
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:1
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:216 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode:
> Range-
> Insert-Null SPID:215 ECID:0 Ec
0x9017B5B0) Value:0x1dec9180 Cost0/1E4)>
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:2
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:215 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec
0x9014D5B0) Value:0x1e31b200 Cost0/1D4)>
> 06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec
0x9014D5B0) Value:0x1e31b200 Cost0/1D4)> 06/08/2006 16:29:02,spid4,Unknown,
>
> Since there are range locks, it looks as if the transaction isolation
> level
> SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
> insert into a user table based on a select statement against a temporary
> table. Prior to this insert that is being deadlocked, the temporary table
> is
> populated and then updated by joining on several user tables, including
> the
> table being used in the deadlock insert. Should I be focusing on the
> update
> to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
> select statement of the temporary table that populates the user table?
> --
> Message posted via http://www.droptable.com|||Hi Karen,
The stored proc code and DDL is included. Thanks for your help.
ALTER PROCEDURE [dbo].[MyStoredProc]
@.E_UID INT OUTPUT,
@.LKey Int,
@.RFID Int,
@.CID Int = Null,
@.CName varchar(255),
@.CCmID VARCHAR(30),
@.PID varchar(255),
@.CmS varchar(3),
@.VID varchar(38),
@.CmPtAm money
AS
DECLARE @.sVIDScrub varchar(38)
-- Determine the value to stuff in the VID field. First get rid of any non-
numerics
SET @.sVIDScrub = dbo.udf_Alpha(@.VID, 1)
CREATE TABLE #TRes (
LKey int,
RFID int,
CID int,
CName varchar(255),
CCmID varchar(30),
PID varchar(255),
CmS varchar(3),
VID varchar(38),
CmPtAm money,
CFID int,
CCode char(8),
PRIMARY KEY (LKey))
INSERT #TRes
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
CmPtAm,
CFID,
CCode )
SELECT @.LKey,
@.RFID,
@.CID,
@.CName,
@.CCmID,
@.PID,
@.CmS,
@.VID,
@.CmPtAm,
null,
null
/* Determine what value to stick into CFID
In order to match up car:
1) try for an exact CPID match; if none, then
2) see if the E CPID is contained in any car CPID or vice versa.
*/
-- Get car information based on CID
UPDATE TR
SET TR.CFID = Cast(Car.CUID AS varchar(10)),
TR.CCode = RTrim(Car.CCode),
TR.CName = Car.CName
FROM #TRes TR
JOIN dbo.RCm RCm
ON RCm.RFID = @.RFID
AND RCm.LKey = @.LKey
JOIN dbo.CarHist CBH
ON CBH.CmID = RCm.CmID
AND CBH.LKey = RCm.LKey
JOIN dbo.Cars Car
ON Car.CUID = CBH.CFID
AND Car.LKey = CBH.LKey
WHERE RCm.LKey = @.LKey
AND CBH.LKey = @.LKey
AND Car.LKey = @.LKey
--Insert the record
INSERT dbo.RCm
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
CmID,
VID,
CmPtAm,
CFID,
CCode )
SELECT LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
-- Only fill the VID column if the size fits the column data type
CASE
WHEN Len(@.sVIDScrub) < 10 THEN Cast(@.sVIDScrub AS int)
ELSE 0
END,
CmPtAm,
CFID,
CCode
FROM #TRes
-- Return the new id
SET @.@.E_UID = SCOPE_IDENTITY()
-- ****************************************
**********************************
**
CREATE TABLE [dbo].[Cars](
[LKey] [int] NOT NULL,
[CUID] [int] IDENTITY(1,1) NOT NULL,
[CCode] [varchar](8) NOT NULL,
[CName] [varchar](35) NOT NULL,
[CUser] [char](12) NOT NULL,
[CDate] [datetime] NOT NULL
CONSTRAINT [PK_Cars] PRIMARY KEY NONCLUSTERED
(
[CUID] ASC,
[LKey] ASC
) ON [PRIMARY],
CONSTRAINT [Unique_CCode] UNIQUE NONCLUSTERED
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_CUID] ON [dbo].[Cars]
(
[LKey] ASC,
[CUID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CCode] ON [dbo].[Cars]
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
-- ****************************************
*******************************
CREATE TABLE [dbo].[RCm](
[E_UID] [int] IDENTITY(1,1) NOT NULL,
[LKey] [int] NOT NULL,
[RFID] [int] NOT NULL,
[CID] [int] NULL,
[CName] [varchar](100) NULL,
[CCmID] [varchar](30) NULL,
[PID] [varchar](255) NULL,
[CmS] [varchar](3) NULL,
[CmID] [varchar](38) NOT NULL,
[VID] [int] NOT NULL DEFAULT ((-1)),
[CmPtAm] [money] NULL,
[CFID] [int] NULL,
[CCode] [char](8) NULL,
CONSTRAINT [PK_RCm] PRIMARY KEY NONCLUSTERED
(
[E_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_E_UID] ON [dbo].[RCm]
(
[Key] ASC,
[E_UID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_RCm_RFID] ON [dbo].[RCm]
(
[RFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CarHist](
[LKey] [int] NOT NULL,
[CarHist_UID] [int] IDENTITY(1,1) NOT NULL,
[CDetFID] [int] NOT NULL CONSTRAINT [DF__CarCharge] DEFAULT (1)
,
[CFID] [int] NOT NULL,
[CCode] [varchar](8) NOT NULL,
[VFID] [int] NULL,
[CmID] AS (convert(varchar(10),[VisitFID]) + ltrim([ClaimIDSuff
ix])),
CONSTRAINT [PK_CarHist] PRIMARY KEY NONCLUSTERED
(
[CarHist_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX [IDX1_CBH_CDetFID] ON [dbo].[CarHist]
(
[CDetFID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CBH_VFID] ON [dbo].[CarHist]
(
[VFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
Kalen Delaney wrote:[vbcol=seagreen]
>Hi cbrichards
>Without the code (and probably the DDL) it's impossible to say.
>You are right that there must be SERIALIZABLE isolation in order to get the
>range locks. And if you are already in SERIALIZABLE isolation, adding
>HOLDLOCK would be redundant.
>Sometimes you can avoid deadlock by requesting an X lock on data in a SELEC
T
>statement, so there is no chance of another process also reading it and
>holding onto the locks, until the first process is done. But again, without
>any more details from you, there's little else to say.
>[quoted text clipped - 51 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1
Deadlock trace interpretation
I have the following from a DBCC Trace:
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:1
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:216 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:215 ECID:0 Ec:(0x9017B5B0) Value:0x1dec9180 Cost:(0/1E4)
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:2
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:215 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
06/08/2006 16:29:02,spid4,Unknown,
Since there are range locks, it looks as if the transaction isolation level
SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
insert into a user table based on a select statement against a temporary
table. Prior to this insert that is being deadlocked, the temporary table is
populated and then updated by joining on several user tables, including the
table being used in the deadlock insert. Should I be focusing on the update
to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
insert upon which the deadlock is occurring and putting a HOLDLOCK on the
select statement of the temporary table that populates the user table?
--
Message posted via http://www.sqlmonster.comHi cbrichards
Without the code (and probably the DDL) it's impossible to say.
You are right that there must be SERIALIZABLE isolation in order to get the
range locks. And if you are already in SERIALIZABLE isolation, adding
HOLDLOCK would be redundant.
Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
statement, so there is no chance of another process also reading it and
holding onto the locks, until the first process is done. But again, without
any more details from you, there's little else to say.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:617d199ca3401@.uwe...
>I have the following from a DBCC Trace:
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:1
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:216 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:215 ECID:0 Ec:(0x9017B5B0) Value:0x1dec9180 Cost:(0/1E4)
>
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:2
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:215 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
>
> 06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
> 06/08/2006 16:29:02,spid4,Unknown,
>
> Since there are range locks, it looks as if the transaction isolation
> level
> SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
> insert into a user table based on a select statement against a temporary
> table. Prior to this insert that is being deadlocked, the temporary table
> is
> populated and then updated by joining on several user tables, including
> the
> table being used in the deadlock insert. Should I be focusing on the
> update
> to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
> select statement of the temporary table that populates the user table?
> --
> Message posted via http://www.sqlmonster.com|||Hi Karen,
The stored proc code and DDL is included. Thanks for your help.
ALTER PROCEDURE [dbo].[MyStoredProc]
@.E_UID INT OUTPUT,
@.LKey Int,
@.RFID Int,
@.CID Int = Null,
@.CName varchar(255),
@.CCmID VARCHAR(30),
@.PID varchar(255),
@.CmS varchar(3),
@.VID varchar(38),
@.CmPtAm money
AS
DECLARE @.sVIDScrub varchar(38)
-- Determine the value to stuff in the VID field. First get rid of any non-
numerics
SET @.sVIDScrub = dbo.udf_Alpha(@.VID, 1)
CREATE TABLE #TRes (
LKey int,
RFID int,
CID int,
CName varchar(255),
CCmID varchar(30),
PID varchar(255),
CmS varchar(3),
VID varchar(38),
CmPtAm money,
CFID int,
CCode char(8),
PRIMARY KEY (LKey))
INSERT #TRes
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
CmPtAm,
CFID,
CCode )
SELECT @.LKey,
@.RFID,
@.CID,
@.CName,
@.CCmID,
@.PID,
@.CmS,
@.VID,
@.CmPtAm,
null,
null
/* Determine what value to stick into CFID
In order to match up car:
1) try for an exact CPID match; if none, then
2) see if the E CPID is contained in any car CPID or vice versa.
*/
-- Get car information based on CID
UPDATE TR
SET TR.CFID = Cast(Car.CUID AS varchar(10)),
TR.CCode = RTrim(Car.CCode),
TR.CName = Car.CName
FROM #TRes TR
JOIN dbo.RCm RCm
ON RCm.RFID = @.RFID
AND RCm.LKey = @.LKey
JOIN dbo.CarHist CBH
ON CBH.CmID = RCm.CmID
AND CBH.LKey = RCm.LKey
JOIN dbo.Cars Car
ON Car.CUID = CBH.CFID
AND Car.LKey = CBH.LKey
WHERE RCm.LKey = @.LKey
AND CBH.LKey = @.LKey
AND Car.LKey = @.LKey
--Insert the record
INSERT dbo.RCm
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
CmID,
VID,
CmPtAm,
CFID,
CCode )
SELECT LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
-- Only fill the VID column if the size fits the column data type
CASE
WHEN Len(@.sVIDScrub) < 10 THEN Cast(@.sVIDScrub AS int)
ELSE 0
END,
CmPtAm,
CFID,
CCode
FROM #TRes
-- Return the new id
SET @.@.E_UID = SCOPE_IDENTITY()
--****************************************************************************
CREATE TABLE [dbo].[Cars](
[LKey] [int] NOT NULL,
[CUID] [int] IDENTITY(1,1) NOT NULL,
[CCode] [varchar](8) NOT NULL,
[CName] [varchar](35) NOT NULL,
[CUser] [char](12) NOT NULL,
[CDate] [datetime] NOT NULL
CONSTRAINT [PK_Cars] PRIMARY KEY NONCLUSTERED
(
[CUID] ASC,
[LKey] ASC
) ON [PRIMARY],
CONSTRAINT [Unique_CCode] UNIQUE NONCLUSTERED
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_CUID] ON [dbo].[Cars]
(
[LKey] ASC,
[CUID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CCode] ON [dbo].[Cars]
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
--***********************************************************************
CREATE TABLE [dbo].[RCm](
[E_UID] [int] IDENTITY(1,1) NOT NULL,
[LKey] [int] NOT NULL,
[RFID] [int] NOT NULL,
[CID] [int] NULL,
[CName] [varchar](100) NULL,
[CCmID] [varchar](30) NULL,
[PID] [varchar](255) NULL,
[CmS] [varchar](3) NULL,
[CmID] [varchar](38) NOT NULL,
[VID] [int] NOT NULL DEFAULT ((-1)),
[CmPtAm] [money] NULL,
[CFID] [int] NULL,
[CCode] [char](8) NULL,
CONSTRAINT [PK_RCm] PRIMARY KEY NONCLUSTERED
(
[E_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_E_UID] ON [dbo].[RCm]
(
[Key] ASC,
[E_UID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_RCm_RFID] ON [dbo].[RCm]
(
[RFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CarHist](
[LKey] [int] NOT NULL,
[CarHist_UID] [int] IDENTITY(1,1) NOT NULL,
[CDetFID] [int] NOT NULL CONSTRAINT [DF__CarCharge] DEFAULT (1),
[CFID] [int] NOT NULL,
[CCode] [varchar](8) NOT NULL,
[VFID] [int] NULL,
[CmID] AS (convert(varchar(10),[VisitFID]) + ltrim([ClaimIDSuffix])),
CONSTRAINT [PK_CarHist] PRIMARY KEY NONCLUSTERED
(
[CarHist_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX [IDX1_CBH_CDetFID] ON [dbo].[CarHist]
(
[CDetFID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CBH_VFID] ON [dbo].[CarHist]
(
[VFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
Kalen Delaney wrote:
>Hi cbrichards
>Without the code (and probably the DDL) it's impossible to say.
>You are right that there must be SERIALIZABLE isolation in order to get the
>range locks. And if you are already in SERIALIZABLE isolation, adding
>HOLDLOCK would be redundant.
>Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
>statement, so there is no chance of another process also reading it and
>holding onto the locks, until the first process is done. But again, without
>any more details from you, there's little else to say.
>>I have the following from a DBCC Trace:
>[quoted text clipped - 51 lines]
>> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
>> select statement of the temporary table that populates the user table?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:1
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:216 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:215 ECID:0 Ec:(0x9017B5B0) Value:0x1dec9180 Cost:(0/1E4)
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:2
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:215 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
06/08/2006 16:29:02,spid4,Unknown,
Since there are range locks, it looks as if the transaction isolation level
SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
insert into a user table based on a select statement against a temporary
table. Prior to this insert that is being deadlocked, the temporary table is
populated and then updated by joining on several user tables, including the
table being used in the deadlock insert. Should I be focusing on the update
to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
insert upon which the deadlock is occurring and putting a HOLDLOCK on the
select statement of the temporary table that populates the user table?
--
Message posted via http://www.sqlmonster.comHi cbrichards
Without the code (and probably the DDL) it's impossible to say.
You are right that there must be SERIALIZABLE isolation in order to get the
range locks. And if you are already in SERIALIZABLE isolation, adding
HOLDLOCK would be redundant.
Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
statement, so there is no chance of another process also reading it and
holding onto the locks, until the first process is done. But again, without
any more details from you, there's little else to say.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:617d199ca3401@.uwe...
>I have the following from a DBCC Trace:
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:1
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:216 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:215 ECID:0 Ec:(0x9017B5B0) Value:0x1dec9180 Cost:(0/1E4)
>
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:2
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:215 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
>
> 06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
> 06/08/2006 16:29:02,spid4,Unknown,
>
> Since there are range locks, it looks as if the transaction isolation
> level
> SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
> insert into a user table based on a select statement against a temporary
> table. Prior to this insert that is being deadlocked, the temporary table
> is
> populated and then updated by joining on several user tables, including
> the
> table being used in the deadlock insert. Should I be focusing on the
> update
> to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
> select statement of the temporary table that populates the user table?
> --
> Message posted via http://www.sqlmonster.com|||Hi Karen,
The stored proc code and DDL is included. Thanks for your help.
ALTER PROCEDURE [dbo].[MyStoredProc]
@.E_UID INT OUTPUT,
@.LKey Int,
@.RFID Int,
@.CID Int = Null,
@.CName varchar(255),
@.CCmID VARCHAR(30),
@.PID varchar(255),
@.CmS varchar(3),
@.VID varchar(38),
@.CmPtAm money
AS
DECLARE @.sVIDScrub varchar(38)
-- Determine the value to stuff in the VID field. First get rid of any non-
numerics
SET @.sVIDScrub = dbo.udf_Alpha(@.VID, 1)
CREATE TABLE #TRes (
LKey int,
RFID int,
CID int,
CName varchar(255),
CCmID varchar(30),
PID varchar(255),
CmS varchar(3),
VID varchar(38),
CmPtAm money,
CFID int,
CCode char(8),
PRIMARY KEY (LKey))
INSERT #TRes
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
CmPtAm,
CFID,
CCode )
SELECT @.LKey,
@.RFID,
@.CID,
@.CName,
@.CCmID,
@.PID,
@.CmS,
@.VID,
@.CmPtAm,
null,
null
/* Determine what value to stick into CFID
In order to match up car:
1) try for an exact CPID match; if none, then
2) see if the E CPID is contained in any car CPID or vice versa.
*/
-- Get car information based on CID
UPDATE TR
SET TR.CFID = Cast(Car.CUID AS varchar(10)),
TR.CCode = RTrim(Car.CCode),
TR.CName = Car.CName
FROM #TRes TR
JOIN dbo.RCm RCm
ON RCm.RFID = @.RFID
AND RCm.LKey = @.LKey
JOIN dbo.CarHist CBH
ON CBH.CmID = RCm.CmID
AND CBH.LKey = RCm.LKey
JOIN dbo.Cars Car
ON Car.CUID = CBH.CFID
AND Car.LKey = CBH.LKey
WHERE RCm.LKey = @.LKey
AND CBH.LKey = @.LKey
AND Car.LKey = @.LKey
--Insert the record
INSERT dbo.RCm
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
CmID,
VID,
CmPtAm,
CFID,
CCode )
SELECT LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
-- Only fill the VID column if the size fits the column data type
CASE
WHEN Len(@.sVIDScrub) < 10 THEN Cast(@.sVIDScrub AS int)
ELSE 0
END,
CmPtAm,
CFID,
CCode
FROM #TRes
-- Return the new id
SET @.@.E_UID = SCOPE_IDENTITY()
--****************************************************************************
CREATE TABLE [dbo].[Cars](
[LKey] [int] NOT NULL,
[CUID] [int] IDENTITY(1,1) NOT NULL,
[CCode] [varchar](8) NOT NULL,
[CName] [varchar](35) NOT NULL,
[CUser] [char](12) NOT NULL,
[CDate] [datetime] NOT NULL
CONSTRAINT [PK_Cars] PRIMARY KEY NONCLUSTERED
(
[CUID] ASC,
[LKey] ASC
) ON [PRIMARY],
CONSTRAINT [Unique_CCode] UNIQUE NONCLUSTERED
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_CUID] ON [dbo].[Cars]
(
[LKey] ASC,
[CUID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CCode] ON [dbo].[Cars]
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
--***********************************************************************
CREATE TABLE [dbo].[RCm](
[E_UID] [int] IDENTITY(1,1) NOT NULL,
[LKey] [int] NOT NULL,
[RFID] [int] NOT NULL,
[CID] [int] NULL,
[CName] [varchar](100) NULL,
[CCmID] [varchar](30) NULL,
[PID] [varchar](255) NULL,
[CmS] [varchar](3) NULL,
[CmID] [varchar](38) NOT NULL,
[VID] [int] NOT NULL DEFAULT ((-1)),
[CmPtAm] [money] NULL,
[CFID] [int] NULL,
[CCode] [char](8) NULL,
CONSTRAINT [PK_RCm] PRIMARY KEY NONCLUSTERED
(
[E_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_E_UID] ON [dbo].[RCm]
(
[Key] ASC,
[E_UID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_RCm_RFID] ON [dbo].[RCm]
(
[RFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CarHist](
[LKey] [int] NOT NULL,
[CarHist_UID] [int] IDENTITY(1,1) NOT NULL,
[CDetFID] [int] NOT NULL CONSTRAINT [DF__CarCharge] DEFAULT (1),
[CFID] [int] NOT NULL,
[CCode] [varchar](8) NOT NULL,
[VFID] [int] NULL,
[CmID] AS (convert(varchar(10),[VisitFID]) + ltrim([ClaimIDSuffix])),
CONSTRAINT [PK_CarHist] PRIMARY KEY NONCLUSTERED
(
[CarHist_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX [IDX1_CBH_CDetFID] ON [dbo].[CarHist]
(
[CDetFID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CBH_VFID] ON [dbo].[CarHist]
(
[VFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
Kalen Delaney wrote:
>Hi cbrichards
>Without the code (and probably the DDL) it's impossible to say.
>You are right that there must be SERIALIZABLE isolation in order to get the
>range locks. And if you are already in SERIALIZABLE isolation, adding
>HOLDLOCK would be redundant.
>Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
>statement, so there is no chance of another process also reading it and
>holding onto the locks, until the first process is done. But again, without
>any more details from you, there's little else to say.
>>I have the following from a DBCC Trace:
>[quoted text clipped - 51 lines]
>> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
>> select statement of the temporary table that populates the user table?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1
Deadlock Trace - can you interpret?
Hi all.
I'm confused by the deadlock trace posted at the end of this post. If anyone
knows where a more complete listing of the information in trace flag 1204
output exists, please let me know - BOL doesn't appear to tell all in this
case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
Does the trace mean that two SPIDs have exclusive locks on the same KEY in a
clustered index, and both SPIDS need a shared lock on the same KEY,
resulting in the deadlock?
Or, is it that two SPIDS have exclusive locks on different KEY values in the
same clustered index, and each SPID needs shared access to the other's KEY,
resulting in the deadlock?
The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
is this a specific key value in the clustered index? Also, what do the
following mean?
"Life:02000000",
"ResType:LockOwner",
"Stype:'OR' ",
"Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
"CleanCnt: 1"
Any help would be tremendously appreciated. I've been working heavily with
SQL Server and Sybase for 8 years now, and have traced deadlocks before, but
this one is leaving me feeling kinda naked.
Cheers,
Steve.
---begin deadlock
trace---
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
Grant List 0::
Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
ECID:0
SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
Value:0x4679a220 Cost0/B0)
Node:2
KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
ECID:0
SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
Value:0x5b7462e0 Cost0/41AF8)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
Value:0x4679a220 Cost0/B0)
---end deadlock
trace---
Hello Steve,
Currently I am looking for somebody who is familar with it. We will reply here with more information as soon as possible.
If you have any more concerns on it, please feel free to post here. Thanks very much for your patience.
Best regards,
Yanhong Huang
Microsoft Community Support
Get Secure! C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Steve
My comments inline:
"Steve Cockayne" <steve@.nospam.com> wrote in message
news:uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> I'm confused by the deadlock trace posted at the end of this post. If
anyone
> knows where a more complete listing of the information in trace flag 1204
> output exists, please let me know - BOL doesn't appear to tell all in this
> case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
> Does the trace mean that two SPIDs have exclusive locks on the same KEY in
a
> clustered index, and both SPIDS need a shared lock on the same KEY,
> resulting in the deadlock?
No, the next description's the right one.
> Or, is it that two SPIDS have exclusive locks on different KEY values in
the
> same clustered index, and each SPID needs shared access to the other's
KEY,
> resulting in the deadlock?
This is absolutely correct. The two nodes in the deadlock graph contain
eXclusive locks on different clustered index keys and are also trying to
acquire shared
locks on each others already locked keys. This is an example of the fairly
common
cyclical deadlock scenario.
Another way you can read your deadlock graph is:
GRANTED LOCKS
Node #1: SPID 65 is granted an X lock on KEY: 9:1109578991:1 (e3018fb914ef -
hash of key value)
Node #2: SPID 74 is granted an X lock on KEY: 9:1109578991:1 (290271463fe1-
hash of key value)
REQUESTED LOCKS
Node #1: SPID 74 requests an S lock on KEY: 9:1109578991:1 (e3018fb914ef -
hash of key value)
Node #2: SPID 65 requests an S lock on KEY: 9:1109578991:1 (290271463fe1-
hash of key value)
SPID 74 was chosen as the deadlock victim in your case, but keep in mind
that the choice of deadlock victim isn't just who "closed" the deadlock
embrace; the "cost" of work already performed is also taken into account.
Note that spid 74's cost was Cost0/B0) & 65's was Cost0/41AF8), although
I'm not sure how to really interpret those cost indicators.
> The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
> documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
e3018fb914ef represents a hashed value of the key. I've been told before
that this is a one way hash, so you can't reverse out the actual value. Howe
ver, this probably doesn't mean much to you as the actual key that the
deadlock occurs on probably won't help you solve the deadlock problem.
> is this a specific key value in the clustered index? Also, what do the
> following mean?
> "Life:02000000",
> "ResType:LockOwner",
> "Stype:'OR' ",
> "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
> "CleanCnt: 1"
I'm not sure what the other bits mean, so I'm hoping you get a more detailed
answer from the MS guys as well in this thread.
However, you have enough information to solve the problem anyhow. Your
analysis of the deadlock cause is correct. The thing to do now is solve it,
which is often easiest done by either :
(a) re-coding the spSSA_DataLog_AddRecord (if that's an option)
(b) implementing re-try logic in the client app
(c) using hints to acquire higher level locks instead of shared locks
(d) volunteering lower priority processes to be deadlock victims
(e) lowering transaction isolation levels
Without seeing your stored proc, it's hard to even make a suggestion as to
what the best approach is, but given you've been doing this a while, perhaps
you're right with the resolution part of this anyway.
I'm certainly looking forward to some more information on the details you've
requested as well.
HTH
Regards,
Greg Linwood
SQL Server MVP
> Any help would be tremendously appreciated. I've been working heavily with
> SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
> this one is leaving me feeling kinda naked.
> Cheers,
> Steve.
> ---begin deadlock
> trace---
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 0::
> Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
> ECID:0
> SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> Value:0x4679a220 Cost0/B0)
> Node:2
> KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
> ECID:0
> SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
> Value:0x5b7462e0 Cost0/41AF8)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> Value:0x4679a220 Cost0/B0)
> ---end deadlock
> trace---
>
|||Greg Linwood has done a good job to explain the deadlock output. For more
info. check the following articles:
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8i93.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_9m43.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_3hdf.asp
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_5xrn.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8um1.asp
| From: "Steve Cockayne" <steve@.nospam.com>
| Subject: Deadlock Trace - can you interpret?
| Date: Wed, 30 Jun 2004 11:14:08 -0700
| Lines: 68
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
|
| Hi all.
|
| I'm confused by the deadlock trace posted at the end of this post. If
anyone
| knows where a more complete listing of the information in trace flag 1204
| output exists, please let me know - BOL doesn't appear to tell all in this
| case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
|
| Does the trace mean that two SPIDs have exclusive locks on the same KEY
in a
| clustered index, and both SPIDS need a shared lock on the same KEY,
| resulting in the deadlock?
|
| Or, is it that two SPIDS have exclusive locks on different KEY values in
the
| same clustered index, and each SPID needs shared access to the other's
KEY,
| resulting in the deadlock?
|
| The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
| documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
| is this a specific key value in the clustered index? Also, what do the
| following mean?
| "Life:02000000",
| "ResType:LockOwner",
| "Stype:'OR' ",
| "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
| "CleanCnt: 1"
|
| Any help would be tremendously appreciated. I've been working heavily with
| SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
| this one is leaving me feeling kinda naked.
|
| Cheers,
| Steve.
|
| ---begin deadlock
| trace---
| Deadlock encountered ... Printing deadlock information
|
| Wait-for graph
|
| Node:1
| KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 0::
| Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
| ECID:0
| SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| Value:0x4679a220 Cost0/B0)
|
| Node:2
| KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 1::
| Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
| ECID:0
| SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
| Value:0x5b7462e0 Cost0/41AF8)
|
| Victim Resource Owner:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| Value:0x4679a220 Cost0/B0)
| ---end deadlock
| trace---
|
|
|
|||Hi Steve,
Did you get my Emails? I am not sure if your Email address
'steve@.nospam.com' is correct or not. So, would plese send me an Email with
your contact information at a-virenp@.microsoft.com?
Thanks.
| From: "Steve Cockayne" <steve@.nospam.com>
| Subject: Deadlock Trace - can you interpret?
| Date: Wed, 30 Jun 2004 11:14:08 -0700
| Lines: 68
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
|
| Hi all.
|
| I'm confused by the deadlock trace posted at the end of this post. If
anyone
| knows where a more complete listing of the information in trace flag 1204
| output exists, please let me know - BOL doesn't appear to tell all in this
| case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
|
| Does the trace mean that two SPIDs have exclusive locks on the same KEY
in a
| clustered index, and both SPIDS need a shared lock on the same KEY,
| resulting in the deadlock?
|
| Or, is it that two SPIDS have exclusive locks on different KEY values in
the
| same clustered index, and each SPID needs shared access to the other's
KEY,
| resulting in the deadlock?
|
| The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
| documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
| is this a specific key value in the clustered index? Also, what do the
| following mean?
| "Life:02000000",
| "ResType:LockOwner",
| "Stype:'OR' ",
| "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
| "CleanCnt: 1"
|
| Any help would be tremendously appreciated. I've been working heavily with
| SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
| this one is leaving me feeling kinda naked.
|
| Cheers,
| Steve.
|
| ---begin deadlock
| trace---
| Deadlock encountered ... Printing deadlock information
|
| Wait-for graph
|
| Node:1
| KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 0::
| Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
| ECID:0
| SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| Value:0x4679a220 Cost0/B0)
|
| Node:2
| KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 1::
| Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
| ECID:0
| SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
| Value:0x5b7462e0 Cost0/41AF8)
|
| Victim Resource Owner:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| Value:0x4679a220 Cost0/B0)
| ---end deadlock
| trace---
|
|
|
|||Hi Viren.
If you've got more to add to this post, why not do it in the thread so we
can all benefit?
Regards,
Greg Linwood
SQL Server MVP
"Viren Parikh" <a-virenp@.microsoft.com> wrote in message
news:MGPDBEEYEHA.2752@.cpmsftngxa06.phx.gbl...
> Hi Steve,
> Did you get my Emails? I am not sure if your Email address
> 'steve@.nospam.com' is correct or not. So, would plese send me an Email
with
> your contact information at a-virenp@.microsoft.com?
> Thanks.
> --
> | From: "Steve Cockayne" <steve@.nospam.com>
> | Subject: Deadlock Trace - can you interpret?
> | Date: Wed, 30 Jun 2004 11:14:08 -0700
> | Lines: 68
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> | Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> |
> | Hi all.
> |
> | I'm confused by the deadlock trace posted at the end of this post. If
> anyone
> | knows where a more complete listing of the information in trace flag
1204
> | output exists, please let me know - BOL doesn't appear to tell all in
this
> | case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
> |
> | Does the trace mean that two SPIDs have exclusive locks on the same KEY
> in a
> | clustered index, and both SPIDS need a shared lock on the same KEY,
> | resulting in the deadlock?
> |
> | Or, is it that two SPIDS have exclusive locks on different KEY values in
> the
> | same clustered index, and each SPID needs shared access to the other's
> KEY,
> | resulting in the deadlock?
> |
> | The resource: 9:1109578991:1 is a clustered index. I haven't yet found
any
> | documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)"
means -
> | is this a specific key value in the clustered index? Also, what do the
> | following mean?
> | "Life:02000000",
> | "ResType:LockOwner",
> | "Stype:'OR' ",
> | "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
> | "CleanCnt: 1"
> |
> | Any help would be tremendously appreciated. I've been working heavily
with
> | SQL Server and Sybase for 8 years now, and have traced deadlocks before,
> but
> | this one is leaving me feeling kinda naked.
> |
> | Cheers,
> | Steve.
> |
> | ---begin deadlock
> | trace---
> | Deadlock encountered ... Printing deadlock information
> |
> | Wait-for graph
> |
> | Node:1
> | KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
> | Grant List 0::
> | Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
> | ECID:0
> | SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
> | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> | Requested By:
> | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> | Value:0x4679a220 Cost0/B0)
> |
> | Node:2
> | KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
> | Grant List 1::
> | Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
> | ECID:0
> | SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
> | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> | Requested By:
> | ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
> | Value:0x5b7462e0 Cost0/41AF8)
> |
> | Victim Resource Owner:
> | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> | Value:0x4679a220 Cost0/B0)
> | ---end deadlock
> | trace---
> |
> |
> |
>
|||Hi all.
Special thanks to Greg, Viren, and Yan-Hong for posting back - I'll post
once I've come up with a solution.
Greg - I'd rather fix the problem with your option (1) - rewrite the sproc -
as I'm pretty bummed it can deadlock itself. In other times I have written
client logic in my low-level database classes to re-try on deadlocks, so
that is an option.
I'll also look at taking exclusive locks instead of shared, but I think the
problem might not go away - follow my thinking here:
I'm using a C# SqlTransaction object to wrap multiple calls to my sproc,
which contains multiple base-sql statements (like, say 10). The table that
produces the deadlock has two indexes. The first is clustered on non-unique
fields to give range-search capability, but also contains the unique key.
The second index is nonclustered, solely on the unique key. The
SqlTransaction essentially ensures that a dataset is submitted through my
sproc, as an all-or-nothing transaction - the C# code loops through the
dataset, calling the sproc once for each data record.
There are multiple threads that could be doing this at the same time. I'm
thinking that the deadlock occurs because the series of calls to the sproc
is unordered - thus one call to the sproc from a SPID would lock a certain
pair of keys through two separate sproc calls, whilst another SPID could
require the keys in a different order, due to calling the sprocs in a
different order.
I'll certainly review the locking in this sproc in-depth, to be sure of what
locks actually are acquired in all cases, and how they are escalated, but I
wonder if sorting the my dataset in C# - before looping over the data to
call the sproc - would buy me deadlock-safety without changing the sproc.
I'll also review why I have the unique key from the nonclustered index as a
field in the clustered index. I think I might have put it in there as a
covering field, but I'm thinking this is not required in a clustered index,
as the leaf level of a clustered index is the data pages, so the dreaded
bookmark-lookup should not be required, right?
I'll certainly post once I know exactly why I'm deadlocking, and what
solution I choose, and when I get the e-mails from Viren, I'll review them
and post salient details to the newsgroup.
Cheers,
Steve.
"Steve Cockayne" <steve@.nospam.com> wrote in message
news:uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> I'm confused by the deadlock trace posted at the end of this post. If
anyone
> knows where a more complete listing of the information in trace flag 1204
> output exists, please let me know - BOL doesn't appear to tell all in this
> case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
> Does the trace mean that two SPIDs have exclusive locks on the same KEY in
a
> clustered index, and both SPIDS need a shared lock on the same KEY,
> resulting in the deadlock?
> Or, is it that two SPIDS have exclusive locks on different KEY values in
the
> same clustered index, and each SPID needs shared access to the other's
KEY,
> resulting in the deadlock?
> The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
> documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
> is this a specific key value in the clustered index? Also, what do the
> following mean?
> "Life:02000000",
> "ResType:LockOwner",
> "Stype:'OR' ",
> "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
> "CleanCnt: 1"
> Any help would be tremendously appreciated. I've been working heavily with
> SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
> this one is leaving me feeling kinda naked.
> Cheers,
> Steve.
> ---begin deadlock
> trace---
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 0::
> Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
> ECID:0
> SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> Value:0x4679a220 Cost0/B0)
> Node:2
> KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
> ECID:0
> SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
> Value:0x5b7462e0 Cost0/41AF8)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
> Value:0x4679a220 Cost0/B0)
> ---end deadlock
> trace---
>
|||Hi Steve
Your idea of sorting the set of rows operated on by the SqlTransaction is a
good idea and may help some.
The information you provided on your indexes is perhaps useful. I'd suggest
that you try out changing the clustered index from its current composite
column to the unique index column. Then create a non-clustered index for the
range selects, perhaps covering the columns to avoid bookmark lookups.
Generally speaking, it's a good idea to keep clustered indexes as narrow as
possible because all non-clustered indexes use the key values from clustered
indexes as lookup keys (where a clustered index exists). There are also some
minor processing overheads added for clustered indexes on non-unique columns
(uniquefiers). Bookmark lookups are a trade off, but certainly not always
dreaded! There are some big benefits, especially in the area of index
maintenance but I won't digress further.
Altering the indexes shouldn't be a hard thing to do. This may be a silver
bullet & you can always put them back to how they are now if you don't get
any benefits fro the change. I'd suggest it's worth a try. If you've got the
option to re-write the stored proc, that might be the best solution. One
suggestion I'd make there is that if you've got concurrent processes that
can run the stored proc, you might consider designing it so that each
instance operates on a range of rows so that deadlocking incidents resulting
from the updates / inserts / deletes not only against rows but index keys
(ranges) are reduced by virtue of the fact that each concurrent process is
operating on it's own "range" of rows rather than interleaving with other
concurrent processes through a shared, sorted rowset.
Regards,
Greg Linwood
SQL Server MVP
"Steve Cockayne" <steve@.nospam.com> wrote in message
news:uvuKlTrYEHA.3972@.TK2MSFTNGP12.phx.gbl...
> Hi all.
> Special thanks to Greg, Viren, and Yan-Hong for posting back - I'll post
> once I've come up with a solution.
> Greg - I'd rather fix the problem with your option (1) - rewrite the
sproc -
> as I'm pretty bummed it can deadlock itself. In other times I have written
> client logic in my low-level database classes to re-try on deadlocks, so
> that is an option.
> I'll also look at taking exclusive locks instead of shared, but I think
the
> problem might not go away - follow my thinking here:
> I'm using a C# SqlTransaction object to wrap multiple calls to my sproc,
> which contains multiple base-sql statements (like, say 10). The table that
> produces the deadlock has two indexes. The first is clustered on
non-unique
> fields to give range-search capability, but also contains the unique key.
> The second index is nonclustered, solely on the unique key. The
> SqlTransaction essentially ensures that a dataset is submitted through my
> sproc, as an all-or-nothing transaction - the C# code loops through the
> dataset, calling the sproc once for each data record.
> There are multiple threads that could be doing this at the same time. I'm
> thinking that the deadlock occurs because the series of calls to the sproc
> is unordered - thus one call to the sproc from a SPID would lock a certain
> pair of keys through two separate sproc calls, whilst another SPID could
> require the keys in a different order, due to calling the sprocs in a
> different order.
> I'll certainly review the locking in this sproc in-depth, to be sure of
what
> locks actually are acquired in all cases, and how they are escalated, but
I
> wonder if sorting the my dataset in C# - before looping over the data to
> call the sproc - would buy me deadlock-safety without changing the sproc.
> I'll also review why I have the unique key from the nonclustered index as
a
> field in the clustered index. I think I might have put it in there as a
> covering field, but I'm thinking this is not required in a clustered
index,[vbcol=seagreen]
> as the leaf level of a clustered index is the data pages, so the dreaded
> bookmark-lookup should not be required, right?
> I'll certainly post once I know exactly why I'm deadlocking, and what
> solution I choose, and when I get the e-mails from Viren, I'll review them
> and post salient details to the newsgroup.
> Cheers,
> Steve.
>
> "Steve Cockayne" <steve@.nospam.com> wrote in message
> news:uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl...
> anyone
1204[vbcol=seagreen]
this[vbcol=seagreen]
in[vbcol=seagreen]
> a
> the
> KEY,
any[vbcol=seagreen]
means -[vbcol=seagreen]
with
> but
>
|||Hi Steve,
You should have got my other Emails but if not then here is what I have to
say on this subject.
I think Greg has done an excellent job to explain the deadlock output but
here is what I say:
What is a waits-for graph?
A waits-for graph is a directed graph that draws out who (which spid+ecid)
is waiting for which (key,row,table etc) resource. Deadlock detection
algorithm tries to find a loop in the directed graph to detect a deadlock.
And finally as the deadlock is detected, deadlock monitoring thread kills
the spid with the smallest cost.
What is different... 7.0 <>8.0?
Different from SQL Server 7.0 you have to think about resources as being
nodes instead of the session threads.
In addition, you may get some threads that does not participate in the
deadlock loop in the output.
One way to read the output:
To draw the graph make each Locked Resource (RID, KEY etc) is a node and
mark their owners. Then draw the arrow from the requestor to the owner for
each line in the output and you should end up with the loop. One of the
participants of this loop will get rolled back according to their costs.
See under BOL\Index\"deadlocks, troubleshooting" for the additional info or
check the following links:
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_7wxf.asp
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_58z1.asp
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_5xrn.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8i93.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_9m43.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_3hdf.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8um1.asp
Let me know if you have any more questions or want us to trouble shoot your
case.
Thanks.
Viren Parikh
| Newsgroups: microsoft.public.sqlserver.server
| From: a-virenp@.microsoft.com (Viren Parikh)
| Organization: Microsoft
| Date: Fri, 02 Jul 2004 14:29:33 GMT
| Subject: RE: Deadlock Trace - can you interpret?
| X-Tomcat-NG: microsoft.public.sqlserver.server
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
|
| Hi Steve,
|
| Did you get my Emails? I am not sure if your Email address
| 'steve@.nospam.com' is correct or not. So, would plese send me an Email
with
| your contact information at a-virenp@.microsoft.com?
|
| Thanks.
| --
| | From: "Steve Cockayne" <steve@.nospam.com>
| | Subject: Deadlock Trace - can you interpret?
| | Date: Wed, 30 Jun 2004 11:14:08 -0700
| | Lines: 68
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| | Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
| | Newsgroups: microsoft.public.sqlserver.server
| | NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
| | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| |
| | Hi all.
| |
| | I'm confused by the deadlock trace posted at the end of this post. If
| anyone
| | knows where a more complete listing of the information in trace flag
1204
| | output exists, please let me know - BOL doesn't appear to tell all in
this
| | case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
| |
| | Does the trace mean that two SPIDs have exclusive locks on the same KEY
| in a
| | clustered index, and both SPIDS need a shared lock on the same KEY,
| | resulting in the deadlock?
| |
| | Or, is it that two SPIDS have exclusive locks on different KEY values
in
| the
| | same clustered index, and each SPID needs shared access to the other's
| KEY,
| | resulting in the deadlock?
| |
| | The resource: 9:1109578991:1 is a clustered index. I haven't yet found
any
| | documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)"
means -
| | is this a specific key value in the clustered index? Also, what do the
| | following mean?
| | "Life:02000000",
| | "ResType:LockOwner",
| | "Stype:'OR' ",
| | "Ec0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",
| | "CleanCnt: 1"
| |
| | Any help would be tremendously appreciated. I've been working heavily
with
| | SQL Server and Sybase for 8 years now, and have traced deadlocks
before,
| but
| | this one is leaving me feeling kinda naked.
| |
| | Cheers,
| | Steve.
| |
| | ---begin deadlock
| | trace---
| | Deadlock encountered ... Printing deadlock information
| |
| | Wait-for graph
| |
| | Node:1
| | KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
| | Grant List 0::
| | Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
| | ECID:0
| | SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
| | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| | Requested By:
| | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| | Value:0x4679a220 Cost0/B0)
| |
| | Node:2
| | KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
| | Grant List 1::
| | Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
| | ECID:0
| | SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
| | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| | Requested By:
| | ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec0x5B65D580)
| | Value:0x5b7462e0 Cost0/41AF8)
| |
| | Victim Resource Owner:
| | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec0x5B5BB580)
| | Value:0x4679a220 Cost0/B0)
| | ---end deadlock
| | trace---
| |
| |
| |
|
|||Hi Steve,
Still one more thing here. The email of you is a no spam email address and so we can't contact you through email. However, you can reach
us by removing online from our email address here.
Viren has posted a reply here. If you have any more concerns, please feel free to reply his post or send us email. We will post back with
more information in the newsgroup.
Thanks very much.
Best regards,
Yanhong Huang
Microsoft Community Support
Get Secure! C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
I'm confused by the deadlock trace posted at the end of this post. If anyone
knows where a more complete listing of the information in trace flag 1204
output exists, please let me know - BOL doesn't appear to tell all in this
case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
Does the trace mean that two SPIDs have exclusive locks on the same KEY in a
clustered index, and both SPIDS need a shared lock on the same KEY,
resulting in the deadlock?
Or, is it that two SPIDS have exclusive locks on different KEY values in the
same clustered index, and each SPID needs shared access to the other's KEY,
resulting in the deadlock?
The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
is this a specific key value in the clustered index? Also, what do the
following mean?
"Life:02000000",
"ResType:LockOwner",
"Stype:'OR' ",
"Ec
0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)","CleanCnt: 1"
Any help would be tremendously appreciated. I've been working heavily with
SQL Server and Sybase for 8 years now, and have traced deadlocks before, but
this one is leaving me feeling kinda naked.
Cheers,
Steve.
---begin deadlock
trace---
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
Grant List 0::
Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
ECID:0
SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)Value:0x4679a220 Cost
0/B0)Node:2
KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
Grant List 1::
Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
ECID:0
SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec
0x5B65D580)Value:0x5b7462e0 Cost
0/41AF8)Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)Value:0x4679a220 Cost
0/B0)---end deadlock
trace---
Hello Steve,
Currently I am looking for somebody who is familar with it. We will reply here with more information as soon as possible.
If you have any more concerns on it, please feel free to post here. Thanks very much for your patience.
Best regards,
Yanhong Huang
Microsoft Community Support
Get Secure! C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Steve
My comments inline:
"Steve Cockayne" <steve@.nospam.com> wrote in message
news:uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> I'm confused by the deadlock trace posted at the end of this post. If
anyone
> knows where a more complete listing of the information in trace flag 1204
> output exists, please let me know - BOL doesn't appear to tell all in this
> case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
> Does the trace mean that two SPIDs have exclusive locks on the same KEY in
a
> clustered index, and both SPIDS need a shared lock on the same KEY,
> resulting in the deadlock?
No, the next description's the right one.
> Or, is it that two SPIDS have exclusive locks on different KEY values in
the
> same clustered index, and each SPID needs shared access to the other's
KEY,
> resulting in the deadlock?
This is absolutely correct. The two nodes in the deadlock graph contain
eXclusive locks on different clustered index keys and are also trying to
acquire shared
locks on each others already locked keys. This is an example of the fairly
common
cyclical deadlock scenario.
Another way you can read your deadlock graph is:
GRANTED LOCKS
Node #1: SPID 65 is granted an X lock on KEY: 9:1109578991:1 (e3018fb914ef -
hash of key value)
Node #2: SPID 74 is granted an X lock on KEY: 9:1109578991:1 (290271463fe1-
hash of key value)
REQUESTED LOCKS
Node #1: SPID 74 requests an S lock on KEY: 9:1109578991:1 (e3018fb914ef -
hash of key value)
Node #2: SPID 65 requests an S lock on KEY: 9:1109578991:1 (290271463fe1-
hash of key value)
SPID 74 was chosen as the deadlock victim in your case, but keep in mind
that the choice of deadlock victim isn't just who "closed" the deadlock
embrace; the "cost" of work already performed is also taken into account.
Note that spid 74's cost was Cost
0/B0) & 65's was Cost0/41AF8), althoughI'm not sure how to really interpret those cost indicators.
> The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
> documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
e3018fb914ef represents a hashed value of the key. I've been told before
that this is a one way hash, so you can't reverse out the actual value. Howe
ver, this probably doesn't mean much to you as the actual key that the
deadlock occurs on probably won't help you solve the deadlock problem.
> is this a specific key value in the clustered index? Also, what do the
> following mean?
> "Life:02000000",
> "ResType:LockOwner",
> "Stype:'OR' ",
> "Ec
0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",> "CleanCnt: 1"
I'm not sure what the other bits mean, so I'm hoping you get a more detailed
answer from the MS guys as well in this thread.
However, you have enough information to solve the problem anyhow. Your
analysis of the deadlock cause is correct. The thing to do now is solve it,
which is often easiest done by either :
(a) re-coding the spSSA_DataLog_AddRecord (if that's an option)
(b) implementing re-try logic in the client app
(c) using hints to acquire higher level locks instead of shared locks
(d) volunteering lower priority processes to be deadlock victims
(e) lowering transaction isolation levels
Without seeing your stored proc, it's hard to even make a suggestion as to
what the best approach is, but given you've been doing this a while, perhaps
you're right with the resolution part of this anyway.
I'm certainly looking forward to some more information on the details you've
requested as well.
HTH
Regards,
Greg Linwood
SQL Server MVP
> Any help would be tremendously appreciated. I've been working heavily with
> SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
> this one is leaving me feeling kinda naked.
> Cheers,
> Steve.
> ---begin deadlock
> trace---
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 0::
> Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
> ECID:0
> SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)> Value:0x4679a220 Cost
0/B0)> Node:2
> KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
> ECID:0
> SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec
0x5B65D580)> Value:0x5b7462e0 Cost
0/41AF8)> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)> Value:0x4679a220 Cost
0/B0)> ---end deadlock
> trace---
>
|||Greg Linwood has done a good job to explain the deadlock output. For more
info. check the following articles:
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8i93.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_9m43.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_3hdf.asp
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_5xrn.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8um1.asp
| From: "Steve Cockayne" <steve@.nospam.com>
| Subject: Deadlock Trace - can you interpret?
| Date: Wed, 30 Jun 2004 11:14:08 -0700
| Lines: 68
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
|
| Hi all.
|
| I'm confused by the deadlock trace posted at the end of this post. If
anyone
| knows where a more complete listing of the information in trace flag 1204
| output exists, please let me know - BOL doesn't appear to tell all in this
| case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
|
| Does the trace mean that two SPIDs have exclusive locks on the same KEY
in a
| clustered index, and both SPIDS need a shared lock on the same KEY,
| resulting in the deadlock?
|
| Or, is it that two SPIDS have exclusive locks on different KEY values in
the
| same clustered index, and each SPID needs shared access to the other's
KEY,
| resulting in the deadlock?
|
| The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
| documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
| is this a specific key value in the clustered index? Also, what do the
| following mean?
| "Life:02000000",
| "ResType:LockOwner",
| "Stype:'OR' ",
| "Ec
0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",| "CleanCnt: 1"
|
| Any help would be tremendously appreciated. I've been working heavily with
| SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
| this one is leaving me feeling kinda naked.
|
| Cheers,
| Steve.
|
| ---begin deadlock
| trace---
| Deadlock encountered ... Printing deadlock information
|
| Wait-for graph
|
| Node:1
| KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 0::
| Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
| ECID:0
| SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)| Value:0x4679a220 Cost
0/B0)|
| Node:2
| KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 1::
| Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
| ECID:0
| SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec
0x5B65D580)| Value:0x5b7462e0 Cost
0/41AF8)|
| Victim Resource Owner:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)| Value:0x4679a220 Cost
0/B0)| ---end deadlock
| trace---
|
|
|
|||Hi Steve,
Did you get my Emails? I am not sure if your Email address
'steve@.nospam.com' is correct or not. So, would plese send me an Email with
your contact information at a-virenp@.microsoft.com?
Thanks.
| From: "Steve Cockayne" <steve@.nospam.com>
| Subject: Deadlock Trace - can you interpret?
| Date: Wed, 30 Jun 2004 11:14:08 -0700
| Lines: 68
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
|
| Hi all.
|
| I'm confused by the deadlock trace posted at the end of this post. If
anyone
| knows where a more complete listing of the information in trace flag 1204
| output exists, please let me know - BOL doesn't appear to tell all in this
| case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
|
| Does the trace mean that two SPIDs have exclusive locks on the same KEY
in a
| clustered index, and both SPIDS need a shared lock on the same KEY,
| resulting in the deadlock?
|
| Or, is it that two SPIDS have exclusive locks on different KEY values in
the
| same clustered index, and each SPID needs shared access to the other's
KEY,
| resulting in the deadlock?
|
| The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
| documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
| is this a specific key value in the clustered index? Also, what do the
| following mean?
| "Life:02000000",
| "ResType:LockOwner",
| "Stype:'OR' ",
| "Ec
0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",| "CleanCnt: 1"
|
| Any help would be tremendously appreciated. I've been working heavily with
| SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
| this one is leaving me feeling kinda naked.
|
| Cheers,
| Steve.
|
| ---begin deadlock
| trace---
| Deadlock encountered ... Printing deadlock information
|
| Wait-for graph
|
| Node:1
| KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 0::
| Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
| ECID:0
| SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)| Value:0x4679a220 Cost
0/B0)|
| Node:2
| KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
| Grant List 1::
| Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
| ECID:0
| SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
| Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| Requested By:
| ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec
0x5B65D580)| Value:0x5b7462e0 Cost
0/41AF8)|
| Victim Resource Owner:
| ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)| Value:0x4679a220 Cost
0/B0)| ---end deadlock
| trace---
|
|
|
|||Hi Viren.
If you've got more to add to this post, why not do it in the thread so we
can all benefit?
Regards,
Greg Linwood
SQL Server MVP
"Viren Parikh" <a-virenp@.microsoft.com> wrote in message
news:MGPDBEEYEHA.2752@.cpmsftngxa06.phx.gbl...
> Hi Steve,
> Did you get my Emails? I am not sure if your Email address
> 'steve@.nospam.com' is correct or not. So, would plese send me an Email
with
> your contact information at a-virenp@.microsoft.com?
> Thanks.
> --
> | From: "Steve Cockayne" <steve@.nospam.com>
> | Subject: Deadlock Trace - can you interpret?
> | Date: Wed, 30 Jun 2004 11:14:08 -0700
> | Lines: 68
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> | Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> |
> | Hi all.
> |
> | I'm confused by the deadlock trace posted at the end of this post. If
> anyone
> | knows where a more complete listing of the information in trace flag
1204
> | output exists, please let me know - BOL doesn't appear to tell all in
this
> | case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
> |
> | Does the trace mean that two SPIDs have exclusive locks on the same KEY
> in a
> | clustered index, and both SPIDS need a shared lock on the same KEY,
> | resulting in the deadlock?
> |
> | Or, is it that two SPIDS have exclusive locks on different KEY values in
> the
> | same clustered index, and each SPID needs shared access to the other's
> KEY,
> | resulting in the deadlock?
> |
> | The resource: 9:1109578991:1 is a clustered index. I haven't yet found
any
> | documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)"
means -
> | is this a specific key value in the clustered index? Also, what do the
> | following mean?
> | "Life:02000000",
> | "ResType:LockOwner",
> | "Stype:'OR' ",
> | "Ec
0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",> | "CleanCnt: 1"
> |
> | Any help would be tremendously appreciated. I've been working heavily
with
> | SQL Server and Sybase for 8 years now, and have traced deadlocks before,
> but
> | this one is leaving me feeling kinda naked.
> |
> | Cheers,
> | Steve.
> |
> | ---begin deadlock
> | trace---
> | Deadlock encountered ... Printing deadlock information
> |
> | Wait-for graph
> |
> | Node:1
> | KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
> | Grant List 0::
> | Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
> | ECID:0
> | SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
> | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> | Requested By:
> | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)> | Value:0x4679a220 Cost
0/B0)> |
> | Node:2
> | KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
> | Grant List 1::
> | Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
> | ECID:0
> | SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
> | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> | Requested By:
> | ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec
0x5B65D580)> | Value:0x5b7462e0 Cost
0/41AF8)> |
> | Victim Resource Owner:
> | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)> | Value:0x4679a220 Cost
0/B0)> | ---end deadlock
> | trace---
> |
> |
> |
>
|||Hi all.
Special thanks to Greg, Viren, and Yan-Hong for posting back - I'll post
once I've come up with a solution.
Greg - I'd rather fix the problem with your option (1) - rewrite the sproc -
as I'm pretty bummed it can deadlock itself. In other times I have written
client logic in my low-level database classes to re-try on deadlocks, so
that is an option.
I'll also look at taking exclusive locks instead of shared, but I think the
problem might not go away - follow my thinking here:
I'm using a C# SqlTransaction object to wrap multiple calls to my sproc,
which contains multiple base-sql statements (like, say 10). The table that
produces the deadlock has two indexes. The first is clustered on non-unique
fields to give range-search capability, but also contains the unique key.
The second index is nonclustered, solely on the unique key. The
SqlTransaction essentially ensures that a dataset is submitted through my
sproc, as an all-or-nothing transaction - the C# code loops through the
dataset, calling the sproc once for each data record.
There are multiple threads that could be doing this at the same time. I'm
thinking that the deadlock occurs because the series of calls to the sproc
is unordered - thus one call to the sproc from a SPID would lock a certain
pair of keys through two separate sproc calls, whilst another SPID could
require the keys in a different order, due to calling the sprocs in a
different order.
I'll certainly review the locking in this sproc in-depth, to be sure of what
locks actually are acquired in all cases, and how they are escalated, but I
wonder if sorting the my dataset in C# - before looping over the data to
call the sproc - would buy me deadlock-safety without changing the sproc.
I'll also review why I have the unique key from the nonclustered index as a
field in the clustered index. I think I might have put it in there as a
covering field, but I'm thinking this is not required in a clustered index,
as the leaf level of a clustered index is the data pages, so the dreaded
bookmark-lookup should not be required, right?
I'll certainly post once I know exactly why I'm deadlocking, and what
solution I choose, and when I get the e-mails from Viren, I'll review them
and post salient details to the newsgroup.
Cheers,
Steve.
"Steve Cockayne" <steve@.nospam.com> wrote in message
news:uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> I'm confused by the deadlock trace posted at the end of this post. If
anyone
> knows where a more complete listing of the information in trace flag 1204
> output exists, please let me know - BOL doesn't appear to tell all in this
> case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
> Does the trace mean that two SPIDs have exclusive locks on the same KEY in
a
> clustered index, and both SPIDS need a shared lock on the same KEY,
> resulting in the deadlock?
> Or, is it that two SPIDS have exclusive locks on different KEY values in
the
> same clustered index, and each SPID needs shared access to the other's
KEY,
> resulting in the deadlock?
> The resource: 9:1109578991:1 is a clustered index. I haven't yet found any
> documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)" means -
> is this a specific key value in the clustered index? Also, what do the
> following mean?
> "Life:02000000",
> "ResType:LockOwner",
> "Stype:'OR' ",
> "Ec
0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",> "CleanCnt: 1"
> Any help would be tremendously appreciated. I've been working heavily with
> SQL Server and Sybase for 8 years now, and have traced deadlocks before,
but
> this one is leaving me feeling kinda naked.
> Cheers,
> Steve.
> ---begin deadlock
> trace---
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 0::
> Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
> ECID:0
> SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)> Value:0x4679a220 Cost
0/B0)> Node:2
> KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
> ECID:0
> SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
> Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec
0x5B65D580)> Value:0x5b7462e0 Cost
0/41AF8)> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)> Value:0x4679a220 Cost
0/B0)> ---end deadlock
> trace---
>
|||Hi Steve
Your idea of sorting the set of rows operated on by the SqlTransaction is a
good idea and may help some.
The information you provided on your indexes is perhaps useful. I'd suggest
that you try out changing the clustered index from its current composite
column to the unique index column. Then create a non-clustered index for the
range selects, perhaps covering the columns to avoid bookmark lookups.
Generally speaking, it's a good idea to keep clustered indexes as narrow as
possible because all non-clustered indexes use the key values from clustered
indexes as lookup keys (where a clustered index exists). There are also some
minor processing overheads added for clustered indexes on non-unique columns
(uniquefiers). Bookmark lookups are a trade off, but certainly not always
dreaded! There are some big benefits, especially in the area of index
maintenance but I won't digress further.
Altering the indexes shouldn't be a hard thing to do. This may be a silver
bullet & you can always put them back to how they are now if you don't get
any benefits fro the change. I'd suggest it's worth a try. If you've got the
option to re-write the stored proc, that might be the best solution. One
suggestion I'd make there is that if you've got concurrent processes that
can run the stored proc, you might consider designing it so that each
instance operates on a range of rows so that deadlocking incidents resulting
from the updates / inserts / deletes not only against rows but index keys
(ranges) are reduced by virtue of the fact that each concurrent process is
operating on it's own "range" of rows rather than interleaving with other
concurrent processes through a shared, sorted rowset.
Regards,
Greg Linwood
SQL Server MVP
"Steve Cockayne" <steve@.nospam.com> wrote in message
news:uvuKlTrYEHA.3972@.TK2MSFTNGP12.phx.gbl...
> Hi all.
> Special thanks to Greg, Viren, and Yan-Hong for posting back - I'll post
> once I've come up with a solution.
> Greg - I'd rather fix the problem with your option (1) - rewrite the
sproc -
> as I'm pretty bummed it can deadlock itself. In other times I have written
> client logic in my low-level database classes to re-try on deadlocks, so
> that is an option.
> I'll also look at taking exclusive locks instead of shared, but I think
the
> problem might not go away - follow my thinking here:
> I'm using a C# SqlTransaction object to wrap multiple calls to my sproc,
> which contains multiple base-sql statements (like, say 10). The table that
> produces the deadlock has two indexes. The first is clustered on
non-unique
> fields to give range-search capability, but also contains the unique key.
> The second index is nonclustered, solely on the unique key. The
> SqlTransaction essentially ensures that a dataset is submitted through my
> sproc, as an all-or-nothing transaction - the C# code loops through the
> dataset, calling the sproc once for each data record.
> There are multiple threads that could be doing this at the same time. I'm
> thinking that the deadlock occurs because the series of calls to the sproc
> is unordered - thus one call to the sproc from a SPID would lock a certain
> pair of keys through two separate sproc calls, whilst another SPID could
> require the keys in a different order, due to calling the sprocs in a
> different order.
> I'll certainly review the locking in this sproc in-depth, to be sure of
what
> locks actually are acquired in all cases, and how they are escalated, but
I
> wonder if sorting the my dataset in C# - before looping over the data to
> call the sproc - would buy me deadlock-safety without changing the sproc.
> I'll also review why I have the unique key from the nonclustered index as
a
> field in the clustered index. I think I might have put it in there as a
> covering field, but I'm thinking this is not required in a clustered
index,[vbcol=seagreen]
> as the leaf level of a clustered index is the data pages, so the dreaded
> bookmark-lookup should not be required, right?
> I'll certainly post once I know exactly why I'm deadlocking, and what
> solution I choose, and when I get the e-mails from Viren, I'll review them
> and post salient details to the newsgroup.
> Cheers,
> Steve.
>
> "Steve Cockayne" <steve@.nospam.com> wrote in message
> news:uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl...
> anyone
1204[vbcol=seagreen]
this[vbcol=seagreen]
in[vbcol=seagreen]
> a
> the
> KEY,
any[vbcol=seagreen]
means -[vbcol=seagreen]
with
> but
>
|||Hi Steve,
You should have got my other Emails but if not then here is what I have to
say on this subject.
I think Greg has done an excellent job to explain the deadlock output but
here is what I say:
What is a waits-for graph?
A waits-for graph is a directed graph that draws out who (which spid+ecid)
is waiting for which (key,row,table etc) resource. Deadlock detection
algorithm tries to find a loop in the directed graph to detect a deadlock.
And finally as the deadlock is detected, deadlock monitoring thread kills
the spid with the smallest cost.
What is different... 7.0 <>8.0?
Different from SQL Server 7.0 you have to think about resources as being
nodes instead of the session threads.
In addition, you may get some threads that does not participate in the
deadlock loop in the output.
One way to read the output:
To draw the graph make each Locked Resource (RID, KEY etc) is a node and
mark their owners. Then draw the arrow from the requestor to the owner for
each line in the output and you should end up with the loop. One of the
participants of this loop will get rolled back according to their costs.
See under BOL\Index\"deadlocks, troubleshooting" for the additional info or
check the following links:
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_7wxf.asp
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_58z1.asp
http://msdn.microsoft.com/library/de...us/trblsql/tr_
servdatabse_5xrn.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8i93.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_9m43.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_3hdf.asp
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_con_7a_8um1.asp
Let me know if you have any more questions or want us to trouble shoot your
case.
Thanks.
Viren Parikh
| Newsgroups: microsoft.public.sqlserver.server
| From: a-virenp@.microsoft.com (Viren Parikh)
| Organization: Microsoft
| Date: Fri, 02 Jul 2004 14:29:33 GMT
| Subject: RE: Deadlock Trace - can you interpret?
| X-Tomcat-NG: microsoft.public.sqlserver.server
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
|
| Hi Steve,
|
| Did you get my Emails? I am not sure if your Email address
| 'steve@.nospam.com' is correct or not. So, would plese send me an Email
with
| your contact information at a-virenp@.microsoft.com?
|
| Thanks.
| --
| | From: "Steve Cockayne" <steve@.nospam.com>
| | Subject: Deadlock Trace - can you interpret?
| | Date: Wed, 30 Jun 2004 11:14:08 -0700
| | Lines: 68
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| | Message-ID: <uO4QK4sXEHA.2544@.TK2MSFTNGP10.phx.gbl>
| | Newsgroups: microsoft.public.sqlserver.server
| | NNTP-Posting-Host: h139-142-65-19.gtcust.grouptelecom.net 139.142.65.19
| | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349408
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| |
| | Hi all.
| |
| | I'm confused by the deadlock trace posted at the end of this post. If
| anyone
| | knows where a more complete listing of the information in trace flag
1204
| | output exists, please let me know - BOL doesn't appear to tell all in
this
| | case (maybe my BOL is out of date). My "SELECT @.@.version" is: 8.00.859
| |
| | Does the trace mean that two SPIDs have exclusive locks on the same KEY
| in a
| | clustered index, and both SPIDS need a shared lock on the same KEY,
| | resulting in the deadlock?
| |
| | Or, is it that two SPIDS have exclusive locks on different KEY values
in
| the
| | same clustered index, and each SPID needs shared access to the other's
| KEY,
| | resulting in the deadlock?
| |
| | The resource: 9:1109578991:1 is a clustered index. I haven't yet found
any
| | documentation telling me what "KEY: 9:1109578991:1 (e3018fb914ef)"
means -
| | is this a specific key value in the clustered index? Also, what do the
| | following mean?
| | "Life:02000000",
| | "ResType:LockOwner",
| | "Stype:'OR' ",
| | "Ec
0x5B65D580) Value:0x5b7462e0 Cost0/41AF8)",| | "CleanCnt: 1"
| |
| | Any help would be tremendously appreciated. I've been working heavily
with
| | SQL Server and Sybase for 8 years now, and have traced deadlocks
before,
| but
| | this one is leaving me feeling kinda naked.
| |
| | Cheers,
| | Steve.
| |
| | ---begin deadlock
| | trace---
| | Deadlock encountered ... Printing deadlock information
| |
| | Wait-for graph
| |
| | Node:1
| | KEY: 9:1109578991:1 (e3018fb914ef) CleanCnt:1 Mode: X Flags: 0x0
| | Grant List 0::
| | Owner:0x42ba3960 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:65
| | ECID:0
| | SPID: 65 ECID: 0 Statement Type: SELECT Line #: 35
| | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| | Requested By:
| | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)| | Value:0x4679a220 Cost
0/B0)| |
| | Node:2
| | KEY: 9:1109578991:1 (290271463fe1) CleanCnt:1 Mode: X Flags: 0x0
| | Grant List 1::
| | Owner:0x5a6659a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:74
| | ECID:0
| | SPID: 74 ECID: 0 Statement Type: SELECT Line #: 35
| | Input Buf: RPC Event: dbo.spSSA_DataLog_AddRecord;1
| | Requested By:
| | ResType:LockOwner Stype:'OR' Mode: S SPID:65 ECID:0 Ec
0x5B65D580)| | Value:0x5b7462e0 Cost
0/41AF8)| |
| | Victim Resource Owner:
| | ResType:LockOwner Stype:'OR' Mode: S SPID:74 ECID:0 Ec
0x5B5BB580)| | Value:0x4679a220 Cost
0/B0)| | ---end deadlock
| | trace---
| |
| |
| |
|
|||Hi Steve,
Still one more thing here. The email of you is a no spam email address and so we can't contact you through email. However, you can reach
us by removing online from our email address here.
Viren has posted a reply here. If you have any more concerns, please feel free to reply his post or send us email. We will post back with
more information in the newsgroup.
Thanks very much.
Best regards,
Yanhong Huang
Microsoft Community Support
Get Secure! C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)