Deadlocks?

hey can i get idea about deadlocks?
how to avoide deadlock actually?
what are the different stretegies used for this technique?> Continue with latest book by Kalen Delaney.
Which one is this Dejan? Are you refering to "Inside SQL Server" ?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> Start with Books OnLine, searc for deadlock, there are quite a few topics.
> Continue with latest book by Kalen Delaney.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > hey can i get idea about deadlocks?
> > how to avoide deadlock actually?
> > what are the different stretegies used for this technique?
>|||Kim's book was very good on locking in general, but pretty sparse when it
came to dealing with deadlocks.
Just my opinion.
Bob Castleman
SuccessWare Software
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> Start with Books OnLine, searc for deadlock, there are quite a few topics.
> Continue with latest book by Kalen Delaney.
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > hey can i get idea about deadlocks?
> > how to avoide deadlock actually?
> > what are the different stretegies used for this technique?
>|||I should be more precise. It is "Hands-On SQL Server 2000 : Troubleshooting
Locking and Blocking" ebook, available at
http://www.shareit.com/product.html?cart=1&productid=183645&affiliateid=&languageid=1&cookies=1&backlink=http://www.netimpress.com/Default.asp?¤cies=USD.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Vinodk" <vinodk_sct@.hotmail.com> wrote in message
news:OKWfVIWbDHA.3768@.tk2msftngp13.phx.gbl...
> > Continue with latest book by Kalen Delaney.
> Which one is this Dejan? Are you refering to "Inside SQL Server" ?
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> http://www.extremeexperts.com
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > Start with Books OnLine, searc for deadlock, there are quite a few
topics.
> > Continue with latest book by Kalen Delaney.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> > news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > > hey can i get idea about deadlocks?
> > > how to avoide deadlock actually?
> > > what are the different stretegies used for this technique?
> >
> >
>|||Vinod,
I guess Dejan is referring to..
Hands-On SQL Server 2000 : Troubleshooting Locking and Blocking (ebook)
By Kalen Delaney
http://www.shareit.com/product.html?cart=1&productid=183645&affiliateid=&languageid=1&cookies=1&backlink=http://www.netimpress.com/Default.asp?¤cies=USD
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Vinodk" <vinodk_sct@.hotmail.com> wrote in message
news:OKWfVIWbDHA.3768@.tk2msftngp13.phx.gbl...
> > Continue with latest book by Kalen Delaney.
> Which one is this Dejan? Are you refering to "Inside SQL Server" ?
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> http://www.extremeexperts.com
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > Start with Books OnLine, searc for deadlock, there are quite a few
topics.
> > Continue with latest book by Kalen Delaney.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> > news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > > hey can i get idea about deadlocks?
> > > how to avoide deadlock actually?
> > > what are the different stretegies used for this technique?
> >
> >
>|||First of all I haven't read everything, for example I haven't read the last
book of Kalen.
But most documentation I have seen describe deadlocks which can be expected,
I haven't seen (or missed them) descriptions of deadlocks that are totally
unexpected.
We had a deadlock between one transaction and a single select statement not
run in a transaction. The statement selected only one row of one table, but
still deadlocked with the transaction. From a select you can not see which
locks are used during the transaction, because it is so fast. Trapping the
deadlock and seeing which locks where used was difficult because when the
deadlock is detected by sql-server the information (read locks) disappears.
Because we did not suspect the 'select' to cause any problems it
took a long time before we could determine the cause of the deadlock
and solve the problem. (The deadlock was difficult to produce to begin
with).
In the end, with the database we had then, I could recreate the deadlock at
will within the QA. Outside that database even with the same data I could
not recreate the problem.
So do not exclude simple select statements when hunting down the culprits of
deadlocks.
Ben Brugman
"hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> hey can i get idea about deadlocks?
> how to avoide deadlock actually?
> what are the different stretegies used for this technique?|||Thankx Dinesh and Dejan ... Looks an good book to have ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eBLD0fWbDHA.2960@.tk2msftngp13.phx.gbl...
> Vinod,
> I guess Dejan is referring to..
> Hands-On SQL Server 2000 : Troubleshooting Locking and Blocking (ebook)
> By Kalen Delaney
>
http://www.shareit.com/product.html?cart=1&productid=183645&affiliateid=&languageid=1&cookies=1&backlink=http://www.netimpress.com/Default.asp?¤cies=USD
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Vinodk" <vinodk_sct@.hotmail.com> wrote in message
> news:OKWfVIWbDHA.3768@.tk2msftngp13.phx.gbl...
> > > Continue with latest book by Kalen Delaney.
> >
> > Which one is this Dejan? Are you refering to "Inside SQL Server" ?
> >
> > --
> > HTH,
> > Vinod Kumar
> > MCSE, DBA, MCAD
> > http://www.extremeexperts.com
> >
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:ugi5BmVbDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > Start with Books OnLine, searc for deadlock, there are quite a few
> topics.
> > > Continue with latest book by Kalen Delaney.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > > Please reply only to the newsgroups.
> > > PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > > "hrishikesh musale" <musaleh@.mahindrabt.com> wrote in message
> > > news:0a5c01c36d58$8a43e1c0$a501280a@.phx.gbl...
> > > > hey can i get idea about deadlocks?
> > > > how to avoide deadlock actually?
> > > > what are the different stretegies used for this technique?
> > >
> > >
> >
> >
>sql

