deAttach database: below

Showing posts with label below. Show all posts

Thursday, March 29, 2012

Deadlocks Information.

When a deadlock occurs, the system dumps a bunch of diagnostics
information into its error log and ends up looking like the excerpt
below. Does an application exist anywhere that parses this stuff and
tells me exactly what tables/indexes/etc... were involved in the deadlock?
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
KEY: 13:1171587312:1 (25009a75c3be) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x45bb0480 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:58
ECID:0
SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 596
Input Buf: RPC Event: pr_Sproc1;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x576E3580)
Value:0x6847aac0 Cost:(0/0)
Node:2
TAB: 13:1203587426 [] CleanCnt:1 Mode: S Flags: 0x0
Grant List 3::
Owner:0x684935a0 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:53
ECID:0
SPID: 53 ECID: 0 Statement Type: SELECT INTO Line #: 579
Input Buf: RPC Event: pr_Sproc2;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec:(0x78181580)
Value:0x57f04c80 Cost:(0/1ED4)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x576E3580)
Value:0x6847aac0 Cost:(0/0)There is no application to do the parsing, afaik.
Look up "Troubleshooting Deadlocks" in the Books Online. It goods a pretty
good description of how to interpret the information
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Frank Rizzo" <none@.none.com> wrote in message
news:ObGR$%23g6FHA.2092@.TK2MSFTNGP12.phx.gbl...
> When a deadlock occurs, the system dumps a bunch of diagnostics
> information into its error log and ends up looking like the excerpt below.
> Does an application exist anywhere that parses this stuff and tells me
> exactly what tables/indexes/etc... were involved in the deadlock?
>
> Deadlock encountered ... Printing deadlock information
>
>
> Wait-for graph
>
>
> Node:1
> KEY: 13:1171587312:1 (25009a75c3be) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 3::
> Owner:0x45bb0480 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:58
> ECID:0
> SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 596
> Input Buf: RPC Event: pr_Sproc1;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x576E3580)
> Value:0x6847aac0 Cost:(0/0)
>
> Node:2
> TAB: 13:1203587426 [] CleanCnt:1 Mode: S Flags: 0x0
> Grant List 3::
> Owner:0x684935a0 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:53
> ECID:0
> SPID: 53 ECID: 0 Statement Type: SELECT INTO Line #: 579
> Input Buf: RPC Event: pr_Sproc2;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec:(0x78181580)
> Value:0x57f04c80 Cost:(0/1ED4)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x576E3580)
> Value:0x6847aac0 Cost:(0/0)
>|||Frank:
If you use profiler, you can get a graphical representation of the
objects/sessions involved in the deadlock. Also, the new TF-1222 provides
much richer information on the deadlock.
Thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:ObGR$%23g6FHA.2092@.TK2MSFTNGP12.phx.gbl...
> When a deadlock occurs, the system dumps a bunch of diagnostics
> information into its error log and ends up looking like the excerpt below.
> Does an application exist anywhere that parses this stuff and tells me
> exactly what tables/indexes/etc... were involved in the deadlock?
>
> Deadlock encountered ... Printing deadlock information
>
>
> Wait-for graph
>
>
> Node:1
> KEY: 13:1171587312:1 (25009a75c3be) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 3::
> Owner:0x45bb0480 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:58
> ECID:0
> SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 596
> Input Buf: RPC Event: pr_Sproc1;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x576E3580)
> Value:0x6847aac0 Cost:(0/0)
>
> Node:2
> TAB: 13:1203587426 [] CleanCnt:1 Mode: S Flags: 0x0
> Grant List 3::
> Owner:0x684935a0 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:53
> ECID:0
> SPID: 53 ECID: 0 Statement Type: SELECT INTO Line #: 579
> Input Buf: RPC Event: pr_Sproc2;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec:(0x78181580)
> Value:0x57f04c80 Cost:(0/1ED4)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x576E3580)
> Value:0x6847aac0 Cost:(0/0)|||Sorry, I should have pointed out that my previous mail is only applicable
for SQL2005.
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in message
news:e5BGA%23h6FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Frank:
> If you use profiler, you can get a graphical representation of the
> objects/sessions involved in the deadlock. Also, the new TF-1222 provides
> much richer information on the deadlock.
> Thanks
> --
> Sunil Agarwal (MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Frank Rizzo" <none@.none.com> wrote in message
> news:ObGR$%23g6FHA.2092@.TK2MSFTNGP12.phx.gbl...
>> When a deadlock occurs, the system dumps a bunch of diagnostics
>> information into its error log and ends up looking like the excerpt
>> below. Does an application exist anywhere that parses this stuff and
>> tells me exactly what tables/indexes/etc... were involved in the
>> deadlock?
>>
>> Deadlock encountered ... Printing deadlock information
>>
>>
>> Wait-for graph
>>
>>
>> Node:1
>> KEY: 13:1171587312:1 (25009a75c3be) CleanCnt:1 Mode: X Flags: 0x0
>> Grant List 3::
>> Owner:0x45bb0480 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:58
>> ECID:0
>> SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 596
>> Input Buf: RPC Event: pr_Sproc1;1
>> Requested By:
>> ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x576E3580)
>> Value:0x6847aac0 Cost:(0/0)
>>
>> Node:2
>> TAB: 13:1203587426 [] CleanCnt:1 Mode: S Flags: 0x0
>> Grant List 3::
>> Owner:0x684935a0 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:53
>> ECID:0
>> SPID: 53 ECID: 0 Statement Type: SELECT INTO Line #: 579
>> Input Buf: RPC Event: pr_Sproc2;1
>> Requested By:
>> ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec:(0x78181580)
>> Value:0x57f04c80 Cost:(0/1ED4)
>> Victim Resource Owner:
>> ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x576E3580)
>> Value:0x6847aac0 Cost:(0/0)
>|||Kalen Delaney wrote:
> There is no application to do the parsing, afaik.
> Look up "Troubleshooting Deadlocks" in the Books Online. It goods a pretty
> good description of how to interpret the information
I understand how to interpret them, however, it is a pain. I thought by
now someone had enough of it and wrote something up. Sounds like a
weekend project.

Deadlocks increasing

I have setup a SQL alert that email's me if deadlocks occur. I recently
started getting flooded with alot of emails(see below) with deadlocks. The
current value does not drop back down, it remains at 4 until I restart the
SQL server. This has happened once in the past before, about 3 weeks ago.
We fixed it by restarting the server. I have DBCC OPENTRAN on all databases
and there are no active transaction open. Perfmon shows that I have 0
deadlocks but still I get these emails. Turning trace 1204 also shows me 0
deadlocks occurring. Can someone help me.
Tim
DATE/TIME: 10/13/2004 10:24:02 AM
DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec'
(instance '_Total') of object 'MSSQL$INST01:Locks' is now above the
threshold of 3.00 (the current value is 4.00).
COMMENT: (None)
JOB RUN: (None)
More information to supply:
"Tim" <Tim@.NOSpam.com> wrote in message
news:#0H6MnTsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> I have setup a SQL alert that email's me if deadlocks occur. I recently
> started getting flooded with alot of emails(see below) with deadlocks.
The
> current value does not drop back down, it remains at 4 until I restart the
> SQL server. This has happened once in the past before, about 3 weeks ago.
> We fixed it by restarting the server. I have DBCC OPENTRAN on all
databases
> and there are no active transaction open. Perfmon shows that I have 0
> deadlocks but still I get these emails. Turning trace 1204 also shows me
0
> deadlocks occurring. Can someone help me.
> Tim
> ----
--
> --
> DATE/TIME: 10/13/2004 10:24:02 AM
> DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec'
> (instance '_Total') of object 'MSSQL$INST01:Locks' is now above the
> threshold of 3.00 (the current value is 4.00).
> COMMENT: (None)
> JOB RUN: (None)
>
|||More information:
select * from sysperfinfo where counter_name = 'Number of Deadlocks/sec'
object_name counter_name instance_name cntr_value cntr_type
MSSQL$INST01:Locks Number of Deadlocks/sec Extent 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Key 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Page 4 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Table 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec RID 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Database 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec _Total 4 272696320
I have 4 page deadlocks that will not release or reset. SP_LOCK shows no
current page locks.
Tim
"Tim" <Tim@.NOSpam.com> wrote in message
news:#0H6MnTsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> I have setup a SQL alert that email's me if deadlocks occur. I recently
> started getting flooded with alot of emails(see below) with deadlocks.
The
> current value does not drop back down, it remains at 4 until I restart the
> SQL server. This has happened once in the past before, about 3 weeks ago.
> We fixed it by restarting the server. I have DBCC OPENTRAN on all
databases
> and there are no active transaction open. Perfmon shows that I have 0
> deadlocks but still I get these emails. Turning trace 1204 also shows me
0
> deadlocks occurring. Can someone help me.
> Tim
> ----
--
> --
> DATE/TIME: 10/13/2004 10:24:02 AM
> DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec'
> (instance '_Total') of object 'MSSQL$INST01:Locks' is now above the
> threshold of 3.00 (the current value is 4.00).
> COMMENT: (None)
> JOB RUN: (None)
>
|||Hi Tim,
To troubleshoot this issue, there would be several other things to try and
to check. However, as this is an issue that happens randomly, we may spend
extensive time to narrow it down since random issues are always hard to
troubleshoot. I would like to recommend that you contact Microsoft Product
Support Services and open a support incident and work with a dedicated
Support Professional.
Please be advised that contacting phone support will be a charged call.
However, if you are simply requesting a hotfix be sent to you and no other
support then charges are usually refunded or waived.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
For now, from your descriptions, I understood that something were blocked
more frequently than you had exptected. Have I understood you? Correct me
if I was wrong.
Based on my scope, you'd better follow the instrunctions in KB below to
collect more information and what cause the block. Blocking could becaused
by various factors
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/kb/224453/en-us
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Deadlocks increasing