Deadlocks, why?

We have a problem with a table giving us deadlock issues and we can't
figure out why.

It's a table we write to fairly often perhaps 50 times a minute. And
also do a select of 200 rows at a time from 4 servers every 5 minutes or so.

We are only keeping 48 hours worth of rows in the table which averages
at 30000 a day on a busy day.

This table has 1 PK and 2 FKs plus one TEXT column which does not
participate in the WHERE clause.

We are using binded variables.

We have applied the latest patch to SQL2003 server running on
Windows2003. The patch is supposed to resolve deadlock issues.

Anyone have any advice on how to alleviate this problem.

ThanksDon Vaillancourt (donv@.webimpact.com) writes:
> We have a problem with a table giving us deadlock issues and we can't
> figure out why.
> It's a table we write to fairly often perhaps 50 times a minute. And
> also do a select of 200 rows at a time from 4 servers every 5 minutes or
> so.
> We are only keeping 48 hours worth of rows in the table which averages
> at 30000 a day on a busy day.
> This table has 1 PK and 2 FKs plus one TEXT column which does not
> participate in the WHERE clause.
> We are using binded variables.
> We have applied the latest patch to SQL2003 server running on
> Windows2003. The patch is supposed to resolve deadlock issues.
> Anyone have any advice on how to alleviate this problem.

I'm afraid that there is not enough information your post to make it
possible to give solutions.

Except one: if it is acceptable that one of the process is always
is the victim, make this process emit SET DEADLOCK_PRIORITY LOW.
We have done this in quite a few places in our system. Background
processes don't scream so much about deadlocks as users do.

But if that is not an option, I can only suggest methods to get more
information.

First, have you enabled deadlock trace on your server and looked at
the output? To enable deadlock trace, use Enterprise Manager to add
these two startup options: -T 1204 -T 3605.

Once you have the deadlock output, try to narrow down exactly which
queries that collide. Once you have the queries, you could post them
together with the table definitions (including indexes!). Or you could
post the deadlock traces (which is not very easy to interpret).

--
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|||Oh, I know which queries are involved and which ones are usually the
victims.

But thanks for the trace idea.

We haven't been able to replicate the deadlock issue in-house ass of
yet, but I will certainly keep those options in mind and use them.

Thank you

Erland Sommarskog wrote:
> Don Vaillancourt (donv@.webimpact.com) writes:
>> We have a problem with a table giving us deadlock issues and we can't
>> figure out why.
>>
>> It's a table we write to fairly often perhaps 50 times a minute. And
>> also do a select of 200 rows at a time from 4 servers every 5 minutes or
>> so.
>>
>> We are only keeping 48 hours worth of rows in the table which averages
>> at 30000 a day on a busy day.
>>
>> This table has 1 PK and 2 FKs plus one TEXT column which does not
>> participate in the WHERE clause.
>>
>> We are using binded variables.
>>
>> We have applied the latest patch to SQL2003 server running on
>> Windows2003. The patch is supposed to resolve deadlock issues.
>>
>> Anyone have any advice on how to alleviate this problem.
> I'm afraid that there is not enough information your post to make it
> possible to give solutions.
> Except one: if it is acceptable that one of the process is always
> is the victim, make this process emit SET DEADLOCK_PRIORITY LOW.
> We have done this in quite a few places in our system. Background
> processes don't scream so much about deadlocks as users do.
> But if that is not an option, I can only suggest methods to get more
> information.
> First, have you enabled deadlock trace on your server and looked at
> the output? To enable deadlock trace, use Enterprise Manager to add
> these two startup options: -T 1204 -T 3605.
> Once you have the deadlock output, try to narrow down exactly which
> queries that collide. Once you have the queries, you could post them
> together with the table definitions (including indexes!). Or you could
> post the deadlock traces (which is not very easy to interpret).|||Don Vaillancourt (donv@.webimpact.com) writes:
> Oh, I know which queries are involved and which ones are usually the
> victims.

OK. With table definitions and indexes and the queries, it's possible
that we can spot some potential problems. Without them it's going to
be hard. :-)

--
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|||Don Vaillancourt wrote:
> Oh, I know which queries are involved and which ones are usually the
> victims.
> But thanks for the trace idea.
> We haven't been able to replicate the deadlock issue in-house ass of
> yet, but I will certainly keep those options in mind and use them.

Oh - you meant -that- kind of deadlock. Try some dried plums <g>.|||Hi Don

It could be that you will never be able to replicate the deadlock if your
hardware/environment is exactly the same. If you have not run
sp_blocker_pss80 you may want to try it
http://support.microsoft.com/defaul...kb;en-us;271509

John

"Don Vaillancourt" <donv@.webimpact.com> wrote in message
news:bWVxf.9204$43.7861@.nnrp.ca.mci.com!nnrp1.uune t.ca...
> Oh, I know which queries are involved and which ones are usually the
> victims.
> But thanks for the trace idea.
> We haven't been able to replicate the deadlock issue in-house ass of yet,
> but I will certainly keep those options in mind and use them.
> Thank you
>
> Erland Sommarskog wrote:
>> Don Vaillancourt (donv@.webimpact.com) writes:
>>> We have a problem with a table giving us deadlock issues and we can't
>>> figure out why.
>>>
>>> It's a table we write to fairly often perhaps 50 times a minute. And
>>> also do a select of 200 rows at a time from 4 servers every 5 minutes or
>>> so.
>>> We are only keeping 48 hours worth of rows in the table which averages
>>> at 30000 a day on a busy day.
>>>
>>> This table has 1 PK and 2 FKs plus one TEXT column which does not
>>> participate in the WHERE clause.
>>>
>>> We are using binded variables.
>>>
>>> We have applied the latest patch to SQL2003 server running on
>>> Windows2003. The patch is supposed to resolve deadlock issues.
>>>
>>> Anyone have any advice on how to alleviate this problem.
>>
>> I'm afraid that there is not enough information your post to make it
>> possible to give solutions.
>>
>> Except one: if it is acceptable that one of the process is always
>> is the victim, make this process emit SET DEADLOCK_PRIORITY LOW.
>> We have done this in quite a few places in our system. Background
>> processes don't scream so much about deadlocks as users do.
>>
>> But if that is not an option, I can only suggest methods to get more
>> information.
>>
>> First, have you enabled deadlock trace on your server and looked at
>> the output? To enable deadlock trace, use Enterprise Manager to add
>> these two startup options: -T 1204 -T 3605.
>>
>> Once you have the deadlock output, try to narrow down exactly which
>> queries that collide. Once you have the queries, you could post them
>> together with the table definitions (including indexes!). Or you could
>> post the deadlock traces (which is not very easy to interpret).
>

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

deadlocks not resolved by SQL Server