I have setup a SQL alert that email's me if deadlocks occur. I recently
started getting flooded with alot of emails(see below) with deadlocks. The
current value does not drop back down, it remains at 4 until I restart the
SQL server. This has happened once in the past before, about 3 weeks ago.
We fixed it by restarting the server. I have DBCC OPENTRAN on all databases
and there are no active transaction open. Perfmon shows that I have 0
deadlocks but still I get these emails. Turning trace 1204 also shows me 0
deadlocks occurring. Can someone help me.
Tim
----
--
DATE/TIME: 10/13/2004 10:24:02 AM
DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec'
(instance '_Total') of object 'MSSQL$INST01:Locks' is now above the
threshold of 3.00 (the current value is 4.00).
COMMENT: (None)
JOB RUN: (None)More information to supply:
"Tim" <Tim@.NOSpam.com> wrote in message
news:#0H6MnTsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> I have setup a SQL alert that email's me if deadlocks occur. I recently
> started getting flooded with alot of emails(see below) with deadlocks.
The
> current value does not drop back down, it remains at 4 until I restart the
> SQL server. This has happened once in the past before, about 3 weeks ago.
> We fixed it by restarting the server. I have DBCC OPENTRAN on all
databases
> and there are no active transaction open. Perfmon shows that I have 0
> deadlocks but still I get these emails. Turning trace 1204 also shows me
0
> deadlocks occurring. Can someone help me.
> Tim
> ----
--
> --
> DATE/TIME: 10/13/2004 10:24:02 AM
> DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec'
> (instance '_Total') of object 'MSSQL$INST01:Locks' is now above the
> threshold of 3.00 (the current value is 4.00).
> COMMENT: (None)
> JOB RUN: (None)
>|||More information:
select * from sysperfinfo where counter_name = 'Number of Deadlocks/sec'
object_name counter_name instance_name cntr_value cntr_type
MSSQL$INST01:Locks Number of Deadlocks/sec Extent 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Key 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Page 4 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Table 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec RID 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Database 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec _Total 4 272696320
I have 4 page deadlocks that will not release or reset. SP_LOCK shows no
current page locks.
Tim
"Tim" <Tim@.NOSpam.com> wrote in message
news:#0H6MnTsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> I have setup a SQL alert that email's me if deadlocks occur. I recently
> started getting flooded with alot of emails(see below) with deadlocks.
The
> current value does not drop back down, it remains at 4 until I restart the
> SQL server. This has happened once in the past before, about 3 weeks ago.
> We fixed it by restarting the server. I have DBCC OPENTRAN on all
databases
> and there are no active transaction open. Perfmon shows that I have 0
> deadlocks but still I get these emails. Turning trace 1204 also shows me
0
> deadlocks occurring. Can someone help me.
> Tim
> ----
--
> --
> DATE/TIME: 10/13/2004 10:24:02 AM
> DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec'
> (instance '_Total') of object 'MSSQL$INST01:Locks' is now above the
> threshold of 3.00 (the current value is 4.00).
> COMMENT: (None)
> JOB RUN: (None)
>|||Hi Tim,
To troubleshoot this issue, there would be several other things to try and
to check. However, as this is an issue that happens randomly, we may spend
extensive time to narrow it down since random issues are always hard to
troubleshoot. I would like to recommend that you contact Microsoft Product
Support Services and open a support incident and work with a dedicated
Support Professional.
Please be advised that contacting phone support will be a charged call.
However, if you are simply requesting a hotfix be sent to you and no other
support then charges are usually refunded or waived.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
For now, from your descriptions, I understood that something were blocked
more frequently than you had exptected. Have I understood you? Correct me
if I was wrong.
Based on my scope, you'd better follow the instrunctions in KB below to
collect more information and what cause the block. Blocking could becaused
by various factors
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/kb/224453/en-us
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!sql

Deadlocks increasing

I have setup a SQL alert that email's me if deadlocks occur. I recently
started getting flooded with alot of emails(see below) with deadlocks. The
current value does not drop back down, it remains at 4 until I restart the
SQL server. This has happened once in the past before, about 3 weeks ago.
We fixed it by restarting the server. I have DBCC OPENTRAN on all databases
and there are no active transaction open. Perfmon shows that I have 0
deadlocks but still I get these emails. Turning trace 1204 also shows me 0
deadlocks occurring. Can someone help me.
Tim
----
--
DATE/TIME: 10/13/2004 10:24:02 AM
DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec'
(instance '_Total') of object 'MSSQL$INST01:Locks' is now above the
threshold of 3.00 (the current value is 4.00).
COMMENT: (None)
JOB RUN: (None)More information to supply:
"Tim" <Tim@.NOSpam.com> wrote in message
news:#0H6MnTsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> I have setup a SQL alert that email's me if deadlocks occur. I recently
> started getting flooded with alot of emails(see below) with deadlocks.
The
> current value does not drop back down, it remains at 4 until I restart the
> SQL server. This has happened once in the past before, about 3 weeks ago.
> We fixed it by restarting the server. I have DBCC OPENTRAN on all
databases
> and there are no active transaction open. Perfmon shows that I have 0
> deadlocks but still I get these emails. Turning trace 1204 also shows me
0
> deadlocks occurring. Can someone help me.
> Tim
> ----
--
> --
> DATE/TIME: 10/13/2004 10:24:02 AM
> DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec'
> (instance '_Total') of object 'MSSQL$INST01:Locks' is now above the
> threshold of 3.00 (the current value is 4.00).
> COMMENT: (None)
> JOB RUN: (None)
>|||More information:
select * from sysperfinfo where counter_name = 'Number of Deadlocks/sec'
object_name counter_name instance_name cntr_value cntr_type
MSSQL$INST01:Locks Number of Deadlocks/sec Extent 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Key 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Page 4 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Table 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec RID 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec Database 0 272696320
MSSQL$INST01:Locks Number of Deadlocks/sec _Total 4 272696320
I have 4 page deadlocks that will not release or reset. SP_LOCK shows no
current page locks.
Tim
"Tim" <Tim@.NOSpam.com> wrote in message
news:#0H6MnTsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> I have setup a SQL alert that email's me if deadlocks occur. I recently
> started getting flooded with alot of emails(see below) with deadlocks.
The
> current value does not drop back down, it remains at 4 until I restart the
> SQL server. This has happened once in the past before, about 3 weeks ago.
> We fixed it by restarting the server. I have DBCC OPENTRAN on all
databases
> and there are no active transaction open. Perfmon shows that I have 0
> deadlocks but still I get these emails. Turning trace 1204 also shows me
0
> deadlocks occurring. Can someone help me.
> Tim
> ----
--
> --
> DATE/TIME: 10/13/2004 10:24:02 AM
> DESCRIPTION: The SQL Server performance counter 'Number of Deadlocks/sec'
> (instance '_Total') of object 'MSSQL$INST01:Locks' is now above the
> threshold of 3.00 (the current value is 4.00).
> COMMENT: (None)
> JOB RUN: (None)
>|||Hi Tim,
To troubleshoot this issue, there would be several other things to try and
to check. However, as this is an issue that happens randomly, we may spend
extensive time to narrow it down since random issues are always hard to
troubleshoot. I would like to recommend that you contact Microsoft Product
Support Services and open a support incident and work with a dedicated
Support Professional.
Please be advised that contacting phone support will be a charged call.
However, if you are simply requesting a hotfix be sent to you and no other
support then charges are usually refunded or waived.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
For now, from your descriptions, I understood that something were blocked
more frequently than you had exptected. Have I understood you? Correct me
if I was wrong.
Based on my scope, you'd better follow the instrunctions in KB below to
collect more information and what cause the block. Blocking could becaused
by various factors
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/kb/224453/en-us
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Tuesday, March 27, 2012

deadlocks

If an instance of SQL 2005 was in use and was using row versioning,
under what circumstances would the below error occur?

Transaction (Process ID 56) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction

We used to get this sort of thing when a large copy process was running
under a transaction, but all it was doing was reading the records and
creating brand new records yet would still lock the entire table. Once
we enabled the row versioning, we stopped having this issue, but it
seems that there are some circumstances in which it still happens, i.e.
the above error.

Any ideas how that might occur?pb648174 (google@.webpaul.net) writes:
> If an instance of SQL 2005 was in use and was using row versioning,
> under what circumstances would the below error occur?
> Transaction (Process ID 56) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction
> We used to get this sort of thing when a large copy process was running
> under a transaction, but all it was doing was reading the records and
> creating brand new records yet would still lock the entire table. Once
> we enabled the row versioning, we stopped having this issue, but it
> seems that there are some circumstances in which it still happens, i.e.
> the above error.
> Any ideas how that might occur?

Without knowledge of the code, and not have seen the deadlock trace?
Not even knowing which of the two varities of snapshot isolation
you are using. SET TRANSACTION LEVEL SHAPSHOT, or READ COMMITTED
SNAPSHOT?

To get a deadlock trace in the SQL Server error log, enable trace
flags 1222 and 3605. (It used be 1204, but 1222 is a new flag, which
gives better information.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I didn't realize there were multiple kinds.. We are using ALTER
DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

My questions is more of a general one - If row versioning is being used
and a particular record is involved in a transaction, should other
transactions just get the older version and not have to respect any
locks? We are seeing blocking happen for normal read operations, which
seems like it shouldn't happen. A write blocking I could see, but the
read blocking doesn't make sense to me.|||pb648174 (google@.webpaul.net) writes:
> I didn't realize there were multiple kinds.. We are using ALTER
> DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

The other one you achieve with ALTER DATABASE db SET
ALLOW_SNAPSHOT_ISOLATION ON. Transactions what want snapshots, then
need to say SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

The two yields slight different results. Pure shapshot isolation, gives
you the state of the database as it looked when the transaction started.
Read Committed Snapshot Isolation (RCSI) is an alternate implementation
of the read committed isolation level. An RCSI transaction can pick up
data that did not exist when the transaction started, but that committed
before the transaction came about to read it.

> My questions is more of a general one - If row versioning is being used
> and a particular record is involved in a transaction, should other
> transactions just get the older version and not have to respect any
> locks? We are seeing blocking happen for normal read operations, which
> seems like it shouldn't happen. A write blocking I could see, but the
> read blocking doesn't make sense to me.

Without any repro it's difficult to comment things out of the blue. However,
note that if you are using alternate isolation level, either by
SET TRANSACTION ISOLATION LEVEL or by query/table hints, the snapshot is
not involved. For instance, run this in one query window:

CREATE TABLE hubba (a int NOT NULL PRIMARY KEY)
go
INSERT hubba(a) VALUES (12)
go
BEGIN TRANSACTION
go
INSERT hubba(a) VALUES (2)
go

Then in another window run:

SELECT MAX(a), MIN(a) FROM hubba

This returns (12, 12). Now try_

SELECT MAX(a), MIN(a) FROM hubba WITH (REPEATABLEREAD)

This blocks, because the isolation level is no longer READ COMMITTED.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ahhhhh... Now we are getting somewhere. I think other transactions are
set as serializable, so that would explain it. Thanks for the tip.

Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have') and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec:(0x5A2E5578)
Value:0x52fa6780 Cost:(0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.
> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.
> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

Sunday, March 25, 2012

deadlock victim even using temp table

Hi. I am struggling to understand why I get the following error in
using the stored procedure noted below. I recently starting using a
temp table as a way of providing custom paging in asp.net and this
problem has occured ever since (maybe 10 times per day with an average
of 30 users on all day).
Here is the error: "Transaction (Process ID ##) was deadlocked on lock
resources with another process and has been chosen as the deadlock
victim. Rerun the transaction"
The client code is below. It uses a DataAdapter to fill a dataset that
is used to populate a datagrid. The long store procedure is below
that. It essentialy fills the temp table with records that are chosen
then retrieves all the necessary fields for the datagrid using whatever
page that is selected. There is code in there to support sorting and
hopefully it's not too confusing.
I apologize for the long post, I didn't want to remove parts of the SP
to make it shorter in case I removed an important element. I admit, I
am only an intermediate programmer so I may be missing some
fundamentals. Hopefully this is obvious to someone.
Thanks in advance.
Jeff
-- client code --
' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
Dim myCommand As New SqlDataAdapter("tochange",
myConnection)
'Dim myCommand As New SqlDataAdapter
'myCommand.SelectCommand.Connection = myConnection
myCommand.SelectCommand.CommandType =
CommandType.StoredProcedure
myCommand.SelectCommand.CommandText =
"dbo.csp_cGeneral_GetRequests2"
myCommand.SelectCommand.Parameters.Add("@.PortalID",
SqlDbType.Int).Value = iPortalID
myCommand.SelectCommand.Parameters.Add("@.Status",
SqlDbType.VarChar, 10).Value = Status
myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
SqlDbType.Int).Value = RequestSeqID
myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
SqlDbType.VarChar, 30).Value = BorrowerLastName
myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
SqlDbType.VarChar, 30).Value = LoanOfficerCompany
myCommand.SelectCommand.Parameters.Add("@.BegDate",
SqlDbType.VarChar, 25).Value = BegDate
myCommand.SelectCommand.Parameters.Add("@.EndDate",
SqlDbType.VarChar, 25).Value = EndDate
myCommand.SelectCommand.Parameters.Add("@.ContactName",
SqlDbType.VarChar, 30).Value = ContactName
myCommand.SelectCommand.Parameters.Add("@.AgentID",
SqlDbType.Int).Value = AgentID
myCommand.SelectCommand.Parameters.Add("@.HasDocs",
SqlDbType.Int).Value = HasDocs
myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
SqlDbType.Int).Value = iStaffSearchID
myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
SqlDbType.Int).Value = _currentPageNumber
myCommand.SelectCommand.Parameters.Add("@.PageSize",
SqlDbType.Int).Value = iPagesize
myCommand.SelectCommand.Parameters.Add("@.SortField",
SqlDbType.VarChar, 30).Value = strSortColumn
myCommand.SelectCommand.Parameters.Add("@.UserID",
SqlDbType.Int).Value = UserID
myCommand.SelectCommand.Parameters.Add("@.Role",
SqlDbType.VarChar, 20).Value = Role
myCommand.SelectCommand.Parameters.Add("@.Maps",
SqlDbType.VarChar, 20).Value = sMaps
' Create and Fill the DataSet
Dim myDataSet As New DataSet
myCommand.Fill(myDataSet, "Requests")
Dim myTable As DataTable = myDataSet.Tables("Requests")
If Not myTable Is Nothing Then
If myTable.Rows.Count > 0 Then
Dim dr As DataRow = myTable.Rows(0)
_TotalRecords = dr.Item("TotalRecords")
Else
_TotalRecords = 0
End If
End If
' Return the DataSet
-- stored procedure --
ALTER procedure dbo.csp_cGeneral_GetRequests2
@.PortalID int,
@.Status varchar(10) = "-1",
@.RequestSeqID int = -1,
@.BorrowerLastName varchar(30) = "-1",
@.LoanOfficerCompany varchar(30) = "-1",
@.BegDate varchar(25) = "-1",
@.EndDate varchar(25) = "-1",
@.ContactName varchar(30) = "-1",
@.AgentID Int = Null,
@.UserID int = 0,
@.Role varchar(20) = 'None',
@.HasDocs decimal = -1,
@.CurrentPage int,
@.PageSize int,
@.SortField varchar(30),
@.Maps varchar(20),
@.AssignedStaffID Int --(-1 all, -2 not in list)
as
--if @.AssignedStaffID = -2
--begin
--
--end
Declare @.TotalRecords int
Declare @.Status1 int
Declare @.Status2 int
Declare @.AssignedAgentID int
Declare @.CustomerID int
set @.AssignedAgentID = 0
set @.CustomerID = 0
if @.Role = 'NotaryAgent'
Begin
set @.AssignedAgentID = @.UserID
set @.CustomerID = Null
end
if @.Role = 'Customer'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = @.UserID
end
if @.Role = 'ServiceOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
if @.Role = 'AgentOwner'
Begin
set @.AssignedAgentID = Null
set @.CustomerID = Null
end
set @.Status1 = -1
set @.Status2 = -1
if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
begin
set @.Status1 = cast(@.Status as int)
set @.Status2 = cast(@.Status as int)
end
else
begin
if @.Status = '123'
Begin
set @.Status1 = 1
set @.Status2 = 3
end
if @.Status = '45'
Begin
set @.Status1 = 4
set @.Status2 = 5
end
if @.Status = '123456'
Begin
set @.Status1 = 1
set @.Status2 = 6
end
if @.Status = '1234569'
Begin
set @.Status1 = 1
set @.Status2 = 10
end
end
Declare @.BegDate2 as smalldatetime
Declare @.EndDate2 as smalldatetime
if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
isdate(@.EndDate) = 0
begin
set @.BegDate2 = Null
Set @.EndDate2 = Null
end
else
begin
set @.BegDate2 = cast(@.BegDate as smalldatetime)
Set @.EndDate2 = cast(@.EndDate as smalldatetime)
end
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
RequestID int,
FileSize int,
DocCount int
)
INSERT INTO #TempTable
(
RequestID,
FileSize,
DocCount
)
SELECT
RequestID,
FileSize,
DocCount
>From (
Select SR.RequestID, isnull(FileSize,0) as FileSize,
isnull(temp1.DocCount,0) as DocCount,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN SRS.StatusOrder
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN 0
WHEN 'HasCust' THEN SR.UserID
WHEN 'StaffOrder' THEN Staff.StaffOrder
ELSE SRS.StatusOrder
END AS sortcol0,
CASE @.SortField
WHEN 'Request' THEN ''
WHEN 'Status' THEN ''
WHEN 'Borrower' THEN SR.BorrowerLastName
WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Location' THEN isnull(SR.SigningCity,'')
WHEN 'ContactInfo' THEN SR.ContactName
WHEN 'Agent' THEN Users.LastName
WHEN 'FileSize' THEN ''
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN Staff.StaffInitials
ELSE ''
END AS sortcol1,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
WHEN 'Borrower' THEN SR.BorrowerFirstName
WHEN 'Date' THEN SR.SigningTime
WHEN 'Location' THEN isnull(SR.SigningState,'')
WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
WHEN 'Agent' THEN Users.FirstName
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE convert(varchar(20),SR.SigningDate,112)
END AS sortcol2,
CASE @.SortField
WHEN 'Request' THEN '0'
WHEN 'Status' THEN SR.SigningTime
WHEN 'Borrower' THEN '0'
WHEN 'Date' THEN '0'
WHEN 'Location' THEN '0'
WHEN 'ContactInfo' THEN '0'
WHEN 'Agent' THEN '0'
WHEN 'FileSize' THEN '0'
WHEN 'HasCust' THEN '0'
WHEN 'StaffOrder' THEN '0'
ELSE SR.SigningTime
END AS sortcol3,
CASE @.SortField
WHEN 'Request' THEN 0
WHEN 'Status' THEN 0
WHEN 'Borrower' THEN 0
WHEN 'Date' THEN 0
WHEN 'Location' THEN 0
WHEN 'ContactInfo' THEN 0
WHEN 'Agent' THEN 0
WHEN 'FileSize' THEN FileSize
WHEN 'HasCust' THEN 0
WHEN 'StaffOrder' THEN 0
ELSE 0
END AS sortcol4,
SR.RequestSeqID AS sortcol5
>From dbo.ctbl_SigningRequests SR
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join (
Select ctbl_Docs.RequestID,
case when sum(Case when ctbl_Docs.LoanDocs = 0 and
ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as
DocCount,
cast(sum(ctbl_Docs.filesize) as decimal)/1000000 as filesize from
ctbl_Docs
Where ctbl_Docs.PortalID = @.PortalID
Group by ctbl_Docs.RequestID
) as temp1 ON SR.RequestID = temp1.RequestID
Where
SR.PortalID = @.PortalID
and SR.InActiveDate is null
and SR.BorrowerLastName like
IsNull(nullif('%'+@.BorrowerLastName+'%',
'%-1%'),'%'+SR.BorrowerLastName+'%')
and SR.LoanOfficerCompany like
IsNull(nullif('%'+@.LoanOfficerCompany+'%
','%-1%'),'%'+SR.LoanOfficerCompany+
'%')
and SR.ContactName like
IsNull(nullif('%'+@.ContactName+'%','%-1%'),'%'+SR.ContactName+'%')
and SR.RequestSeqID =
isnull(Nullif(@.RequestSeqID,-1),SR.RequestSeqID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AgentID,-1),IsNull(SR.AssignedAgent,-1))
and SR.SigningStatusID Between
IsNull(Nullif(@.Status1,-1),SR.SigningStatusID) and
IsNull(NullIf(@.Status2,-1),SR.SigningStatusID)
and SR.SigningDate Between IsNull(@.BegDate2,SR.SigningDate) and
IsNull(@.EndDate2,SR.SigningDate)
and SR.DeleteDate Is Null
and isnull(temp1.FileSize,0) > cast(@.HasDocs as decimal)
and SR.UserID = IsNull(@.CustomerID,SR.UserID)
and IsNull(SR.AssignedAgent,-1) =
isnull(Nullif(@.AssignedAgentID,-1),IsNull(SR.AssignedAgent,-1))
and IsNull(SR.AssignedStaffID,-1) =
isnull(Nullif(@.AssignedStaffID,-1),IsNull(SR.AssignedStaffID,-1))
) as t1
order by sortcol0, sortcol1, sortcol2, sortcol3, sortcol4 DESC,
sortcol5
--Create variable to identify the first and last record that should be
selected
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
if @.CurrentPage > ceiling(cast(@.TotalRecords as float)/cast (@.PageSize
as float))
set @.CurrentPage = isnull(ceiling(@.TotalRecords / @.PageSize),1)
--select ceiling(cast(@.TotalRecords as float)/cast (@.PageSize as
float))
--select ceiling(cast(31/10 as float))
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
--Return the total number of records available as an output parameter
--Select one page of data based on the record numbers above
Select SR.RequestID, SR.RequestSeqID, SRS.StatusNameShort, SR.UserID,
SRS.StatusOrder, SR.SigningDate, SR.SigningTime, SR.LoanNumber,
Case When SR.InvoiceCreated is Null then 0 else 1 end as
InvoiceCreated,
Case When SR.Invoiced is Null then 0 else 1 end as Invoiced,
Case When SR.InvoicePaid is Null then 0 else 1 end as CustomerPaid,
Case When SR.NotaryPaid is Null then 0 else 1 end as NotaryPaid,
Case When SR.Invoiced is Null then '0' else '1' end + Case When
SR.InvoicePaid is Null then '0' else '1' end
+ Case When SR.NotaryPaid is Null then '0' else '1' end as IconSort,
SR.ContactName, Isnull(SR.ContactEmail,'') as ContactEmail,
Isnull(SR.ContactPhone,'') as ContactPhone,
-- SR.ContactName + '' + left(SR.LoanOfficerCompany,10) as
ContactInfo, SR.BorrowerLastName, SR.BorrowerFirstName,
Left(SR.ContactName,15) as ContactInfo, SR.BorrowerLastName,
SR.BorrowerFirstName,
SR.LoanOfficerCompany, left(SR.LoanOfficerCompany,10) as
LoanOfficerCompanyShort,
Case When Users.UserID Is Null then '(Assign Notary)' else
Users.FirstName + ' ' + Users.LastName end as AssignedAgentName,
IsNull(Users.UserID,0) as AssignedAgentID, isnull(SR.SigningCity,'')
+ ', ' + isnull(SR.SigningState,'') as CityState,
isnull(SR.SigningZip,'') as SigningZip,
SR.LastChangedByMobile,
cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1)) as HudDocsFlag,
Case cast(isnull(SR.DocsIn,0) as char(1)) + cast(isnull(SR.HudIn,0) as
char(1)) + cast(#TempTable.DocCount as char(1))
When '001' then 'Loan Docs and Title are not in. Other docs exist.'
When '011' then 'Loan Docs are not in. Title is in. Other docs
exist.'
When '101' then 'Loan Docs are in. Title is not in. Other docs
exist.'
When '111' then 'Loan Docs and Title are in. Other docs exist.'
When '000' then 'Loan Docs and Title are not in.'
When '010' then 'Loan Docs are not in. Title is in.'
When '100' then 'Loan Docs are in. Title is not in.'
When '110' then 'Loan Docs and Title are in.'
else 'Desc error.'
end as HudDocsFlagDesc,
#TempTable.FileSize,
Case When SR.UserID = 0 then '-' else 'C' end as CustomerFlag,
Case When SR.AssignedAgent = 0 and @.Maps='MSN' then
''
When SR.AssignedAgent <> 0 and @.Maps='MSN' then
replace(replace('http://maps.msn.com/directionsFind.aspx?strt1=' +
isnull(Users.Street,'') + '&city1=' + isnull(Users.City,'') + '&zipc1='
+ isnull(Users.PostalCode,'')
+ '&cnty1=0&strt2=' + isnull(SR.SigningAddress1,'') + '&city2=' +
isnull(SR.SigningCity,'') + '&zipc2=' + isnull(SR.SigningZip,'') +
'&cnty2=0&rtyp=1&unit=0',' ','%20'),'#','')
When SR.AssignedAgent = 0 and @.Maps='QUEST' then
''
When SR.AssignedAgent <> 0 and @.Maps='QUEST' then
replace(replace('http://www.mapquest.com/directions/main.adp?go=1&do=nw&rmm=
1&un=m&cl=EN&ct=NA&rsres=1&1a='
+ isnull(Users.Street,'') + '&1c=' + isnull(Users.City,'') + '&1s=' +
isnull(Users.Region,'') + '&1z=' + isnull(Users.PostalCode,'')
+ '&2a=' + isnull(SR.SigningAddress1,'') + '&2c=' +
isnull(SR.SigningCity,'') + '&2s=' + isnull(SR.SigningState,'') +
'&2z=' + isnull(SR.SigningZip,''),' ','%20'),'#','')
else ''
end as MapLink,
cast(isnull(ctbl_PortalData.FileSSLActivate,1) as varchar(1)) as
FileSSLActivate,
(isnull(SR.PriceQty_Agent1,0) * isnull(SR.PriceAmt_Agent1,0))
+(isnull(SR.PriceQty_Agent2,0) * isnull(SR.PriceAmt_Agent2,0))
+(isnull(SR.PriceQty_Agent3,0) * isnull(SR.PriceAmt_Agent3,0))
+(isnull(SR.PriceQty_Agent4,0) * isnull(SR.PriceAmt_Agent4,0))
+(isnull(SR.PriceQty_Agent5,0) * isnull(SR.PriceAmt_Agent5,0)) as
NotaryInvoiceTotal, @.TotalRecords as TotalRecords,
isnull(Staff.StaffInitials, Isnull(Staff.FirstName,'***')) as Staff,
isnull(Staff.StaffOrder,0) as StaffOrder
>From dbo.ctbl_SigningRequests SR
inner join #TempTable
on #TempTable.RequestID = SR.RequestID
Left Outer Join dbo.ctbl_SigningRequestStatus SRS
ON SR.SigningStatusID = SRS.SigningStatusID
Left Outer Join dbo.ctbl_UserData UD
ON SR.AssignedAgent = UD.UserID
Left Outer Join dbo.Users Users
ON SR.AssignedAgent = Users.UserID
Left Outer Join dbo.Users Staff
ON SR.AssignedStaffID = Staff.UserID
Left Outer Join ctbl_PortalData
On ctbl_PortalData.PortalID = @.PortalID
WHERE
ID > @.FirstRec
AND
ID < @.LastRec
order by #TempTable.[ID]Check the transaction isolation level: you can probably live with READ
COMMITTED. Also make sure that the procedure isn't running in the context o
f
a transaction. If that isn't the problem, then make sure that indexes exist
on the columns joined and that the execution plan uses them. You may have t
o
coerce the optimizer with a hint or two.
"jhonz@.etsmail.com" wrote:

> Hi. I am struggling to understand why I get the following error in
> using the stored procedure noted below. I recently starting using a
> temp table as a way of providing custom paging in asp.net and this
> problem has occured ever since (maybe 10 times per day with an average
> of 30 users on all day).
> Here is the error: "Transaction (Process ID ##) was deadlocked on lock
> resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction"
> The client code is below. It uses a DataAdapter to fill a dataset that
> is used to populate a datagrid. The long store procedure is below
> that. It essentialy fills the temp table with records that are chosen
> then retrieves all the necessary fields for the datagrid using whatever
> page that is selected. There is code in there to support sorting and
> hopefully it's not too confusing.
> I apologize for the long post, I didn't want to remove parts of the SP
> to make it shorter in case I removed an important element. I admit, I
> am only an intermediate programmer so I may be missing some
> fundamentals. Hopefully this is obvious to someone.
> Thanks in advance.
> Jeff
>
> -- client code --
> ' Create Instance of Connection and Command Object
> Dim myConnection As New
> SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
> Dim myCommand As New SqlDataAdapter("tochange",
> myConnection)
> 'Dim myCommand As New SqlDataAdapter
> 'myCommand.SelectCommand.Connection = myConnection
> myCommand.SelectCommand.CommandType =
> CommandType.StoredProcedure
> myCommand.SelectCommand.CommandText =
> "dbo.csp_cGeneral_GetRequests2"
> myCommand.SelectCommand.Parameters.Add("@.PortalID",
> SqlDbType.Int).Value = iPortalID
> myCommand.SelectCommand.Parameters.Add("@.Status",
> SqlDbType.VarChar, 10).Value = Status
> myCommand.SelectCommand.Parameters.Add("@.RequestSeqID",
> SqlDbType.Int).Value = RequestSeqID
> myCommand.SelectCommand.Parameters.Add("@.BorrowerLastName",
> SqlDbType.VarChar, 30).Value = BorrowerLastName
> myCommand.SelectCommand.Parameters.Add("@.LoanOfficerCompany",
> SqlDbType.VarChar, 30).Value = LoanOfficerCompany
> myCommand.SelectCommand.Parameters.Add("@.BegDate",
> SqlDbType.VarChar, 25).Value = BegDate
> myCommand.SelectCommand.Parameters.Add("@.EndDate",
> SqlDbType.VarChar, 25).Value = EndDate
> myCommand.SelectCommand.Parameters.Add("@.ContactName",
> SqlDbType.VarChar, 30).Value = ContactName
> myCommand.SelectCommand.Parameters.Add("@.AgentID",
> SqlDbType.Int).Value = AgentID
> myCommand.SelectCommand.Parameters.Add("@.HasDocs",
> SqlDbType.Int).Value = HasDocs
> myCommand.SelectCommand.Parameters.Add("@.AssignedStaffID",
> SqlDbType.Int).Value = iStaffSearchID
> myCommand.SelectCommand.Parameters.Add("@.CurrentPage",
> SqlDbType.Int).Value = _currentPageNumber
> myCommand.SelectCommand.Parameters.Add("@.PageSize",
> SqlDbType.Int).Value = iPagesize
> myCommand.SelectCommand.Parameters.Add("@.SortField",
> SqlDbType.VarChar, 30).Value = strSortColumn
> myCommand.SelectCommand.Parameters.Add("@.UserID",
> SqlDbType.Int).Value = UserID
> myCommand.SelectCommand.Parameters.Add("@.Role",
> SqlDbType.VarChar, 20).Value = Role
> myCommand.SelectCommand.Parameters.Add("@.Maps",
> SqlDbType.VarChar, 20).Value = sMaps
> ' Create and Fill the DataSet
> Dim myDataSet As New DataSet
> myCommand.Fill(myDataSet, "Requests")
> Dim myTable As DataTable = myDataSet.Tables("Requests")
> If Not myTable Is Nothing Then
> If myTable.Rows.Count > 0 Then
> Dim dr As DataRow = myTable.Rows(0)
> _TotalRecords = dr.Item("TotalRecords")
> Else
> _TotalRecords = 0
> End If
> End If
> ' Return the DataSet
>
> -- stored procedure --
> ALTER procedure dbo.csp_cGeneral_GetRequests2
> @.PortalID int,
> @.Status varchar(10) = "-1",
> @.RequestSeqID int = -1,
> @.BorrowerLastName varchar(30) = "-1",
> @.LoanOfficerCompany varchar(30) = "-1",
> @.BegDate varchar(25) = "-1",
> @.EndDate varchar(25) = "-1",
> @.ContactName varchar(30) = "-1",
> @.AgentID Int = Null,
> @.UserID int = 0,
> @.Role varchar(20) = 'None',
> @.HasDocs decimal = -1,
> @.CurrentPage int,
> @.PageSize int,
> @.SortField varchar(30),
> @.Maps varchar(20),
> @.AssignedStaffID Int --(-1 all, -2 not in list)
> as
> --if @.AssignedStaffID = -2
> --begin
> --
> --end
> Declare @.TotalRecords int
> Declare @.Status1 int
> Declare @.Status2 int
> Declare @.AssignedAgentID int
> Declare @.CustomerID int
> set @.AssignedAgentID = 0
> set @.CustomerID = 0
> if @.Role = 'NotaryAgent'
> Begin
> set @.AssignedAgentID = @.UserID
> set @.CustomerID = Null
> end
> if @.Role = 'Customer'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = @.UserID
> end
> if @.Role = 'ServiceOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> if @.Role = 'AgentOwner'
> Begin
> set @.AssignedAgentID = Null
> set @.CustomerID = Null
> end
> set @.Status1 = -1
> set @.Status2 = -1
> if len(@.Status) = 1 or (len(@.Status) = 2 and not @.Status = '45')
> begin
> set @.Status1 = cast(@.Status as int)
> set @.Status2 = cast(@.Status as int)
> end
> else
> begin
> if @.Status = '123'
> Begin
> set @.Status1 = 1
> set @.Status2 = 3
> end
> if @.Status = '45'
> Begin
> set @.Status1 = 4
> set @.Status2 = 5
> end
> if @.Status = '123456'
> Begin
> set @.Status1 = 1
> set @.Status2 = 6
> end
> if @.Status = '1234569'
> Begin
> set @.Status1 = 1
> set @.Status2 = 10
> end
> end
> Declare @.BegDate2 as smalldatetime
> Declare @.EndDate2 as smalldatetime
> if @.BegDate = '-1' or @.EndDate = '-1' or isdate(@.BegDate) = 0 or
> isdate(@.EndDate) = 0
> begin
> set @.BegDate2 = Null
> Set @.EndDate2 = Null
> end
> else
> begin
> set @.BegDate2 = cast(@.BegDate as smalldatetime)
> Set @.EndDate2 = cast(@.EndDate as smalldatetime)
> end
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> RequestID int,
> FileSize int,
> DocCount int
> )
> INSERT INTO #TempTable
> (
> RequestID,
> FileSize,
> DocCount
> )
> SELECT
> RequestID,
> FileSize,
> DocCount
> Select SR.RequestID, isnull(FileSize,0) as FileSize,
> isnull(temp1.DocCount,0) as DocCount,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN SRS.StatusOrder
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN 0
> WHEN 'HasCust' THEN SR.UserID
> WHEN 'StaffOrder' THEN Staff.StaffOrder
> ELSE SRS.StatusOrder
> END AS sortcol0,
> CASE @.SortField
> WHEN 'Request' THEN ''
> WHEN 'Status' THEN ''
> WHEN 'Borrower' THEN SR.BorrowerLastName
> WHEN 'Date' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Location' THEN isnull(SR.SigningCity,'')
> WHEN 'ContactInfo' THEN SR.ContactName
> WHEN 'Agent' THEN Users.LastName
> WHEN 'FileSize' THEN ''
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN Staff.StaffInitials
> ELSE ''
> END AS sortcol1,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN convert(varchar(20),SR.SigningDate,112)
> WHEN 'Borrower' THEN SR.BorrowerFirstName
> WHEN 'Date' THEN SR.SigningTime
> WHEN 'Location' THEN isnull(SR.SigningState,'')
> WHEN 'ContactInfo' THEN SR.LoanOfficerCompany
> WHEN 'Agent' THEN Users.FirstName
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE convert(varchar(20),SR.SigningDate,112)
> END AS sortcol2,
> CASE @.SortField
> WHEN 'Request' THEN '0'
> WHEN 'Status' THEN SR.SigningTime
> WHEN 'Borrower' THEN '0'
> WHEN 'Date' THEN '0'
> WHEN 'Location' THEN '0'
> WHEN 'ContactInfo' THEN '0'
> WHEN 'Agent' THEN '0'
> WHEN 'FileSize' THEN '0'
> WHEN 'HasCust' THEN '0'
> WHEN 'StaffOrder' THEN '0'
> ELSE SR.SigningTime
> END AS sortcol3,
> CASE @.SortField
> WHEN 'Request' THEN 0
> WHEN 'Status' THEN 0
> WHEN 'Borrower' THEN 0
> WHEN 'Date' THEN 0
> WHEN 'Location' THEN 0
> WHEN 'ContactInfo' THEN 0
> WHEN 'Agent' THEN 0
> WHEN 'FileSize' THEN FileSize
> WHEN 'HasCust' THEN 0
> WHEN 'StaffOrder' THEN 0
> ELSE 0
> END AS sortcol4,
> SR.RequestSeqID AS sortcol5
> Left Outer Join dbo.ctbl_SigningRequestStatus SRS
> ON SR.SigningStatusID = SRS.SigningStatusID
> Left Outer Join dbo.ctbl_UserData UD
> ON SR.AssignedAgent = UD.UserID
> Left Outer Join dbo.Users Users
> ON SR.AssignedAgent = Users.UserID
> Left Outer Join ctbl_PortalData
> On ctbl_PortalData.PortalID = @.PortalID
> Left Outer Join dbo.Users Staff
> ON SR.AssignedStaffID = Staff.UserID
> Left Outer Join (
> Select ctbl_Docs.RequestID,
> case when sum(Case when ctbl_Docs.LoanDocs = 0 and
> ctbl_Docs.TitleDocs = 0 then 1 else 0 end) > 0 then 1 else 0 end as|||Thanks, Brian. Couldn't I use WITH (NOLOCK) on the select that fills
the temptable and then later selects from it? READ COMMITTED seems to
be SQL 2000 default and is probably arleady set. I image the locks or
on the select a temp table should not be shared amongts users. In
ASP.Net's connection pooling, do you think things could get crossed
there.
I am not running a transaction so I think I am safe there.
I ran the execution plan and I don't see any table scans. Is that
sufficient indication that things are OK there?
Jeff

deadlock victim

h
im the below output from profiler..processes r getting deadlock and most of the time the below processes spid is causing blocking..but im unable to get procedure associated with that...
RPC Event 0 sp_executesql;
wot is sp_executesql;1 ?There are some trace falgs you can turn on to help resolve deadlocking
problems.
DBCC TRACEON (1204, 3605,-1)
will output info into your SQL Server ErrorLog.
--
HTH
Ryan Waight, MCDBA, MCSE
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:B1BF5BC8-8889-4054-9FDD-1FE3B52418B9@.microsoft.com...
> hi
> im the below output from profiler..processes r getting deadlock and most
of the time the below processes spid is causing blocking..but im unable to
get procedure associated with that...
> RPC Event 0 sp_executesql;1
> wot is sp_executesql;1 ?
>|||h
thnks for yr reply..but i failied to identify which the procedure associated with this...RPC Event 0 sp_executesql;
this "RPC Event 0 sp_executesql;1" im in getting profiler as deadlock processes

Thursday, March 22, 2012

deadlock Question

0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4Victim Resource Owner:
2005-06-14 13:46:48.00 spid4ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec

0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4Requested By:
2005-06-14 13:46:48.00 spid4Grant List 3::
2005-06-14 13:46:48.00 spid4Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4Grant List 1::
2005-06-14 13:46:48.00 spid4KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec

0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4Requested By:
2005-06-14 13:46:48.00 spid4Input Buf: RPC Event: SPAR42B04.dbo.Search;1
2005-06-14 13:46:48.00 spid4SPID: 93 ECID: 0 Statement Type: UPDATE Line
#: 95
2005-06-14 13:46:48.00 spid4Owner:0x55bbd100 Mode: X Flg:0x0 Ref:1
Life:02000000 SPID:93 ECID:0
2005-06-14 13:46:48.00 spid4Grant List 3::
2005-06-14 13:46:48.00 spid4KEY: 13:258099960:1 (8e0060ab9a82) CleanCnt:1
Mode: X Flags: 0x0
2005-06-14 13:46:48.00 spid4Node:1
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4Wait-for graph
Could you post the code that caused it?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:89F25B5D-11F5-433D-8BD9-C4734010684B@.microsoft.com...
HI,
below is the deadlock i am frequently getting in my application. In the
below error log, SPID 100 is blocked from its request for an Shared(S) lock
on object 258099960 because SPID 93 already has an exclusive lock on it. In
Node 2, SPID 93 is blocked from its request for an exclusive lock on Table
258099960
because SPID 100 has an Shared lock on it. SQL Server chose SPID 100 as the
deadlock victim to break the deadlock, as indicated by the Victim Resource
Owner entry.
I understood why it is happening but not completely and not able to resolve
this issue, can any one help.
================================================== =
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec

0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Victim Resource Owner:
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec

0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4 SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4 Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 1::
2005-06-14 13:46:48.00 spid4 KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec

deadlock Question

HI,
below is the deadlock i am frequently getting in my application. In the
below error log, SPID 100 is blocked from its request for an Shared(S) lock
on object 258099960 because SPID 93 already has an exclusive lock on it. In
Node 2, SPID 93 is blocked from its request for an exclusive lock on Table
258099960
because SPID 100 has an Shared lock on it. SQL Server chose SPID 100 as the
deadlock victim to break the deadlock, as indicated by the Victim Resource
Owner entry.
I understood why it is happening but not completely and not able to resolve
this issue, can any one help.
===================================================
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec:(0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Victim Resource Owner:
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec:(0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4 SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4 Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 1::
2005-06-14 13:46:48.00 spid4 KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec:(0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: SPAR42B04.dbo.Search;1
2005-06-14 13:46:48.00 spid4 SPID: 93 ECID: 0 Statement Type: UPDATE Line
#: 95
2005-06-14 13:46:48.00 spid4 Owner:0x55bbd100 Mode: X Flg:0x0 Ref:1
Life:02000000 SPID:93 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 KEY: 13:258099960:1 (8e0060ab9a82) CleanCnt:1
Mode: X Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:1
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 Wait-for graphCould you post the code that caused it?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:89F25B5D-11F5-433D-8BD9-C4734010684B@.microsoft.com...
HI,
below is the deadlock i am frequently getting in my application. In the
below error log, SPID 100 is blocked from its request for an Shared(S) lock
on object 258099960 because SPID 93 already has an exclusive lock on it. In
Node 2, SPID 93 is blocked from its request for an exclusive lock on Table
258099960
because SPID 100 has an Shared lock on it. SQL Server chose SPID 100 as the
deadlock victim to break the deadlock, as indicated by the Victim Resource
Owner entry.
I understood why it is happening but not completely and not able to resolve
this issue, can any one help.
===================================================
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec:(0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Victim Resource Owner:
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec:(0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4 SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4 Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 1::
2005-06-14 13:46:48.00 spid4 KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec:(0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: SPAR42B04.dbo.Search;1
2005-06-14 13:46:48.00 spid4 SPID: 93 ECID: 0 Statement Type: UPDATE Line
#: 95
2005-06-14 13:46:48.00 spid4 Owner:0x55bbd100 Mode: X Flg:0x0 Ref:1
Life:02000000 SPID:93 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 KEY: 13:258099960:1 (8e0060ab9a82) CleanCnt:1
Mode: X Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:1
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 Wait-for graph

deadlock Question

0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Victim Resource Owner:
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec

0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4 SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4 Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 1::
2005-06-14 13:46:48.00 spid4 KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec

0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: SPAR42B04.dbo.Search;1
2005-06-14 13:46:48.00 spid4 SPID: 93 ECID: 0 Statement Type: UPDATE Line
#: 95
2005-06-14 13:46:48.00 spid4 Owner:0x55bbd100 Mode: X Flg:0x0 Ref:1
Life:02000000 SPID:93 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 KEY: 13:258099960:1 (8e0060ab9a82) CleanCnt:1
Mode: X Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:1
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 Wait-for graphCould you post the code that caused it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:89F25B5D-11F5-433D-8BD9-C4734010684B@.microsoft.com...
HI,
below is the deadlock i am frequently getting in my application. In the
below error log, SPID 100 is blocked from its request for an Shared(S) lock
on object 258099960 because SPID 93 already has an exclusive lock on it. In
Node 2, SPID 93 is blocked from its request for an exclusive lock on Table
258099960
because SPID 100 has an Shared lock on it. SQL Server chose SPID 100 as the
deadlock victim to break the deadlock, as indicated by the Victim Resource
Owner entry.
I understood why it is happening but not completely and not able to resolve
this issue, can any one help.
========================================
===========
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec

0x56EDF598) Value:0x52
2005-06-14 13:46:48.00 spid4 Victim Resource Owner:
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:93
ECID:0 Ec

0x543E3548) Value:0x1ff
2005-06-14 13:46:48.00 spid4 Requested By:
2005-06-14 13:46:48.00 spid4 Grant List 3::
2005-06-14 13:46:48.00 spid4 Input Buf: RPC Event: FindCustomer;1
2005-06-14 13:46:48.00 spid4 SPID: 100 ECID: 0 Statement Type: SELECT Line
#: 1
2005-06-14 13:46:48.00 spid4 Owner:0x52f99720 Mode: S Flg:0x0 Ref:1
Life:02000000 SPID:100 ECID:0
2005-06-14 13:46:48.00 spid4 Grant List 1::
2005-06-14 13:46:48.00 spid4 KEY: 13:836250084:1 (8e0060ab9a82) CleanCnt:1
Mode: U Flags: 0x0
2005-06-14 13:46:48.00 spid4 Node:2
2005-06-14 13:46:48.00 spid4
2005-06-14 13:46:48.00 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:100
ECID:0 Ec

deadlock problem

I have inherited a problem from the guy I have taken over from. Below
is the log error message followed by the procedure in question. Anybody
have any ideas why this is deadlocking?
2006-04-03 15:00:40.14 spid4 Node:1
2006-04-03 15:00:40.14 spid4 RID: 8:1:89142:1
CleanCnt:1 Mode: X Flags: 0x2
2006-04-03 15:00:40.14 spid4 Grant List 3::
2006-04-03 15:00:40.14 spid4 Owner:0x3571b2c0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
2006-04-03 15:00:40.14 spid4 SPID: 63 ECID: 0 Statement Type:
SELECT Line #: 71
2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
FeltexJob_Update;1
2006-04-03 15:00:40.14 spid4 Requested By:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
U SPID:58 ECID:0 Ec:(0x53BF3570) Value:0x2c2a82a0 Cost:(0/B4)
2006-04-03 15:00:40.14 spid4
2006-04-03 15:00:40.14 spid4 Node:2
2006-04-03 15:00:40.14 spid4 RID: 8:1:70483:3
CleanCnt:1 Mode: X Flags: 0x2
2006-04-03 15:00:40.14 spid4 Grant List 1::
2006-04-03 15:00:40.14 spid4 Owner:0x2c2a8b60 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
2006-04-03 15:00:40.14 spid4 SPID: 58 ECID: 0 Statement Type:
UPDATE Line #: 38
2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
FeltexJob_Update;1
2006-04-03 15:00:40.14 spid4 Requested By:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
2006-04-03 15:00:40.14 spid4 Victim Resource Owner:
2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
CREATE PROCEDURE Job_Update(@.JobID int,@.JobName
varchar(128),@.JobDescription varchar(512),
@.JobUserName varchar(60),@.JobEmailType int,@.JobEmailTo varchar(1000),
@.JobEmailCC varchar(255),@.JobClassName varchar(60),@.JobParameters
varbinary(5500),
@.JobDLLName varchar(60),@.JobDLLPathName varchar(60),@.CategoryId int,
@.FrequencyType int,@.UpdateBy varchar(30),@.UpdateDate datetime,
@.RowTS varbinary(8) Output )
-- WITH ENCRYPTION
AS
--
-- Update table Job
-- Uses Optimistic locking via RowTS. New RowTS is returned in @.RowTS
-- BEGIN & Commit Transaction is done in proc. Can also be done in VB.
-- If Update fails it returns an error and does a RaisError (Will force
VB error)
--
BEGIN
Declare @.OldTs VarBinary(8)
DECLARE @.nRowCount INT
-- Begin the transaction
BEGIN TRANSACTION
-- Retrieve and check timestamp. Update lock held until Commit (or
Rollback)
SELECT @.OldTs = RowTS from Job WHERE JobID = @.JobID
SELECT @.nRowCount = @.@.ROWCOUNT
IF @.nRowCount = 0
BEGIN
RaisError 50302 'Update failed - Job record was deleted by another
user'
GOTO PROC_ROLLBACK
END
IF @.OldTs <> @.RowTS
BEGIN
RaisError 50303 'Update failed - Job record was updated by another
user'
GOTO PROC_ROLLBACK
END
UPDATE dbo.Job Set JobName = @.JobName,
JobDescription = @.JobDescription,
JobUserName = @.JobUserName,
JobEmailType = @.JobEmailType,
JobEmailTo = @.JobEmailTo,
JobEmailCC = @.JobEmailCC,
JobClassName = @.JobClassName,
JobParameters = @.JobParameters,
JobDLLName = @.JobDLLName,
JobDLLPathName = @.JobDLLPathName,
CategoryId = @.CategoryId,
FrequencyType = @.FrequencyType,
UpdateBy = @.UpdateBy,
UpdateDate = @.UpdateDate,
RowTS = Convert(VarBinary(8),CURRENT_TIMESTAMP,21)
WHERE JobID = @.JobID
AND RowTS = @.OldTs
SELECT @.nRowCount = @.@.ROWCOUNT
IF @.@.error <> 0
BEGIN
RaisError 50301 'Job Update Failed'
GOTO PROC_ROLLBACK
END
IF @.nRowCount = 0
BEGIN
RaisError 50303 'Update failed - Job record was updated by another
user'
GOTO PROC_ROLLBACK
END
-- Get the new timestamp
SELECT @.RowTS = RowTS FROM Job WHERE JobID = @.JobID
-- Commit the Transaction
COMMIT TRANSACTION
RETURN(0)
PROC_ROLLBACK:
-- Rollback on Error
ROLLBACK TRANSACTION
RETURN (-301)
END
GOchris,
Can you check if this table has a clustered index?
Can you check if this table has a nonclustered index by [JobID]?
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/kb/q169960/
AMB
"chris.nolan@.feltex.com" wrote:
> I have inherited a problem from the guy I have taken over from. Below
> is the log error message followed by the procedure in question. Anybody
> have any ideas why this is deadlocking?
>
> 2006-04-03 15:00:40.14 spid4 Node:1
> 2006-04-03 15:00:40.14 spid4 RID: 8:1:89142:1
> CleanCnt:1 Mode: X Flags: 0x2
> 2006-04-03 15:00:40.14 spid4 Grant List 3::
> 2006-04-03 15:00:40.14 spid4 Owner:0x3571b2c0 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:63 ECID:0
> 2006-04-03 15:00:40.14 spid4 SPID: 63 ECID: 0 Statement Type:
> SELECT Line #: 71
> 2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
> FeltexJob_Update;1
> 2006-04-03 15:00:40.14 spid4 Requested By:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
> U SPID:58 ECID:0 Ec:(0x53BF3570) Value:0x2c2a82a0 Cost:(0/B4)
> 2006-04-03 15:00:40.14 spid4
> 2006-04-03 15:00:40.14 spid4 Node:2
> 2006-04-03 15:00:40.14 spid4 RID: 8:1:70483:3
> CleanCnt:1 Mode: X Flags: 0x2
> 2006-04-03 15:00:40.14 spid4 Grant List 1::
> 2006-04-03 15:00:40.14 spid4 Owner:0x2c2a8b60 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
> 2006-04-03 15:00:40.14 spid4 SPID: 58 ECID: 0 Statement Type:
> UPDATE Line #: 38
> 2006-04-03 15:00:40.14 spid4 Input Buf: RPC Event:
> FeltexJob_Update;1
> 2006-04-03 15:00:40.14 spid4 Requested By:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
> 2006-04-03 15:00:40.14 spid4 Victim Resource Owner:
> 2006-04-03 15:00:40.14 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:63 ECID:0 Ec:(0x76F51570) Value:0x78fb0520 Cost:(0/B4)
>
> CREATE PROCEDURE Job_Update(@.JobID int,@.JobName
> varchar(128),@.JobDescription varchar(512),
> @.JobUserName varchar(60),@.JobEmailType int,@.JobEmailTo varchar(1000),
> @.JobEmailCC varchar(255),@.JobClassName varchar(60),@.JobParameters
> varbinary(5500),
> @.JobDLLName varchar(60),@.JobDLLPathName varchar(60),@.CategoryId int,
> @.FrequencyType int,@.UpdateBy varchar(30),@.UpdateDate datetime,
> @.RowTS varbinary(8) Output )
> -- WITH ENCRYPTION
> AS
> --
> -- Update table Job
> -- Uses Optimistic locking via RowTS. New RowTS is returned in @.RowTS
> -- BEGIN & Commit Transaction is done in proc. Can also be done in VB.
> -- If Update fails it returns an error and does a RaisError (Will force
> VB error)
> --
> BEGIN
> Declare @.OldTs VarBinary(8)
> DECLARE @.nRowCount INT
> -- Begin the transaction
> BEGIN TRANSACTION
> -- Retrieve and check timestamp. Update lock held until Commit (or
> Rollback)
> SELECT @.OldTs = RowTS from Job WHERE JobID = @.JobID
> SELECT @.nRowCount = @.@.ROWCOUNT
> IF @.nRowCount = 0
> BEGIN
> RaisError 50302 'Update failed - Job record was deleted by another
> user'
> GOTO PROC_ROLLBACK
> END
> IF @.OldTs <> @.RowTS
> BEGIN
> RaisError 50303 'Update failed - Job record was updated by another
> user'
> GOTO PROC_ROLLBACK
> END
> UPDATE dbo.Job Set JobName = @.JobName,
> JobDescription = @.JobDescription,
> JobUserName = @.JobUserName,
> JobEmailType = @.JobEmailType,
> JobEmailTo = @.JobEmailTo,
> JobEmailCC = @.JobEmailCC,
> JobClassName = @.JobClassName,
> JobParameters = @.JobParameters,
> JobDLLName = @.JobDLLName,
> JobDLLPathName = @.JobDLLPathName,
> CategoryId = @.CategoryId,
> FrequencyType = @.FrequencyType,
> UpdateBy = @.UpdateBy,
> UpdateDate = @.UpdateDate,
> RowTS = Convert(VarBinary(8),CURRENT_TIMESTAMP,21)
> WHERE JobID = @.JobID
> AND RowTS = @.OldTs
> SELECT @.nRowCount = @.@.ROWCOUNT
> IF @.@.error <> 0
> BEGIN
> RaisError 50301 'Job Update Failed'
> GOTO PROC_ROLLBACK
> END
> IF @.nRowCount = 0
> BEGIN
> RaisError 50303 'Update failed - Job record was updated by another
> user'
> GOTO PROC_ROLLBACK
> END
> -- Get the new timestamp
> SELECT @.RowTS = RowTS FROM Job WHERE JobID = @.JobID
> -- Commit the Transaction
> COMMIT TRANSACTION
> RETURN(0)
> PROC_ROLLBACK:
> -- Rollback on Error
> ROLLBACK TRANSACTION
> RETURN (-301)
> END
> GO
>|||Good question. I should have mentioned that. It doesn't have any
indexes at all.|||Does anybody have any suggestions please?

Wednesday, March 21, 2012

Deadlock on SQL SELECT statement

I have inherited the maintenance of a product which includes the snipet
of code below. Every 10 seconds the code is executed. It is causing a
deadlock in some instances, but I am undable to reproduce the problem
on my machine. The "PC" table contains a list of PCs seen on a
network, so isn't very large. Since I dont have much background in
database programming, I was wondering if there is some simple answer to
the deadlock issue...but from reading on deadlocks, there rarely seems
to be a simple solution.
// ****************************************
// Find PCs to restart
CString strQuery;
strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
_RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
PrepareSQLDate(CTime::GetCurrentTime()))
;
try
{
for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
{
list.Add(rs.GetColInt(0));
}
rs.Close();
}
catch (CDBException * e)
{
HandleException (e, strQuery);
}
return list.GetCount();
// ****************************************
**
Thanks in advance.In message <1138983056.041276.84650@.g47g2000cwa.googlegroups.com>,
bigcoops@.hotmail.com writes
>network, so isn't very large. Since I dont have much background in
>database programming, I was wondering if there is some simple answer to
>the deadlock issue...but from reading on deadlocks, there rarely seems
>to be a simple solution.
You may want to give Thread Validator a whirl.
http://www.softwareverify.com
Stephen
--
Stephen Kellett
Object Media Limited http://www.objmedia.demon.co.uk/software.html
Computer Consultancy, Software Development
Windows C++, Java, Assembler, Performance Analysis, Troubleshooting|||Try this:
select _ID from PC WITH (NOLOCK) ... and so forth
HTH,
Tom Dacon
Dacon Software Consulting
<bigcoops@.hotmail.com> wrote in message
news:1138983056.041276.84650@.g47g2000cwa.googlegroups.com...
>I have inherited the maintenance of a product which includes the snipet
> of code below. Every 10 seconds the code is executed. It is causing a
> deadlock in some instances, but I am undable to reproduce the problem
> on my machine. The "PC" table contains a list of PCs seen on a
> network, so isn't very large. Since I dont have much background in
> database programming, I was wondering if there is some simple answer to
> the deadlock issue...but from reading on deadlocks, there rarely seems
> to be a simple solution.
> // ****************************************
> // Find PCs to restart
> CString strQuery;
> strQuery.Format ("select _ID from PC where (_FLAGS & 4) > 0 and
> _RESTART > %s and _RESTART <= %s", PrepareSQLDate((CTime)0),
> PrepareSQLDate(CTime::GetCurrentTime()))
;
> try
> {
> for (CRecordSet rs(this, strQuery); !rs.IsEOF() ; rs.MoveNext())
> {
> list.Add(rs.GetColInt(0));
> }
> rs.Close();
> }
> catch (CDBException * e)
> {
> HandleException (e, strQuery);
> }
> return list.GetCount();
> // ****************************************
**
> Thanks in advance.
>|||Doesn't NOLOCK have the potential of getting dirty data?
Since the 10 second timer is set after the code above is executed, is
it possible the CRecordSet::Close() method did not close properly and
is holding a lock on the table? So when the next timer goes off the
deadlock occurs.
Thanks,
bigcoops|||It appears that this is not the place where deadlocks are occurring.
There is another SELECT statement, "select _NAME from PC where _ID =
....", and I suspect all other statements accessing the PC table will
cause a deadlock. Has anyone seen a similar issue where access to a
table will cause a deadlock?|||In addition to the deadlocks, there are now "Timeout expired (S1T00)"
errors occuring, which is more than likely a releated issue.

Monday, March 19, 2012

Deadlock Issue - Cannot pinpoint resource causing contention

All,
Have a deadlock issue - see 1204 trace info below.
After reviewing BOL I cannot ascertain the exact resource (table, page,
index, etc) that is causing the deadlock. I believe it might be a row in a
table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
and I cannot determine the exact table.
Question - is there something in the trace data that can tell me the exact
resource down to the specific table - assuming it is a table.
Starting deadlock search 75296
Target Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec

0xB611B540)
Value:0x2e7feac0
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec

0xB611B540) Value:0x2e7feac0
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec

0x2ED0F590) Value:0x2e893d60
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec

0xB611B540) Value:0x2e7feac0
Deadlock cycle was encountered ... verifying cycle
Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec

0xB611B540) Value:0x2e7feac0 Cost

0/80)
Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
Ec

0x2ED0F590) Value:0x2e893d60 Cost

0/80)
Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
Ec

0xB611B540) Value:0x2e7feac0 Cost

0/80)
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
ECID:0
SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec

0xB611B540)
Value:0x2e7feac0 Cost

0/80)
Node:2
RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
ECID:0
SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec

0x2ED0F590)
Value:0x2e893d60 Cost

0/80)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec

0x2ED0F590)
Value:0x2e893d60 Cost

0/80)
End deadlock search 75296 ... a deadlock was found.
Hi
Have you seen
http://msdn.microsoft.com/library/de...abse_5xrn.asp?
I prefer to use sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
You are having problems with tempdb, so you may want to cut down the usage
as detailed in
parts of
http://msdn.microsoft.com/library/de...netchapt14.asp
Also you may want to implement the suggestions in
http://support.microsoft.com/default...b;en-us;328551
John
"John" <John@.discussions.microsoft.com> wrote in message
news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in
> a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about
> this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec

0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec

0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec

0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec

0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec

0xB611B540) Value:0x2e7feac0 Cost

0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec

0x2ED0F590) Value:0x2e893d60 Cost

0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec

0xB611B540) Value:0x2e7feac0 Cost