I am under the impression that SQL Server would automatically detect a
deadlock and roll back a participating transaction. However, I have had a
few cases where automatic resolution did not occur. Instead, I had to go in
and manually kill the transaction that is causing a deadlock.
Is there anyway to avoid this? I don't want to have to manually kill a
transaction to unwind a deadlock if that's at all possible.
Thanks in advanceHi
It sounds like you have prolonged blocking rather than a deadlock, in which
case your query should timeout. Make sure that your application has
overridden the default timeouts and requested to wait indefinitely.
You should also investigate if you application has long running transactions
that have not been correctly committed/rolled back or if poor indexing is
affecting performance.
John
"C.W." wrote:
> I am under the impression that SQL Server would automatically detect a
> deadlock and roll back a participating transaction. However, I have had a
> few cases where automatic resolution did not occur. Instead, I had to go in
> and manually kill the transaction that is causing a deadlock.
> Is there anyway to avoid this? I don't want to have to manually kill a
> transaction to unwind a deadlock if that's at all possible.
> Thanks in advance
>
>

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 Ec0x576E3580)
Value:0x6847aac0 Cost0/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 Ec0x78181580)
Value:0x57f04c80 Cost0/1ED4)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec0x576E3580)
Value:0x6847aac0 Cost0/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 Ec0x576E3580)
> Value:0x6847aac0 Cost0/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 Ec0x78181580)
> Value:0x57f04c80 Cost0/1ED4)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec0x576E3580)
> Value:0x6847aac0 Cost0/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 Ec0x576E3580)
> Value:0x6847aac0 Cost0/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 Ec0x78181580)
> Value:0x57f04c80 Cost0/1ED4)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec0x576E3580)
> Value:0x6847aac0 Cost0/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...
>|||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 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 Ec0x576E3580)
Value:0x6847aac0 Cost0/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 Ec0x78181580)
Value:0x57f04c80 Cost0/1ED4)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec0x576E3580)
Value:0x6847aac0 Cost0/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 Ec0x576E3580)
> Value:0x6847aac0 Cost0/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 Ec0x78181580)
> Value:0x57f04c80 Cost0/1ED4)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec0x576E3580)
> Value:0x6847aac0 Cost0/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 Ec0x576E3580)
> Value:0x6847aac0 Cost0/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 Ec0x78181580)
> Value:0x57f04c80 Cost0/1ED4)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec0x576E3580)
> Value:0x6847aac0 Cost0/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...
>
|||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 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 in error log

In the past when a deadlock occurred, it was written into
the SQL Server error logs. I thought this was SQL Server
wide. However, now I'm in a new company and when I created
a deadlock, it wasn't written into the SQL Error logs.
Am I missing a setting somewhere?
Please advise
Thanks
FredHi,
This level of error logging is not by default. Check BOL
on DBCC TRACEON.
Brig
>--Original Message--
>In the past when a deadlock occurred, it was written
into
>the SQL Server error logs. I thought this was SQL Server
>wide. However, now I'm in a new company and when I
created
>a deadlock, it wasn't written into the SQL Error logs.
>Am I missing a setting somewhere?
>Please advise
>Thanks
>Fred
>.
>

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 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:

> 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:
>

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
>>

Deadlocks and multiple Indexed VIEWs on a table.

(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I can get help with.
I have a Table A that has a trigger to update Table B. There are about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, maybe they're getting
updated in a different *order* for different sessions? I would have assumed that it'd
always update in the same order, but I could see where maybe SQL Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas and
opinions!
Thanks! :-)
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_02AA_01C37229.4A089D50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are about =6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, maybe =they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL Server =says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and Session =2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any further =ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_02AA_01C37229.4A089D50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. SUM() =or BIG_COUNT().
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I have a =Table A that has a trigger to update Table B. There are about 6 Indexed VIEWs(some of which are pretty "heavy") that use Table B. When =I have multiple sessions try toinsert into Table A, I'm getting consistent deadlocks.I'm postulating that perhaps, when the Indexed VIEWs =get updated, maybe they're gettingupdated in a different *order* for =different sessions? I would have assumed that it'dalways update in the =same order, but I could see where maybe SQL Server says, ="Oops...thisindex is busy, so I'll go ahead and update this other one first." And then =we'd have theclassic deadlock conditions of Session 1 requesting X and Y, =and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas andopinions!Thanks! :-)John Peterson

---

--=_NextPart_000_02AA_01C37229.4A089D50--|||This is a multi-part message in MIME format.
--=_NextPart_000_008F_01C37211.C231F410
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_008F_01C37211.C231F410
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks, Tom! Would lock escalation contribute =to the propensity for a deadlock? I'm using the Profiler, and I see the =Deadlocks -- but I didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using =aggregation -- they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can =provide!
John Peterson
"Tom Moreau" = wrote in message news:OiCuHrkcDHA.2960=@.tk2msftngp13.phx.gbl...
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. =SUM() or BIG_COUNT().
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I have =a Table A that has a trigger to update Table B. There are about 6 =Indexed VIEWs(some of which are pretty "heavy") that use Table B. =When I have multiple sessions try toinsert into Table A, I'm getting =consistent deadlocks.I'm postulating that perhaps, when the Indexed VIEWs =get updated, maybe they're gettingupdated in a different *order* for =different sessions? I would have assumed that it'dalways update in the =same order, but I could see where maybe SQL Server says, ="Oops...thisindex is busy, so I'll go ahead and update this other one first." And =then we'd have theclassic deadlock conditions of Session 1 requesting X and =Y, and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas andopinions!Thanks! :-)John Peterson

---