0/80)
>
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> RID: 2:1:81:0 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 0::
> Owner:0x2e83b8a0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54
> ECID:0
> SPID: 54 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: dbo.p_ins_equipment_circuit_ref_info;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec

0xB611B540)
> Value:0x2e7feac0 Cost

0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000
> SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec

0x2ED0F590)
> Value:0x2e893d60 Cost

0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec

0x2ED0F590)
> Value:0x2e893d60 Cost

0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>
|||Well - I think I answered my own question after seeing other similar threads.
Using dbcc page it appears the deadlock resource is in tempdb database. Fun.
"John" wrote:

> All,
> Have a deadlock issue - see 1204 trace info below.
> After reviewing BOL I cannot ascertain the exact resource (table, page,
> index, etc) that is causing the deadlock. I believe it might be a row in a
> table (ie - RID: 2:1:81:0 and RID: 2:1:81:1) but I am suspicious about this
> and I cannot determine the exact table.
> Question - is there something in the trace data that can tell me the exact
> resource down to the specific table - assuming it is a table.
> Starting deadlock search 75296
> Target Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0 Ec

0xB611B540)
> Value:0x2e7feac0
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec

0xB611B540) Value:0x2e7feac0
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec

0x2ED0F590) Value:0x2e893d60
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec

0xB611B540) Value:0x2e7feac0
>
> Deadlock cycle was encountered ... verifying cycle
> Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec

0xB611B540) Value:0x2e7feac0 Cost

0/80)
> Node:2 ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0
> Ec

0x2ED0F590) Value:0x2e893d60 Cost

0/80)
> Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:185 ECID:0
> Ec

0xB611B540) Value:0x2e7feac0 Cost

0xB611B540)
> Value:0x2e7feac0 Cost

0/80)
> Node:2
> RID: 2:1:81:1 CleanCnt:1 Mode: X Flags: 0x2
> Grant List 1::
> Owner:0x2e7feaa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:185
> ECID:0
> SPID: 185 ECID: 0 Statement Type: DELETE Line #: 18
> Input Buf: RPC Event: sp_executesql;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec

0x2ED0F590)
> Value:0x2e893d60 Cost

0/80)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec

0x2ED0F590)
> Value:0x2e893d60 Cost

0/80)
> End deadlock search 75296 ... a deadlock was found.
> --
>
|||John,
Yes, Yes, Yes, No.
I will probably be reviewing and implementing 328551. Thanks for the heads
up.
"John Bell" wrote:

> Hi
> Have you seen
> http://msdn.microsoft.com/library/de...abse_5xrn.asp?
> I prefer to use sp_blocker_pss80
> http://support.microsoft.com/kb/271509/EN-US/
> You are having problems with tempdb, so you may want to cut down the usage
> as detailed in
> parts of
> http://msdn.microsoft.com/library/de...netchapt14.asp
> Also you may want to implement the suggestions in
> http://support.microsoft.com/default...b;en-us;328551
> John
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:49B3981C-DDB8-4B37-9606-00F2F6E32504@.microsoft.com...
>
>

Thursday, March 29, 2012

Deadlocks Information.

Deadlocks increasing

Deadlocks increasing

Deadlocks increasing

Tuesday, March 27, 2012

deadlocks

Deadlocked on the same resource (same index)

Sunday, March 25, 2012

deadlock victim even using temp table

deadlock victim

Thursday, March 22, 2012

deadlock Question

deadlock Question

deadlock Question

deadlock problem

Wednesday, March 21, 2012

Deadlock on SQL SELECT statement

Monday, March 19, 2012

Deadlock Issue - Cannot pinpoint resource causing contention

deAttach database

Blog Archive

About Me