--=_NextPart_000_008F_01C37211.C231F410--|||This is a multi-part message in MIME format.
--=_NextPart_000_02DF_01C3722C.17629510
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Lock escalation converts a row or page lock to a table lock. If you =have an exclusive table lock (as in a monstrous INSERT or UPDATE), =that's going to block everything else from accessing the table. Now, if =you have a number of indexed views that access table B, then access to =those views is also blocked. The longer a lock is held, the greater the =chance for a deadlock.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:#UTgyxkcDHA.3620@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_02DF_01C3722C.17629510
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Lock escalation converts a row or page =lock to a table lock. If you have an exclusive table lock (as in a monstrous INSERT or UPDATE), that's going to block everything else =from accessing the table. Now, if you have a number of indexed views =that access table B, then access to those views is also blocked. The =longer a lock is held, the greater the chance for a deadlock.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:#UTgyxkcDHA.3620=@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute =to the propensity for a deadlock? I'm using the Profiler, and I see the =Deadlocks -- but I didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using =aggregation -- they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can =provide!
John Peterson
"Tom Moreau" = wrote in message news:OiCuHrkcDHA.2960=@.tk2msftngp13.phx.gbl...
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. =SUM() or BIG_COUNT().
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I have =a Table A that has a trigger to update Table B. There are about 6 =Indexed VIEWs(some of which are pretty "heavy") that use Table B. =When I have multiple sessions try toinsert into Table A, I'm getting =consistent deadlocks.I'm postulating that perhaps, when the Indexed VIEWs =get updated, maybe they're gettingupdated in a different *order* for =different sessions? I would have assumed that it'dalways update in the =same order, but I could see where maybe SQL Server says, ="Oops...thisindex is busy, so I'll go ahead and update this other one first." And =then we'd have theclassic deadlock conditions of Session 1 requesting X and =Y, and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas andopinions!Thanks! :-)John Peterson

---

--=_NextPart_000_02DF_01C3722C.17629510--|||This is a multi-part message in MIME format.
--=_NextPart_000_00BB_01C37219.8C7BD360
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks again, Tom!
Yeah -- I don't think we have large INSERTs/UPDATEs, but rather just =row-at-a-time. It's puzzling to me why we're seeing these deadlocks, =but we are. Even if we have a bunch of processes just INSERT into this =Table A and have all the Indexed VIEWs get updated we get deadlocked. =I'd think that this process would always have the same "flow" and =potentially avoid deadlocks because there isn't much processing going on =with the exception of the Indexed VIEW updates that are "behind the =scenes".
Do you have any recommendations beyond looking for Escalating Locks that =I should examine? And, if we see Escalation, do you have =tips/techniques for what I can do about that?
Thanks!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23at1k2kcDHA.1204@.TK2MSFTNGP12.phx.gbl...
Lock escalation converts a row or page lock to a table lock. If you =have an exclusive table lock (as in a monstrous INSERT or UPDATE), =that's going to block everything else from accessing the table. Now, if =you have a number of indexed views that access table B, then access to =those views is also blocked. The longer a lock is held, the greater the =chance for a deadlock.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:#UTgyxkcDHA.3620@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case =you can detect this through the Profiler. It's also possible that you =are using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful =I can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." =And then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_00BB_01C37219.8C7BD360
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks again, Tom!
Yeah -- I don't think we have large INSERTs/UPDATEs, =but rather just row-at-a-time. It's puzzling to me why we're seeing =these deadlocks, but we are. Even if we have a bunch of processes just =INSERT into this Table A and have all the Indexed VIEWs get updated we get deadlocked. I'd think that this process would always have the same ="flow" and potentially avoid deadlocks because there isn't much processing =going on with the exception of the Indexed VIEW updates that are "behind the scenes".
Do you have any recommendations beyond looking for =Escalating Locks that I should examine? And, if we see Escalation, do you =have tips/techniques for what I can do about that?
Thanks!
John Peterson
"Tom Moreau" = wrote in message news:%23at1k2kcDHA.=1204@.TK2MSFTNGP12.phx.gbl...
Lock escalation converts a row or =page lock to a table lock. If you have an exclusive table lock (as in a monstrous INSERT or UPDATE), that's going to block everything =else from accessing the table. Now, if you have a number of indexed views =that access table B, then access to those views is also blocked. The =longer a lock is held, the greater the chance for a deadlock.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:#UTgyxkcDHA.3620=@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation =contribute to the propensity for a deadlock? I'm using the Profiler, and I see the = Deadlocks -- but I didn't include the Lock:Escalation =Event.

I don't think any of those Indexed VIEWs are using = aggregation -- they're just pulling a lot of data from a lot of =disparate tables.

Thanks again for any help you can =provide!

John Peterson

"Tom Moreau" = wrote in message news:OiCuHrkcDHA.2960=@.tk2msftngp13.phx.gbl...
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - =e.g. SUM() or BIG_COUNT().
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I =have a Table A that has a trigger to update Table B. There are about =6 Indexed VIEWs(some of which are pretty "heavy") that use Table =B. When I have multiple sessions try toinsert into Table A, I'm =getting consistent deadlocks.I'm postulating that perhaps, when the =Indexed VIEWs get updated, maybe they're gettingupdated in a different =*order* for different sessions? I would have assumed that =it'dalways update in the same order, but I could see where maybe SQL Server =says, "Oops...thisindex is busy, so I'll go ahead and update this =other one first." And then we'd have theclassic deadlock conditions =of Session 1 requesting X and Y, and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to =solicit any further ideas andopinions!Thanks! =:-)John Peterson

---

--=_NextPart_000_00BB_01C37219.8C7BD360--

Deadlocks & Transaction Isolation Level

If I understand correctly, when using a Read Committed isolation level, the
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.
> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.
|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID =
> 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>

Deadlocks & Transaction Isolation Level

If I understand correctly, when using a Read Committed isolation level, the
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID => 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>

Deadlocks & Transaction Isolation Level

If I understand correctly, when using a Read Committed isolation level, the
most common reason for a deadlock is because two processes update a set of
tables in different order. However, it seems that when using a Repeatable
Read isolation level, the odds of a deadlock increase significantly.
For example, open Management Studio and create two different connections
against the AdventureWorks database. In both connections execute the
following:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Begin Tran
SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
Then, in the first connection execute the following (but do not commit the
transaction):
Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID = 1
In the second connection execute the exact same line. This will cause a
deadlock error in the second connection. We get a deadlock even though both
processes are performing the exact same action in the exact same order.
This example may not be the best but is my assumption correct that when
using Repeatable Read, the likelihood of a deadlock error is greater than
when using Read Committed?
Thanks, Amos.> when using a Read Committed isolation level, the
> most common reason for a deadlock is because two processes
> update a set of tables in different order.
not exactly. 2 processses may update rows in only one table and still
clinch in a deadlock.|||Hi Amos
Yes, your understanding is correct. Using a higher isolation level like
repeatable read has tradeoffs.
In read committed the locks on the SELECT would be released as soon as the
SELECT was finished. In repeatable read, the SELECT (shared) locks are not
released. The good news is that each transaction is guaranteed to read the
same data throughout the transaction. The bad news is there is a greater
chance of deadlock. Each connection has a shared lock on the row in the
Employee table, and wants an exclusive lock. Neither can get the exclusive
lock because the other has the shared lock, so you have deadlock.
One of the first suggestions we give to try to reduce deadlock is to reduce
your isolation level; in this case, bring it back to read committed.
Another solution here would be to use an UPDLOCK hint when you do the
select. Then the first process would get an update lock, not a shared lock,
and when the second process tried to get the update lock, it would be
blocked. The first process could then get the exclusive lock and do the
update operation, and finish the transaction. Then the second process could
get first the update lock, then the exclusive lock, and then finish, with no
deadlock occurring.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:%23$22cVEKGHA.3960@.TK2MSFTNGP09.phx.gbl...
> If I understand correctly, when using a Read Committed isolation level,
> the most common reason for a deadlock is because two processes update a
> set of tables in different order. However, it seems that when using a
> Repeatable Read isolation level, the odds of a deadlock increase
> significantly.
> For example, open Management Studio and create two different connections
> against the AdventureWorks database. In both connections execute the
> following:
> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> Begin Tran
> SELECT EmployeeID From HumanResources.Employee Where EmployeeID = 1
>
> Then, in the first connection execute the following (but do not commit the
> transaction):
> Update HumanResources.Employee Set MaritalStatus = 'M' Where EmployeeID =
> 1
> In the second connection execute the exact same line. This will cause a
> deadlock error in the second connection. We get a deadlock even though
> both processes are performing the exact same action in the exact same
> order.
> This example may not be the best but is my assumption correct that when
> using Repeatable Read, the likelihood of a deadlock error is greater than
> when using Read Committed?
> Thanks, Amos.
>
>