Deadlock on different unique RIDs in SQL Server 2000 - how?

Ok, I'm in the process of unravelling some deadlocks we've got in our
current application. I've been able to work through the PAG and KEY
deadlocks with no problems. However, I'm absolutely stumped with a
deadlock that has two RID nodes.
There are 2 nodes in this deadlock, each with a different unique RID as
the resource currently deadlocked.
Using the DBCC PAGE command shows that the page for each of the RID
nodes is from the same table. But each individual RID node is for a
unique row in that table (obviously).
The deadlock wait-for graph report clearly identifies the two SPIDs
that are deadlocking. Using the information I have from the profiler
trace of the run I can see the history of the SQL up to and including
the point of deadlock.
But here's the crux of the problem I don't understand.
The SPID for Node 1 is updating row R1 in table T.
The SPID for Node 2 is inserting row R2 into table T.
The deadlock occurs in the code for the update trigger and insert
trigger that is attached to table T.
How can these two SPIDs deadlock each other on the RID?

>From the logic of the application, if I'm updating R1 it has no
"knowledge" of the fact that R2 is being added to the table. Likewise,
adding new row R2 has no "knowledge" of existing row R1.
I have simplified what actually happens slightly, but fundamentally the
problem boils down to the issue I've just highlighted. The two SPIDS
deadlocking on two completely different RIDs - I don't understand how
or why?
It's worth mentioning that both the insert and the update trigger use
the inserted virtual table to get the ID of the item just
added/updated. And the code does other processing using that ID.
I know this sounds like a real long shot, but is it at all possible
that the inserted table has got "confused" with the "wrong" ID in it
when each SPID starts executing its insert/update trigger?
Any help at all would be appreciated.I suggest you examine the execute plans of the trigger statements involved
in the deadlock. Check to see of there is a operation (e.g. scan) that
might require access to the row modified by the other transaction. Some
index tuning can avoid this situation.
Hope this helps.
Dan Guzman
SQL Server MVP
"MisterD" <david@.nfs-hospitality.com> wrote in message
news:1152630911.019162.260080@.b28g2000cwb.googlegroups.com...
> Ok, I'm in the process of unravelling some deadlocks we've got in our
> current application. I've been able to work through the PAG and KEY
> deadlocks with no problems. However, I'm absolutely stumped with a
> deadlock that has two RID nodes.
> There are 2 nodes in this deadlock, each with a different unique RID as
> the resource currently deadlocked.
> Using the DBCC PAGE command shows that the page for each of the RID
> nodes is from the same table. But each individual RID node is for a
> unique row in that table (obviously).
> The deadlock wait-for graph report clearly identifies the two SPIDs
> that are deadlocking. Using the information I have from the profiler
> trace of the run I can see the history of the SQL up to and including
> the point of deadlock.
> But here's the crux of the problem I don't understand.
> The SPID for Node 1 is updating row R1 in table T.
> The SPID for Node 2 is inserting row R2 into table T.
> The deadlock occurs in the code for the update trigger and insert
> trigger that is attached to table T.
> How can these two SPIDs deadlock each other on the RID?
>
> "knowledge" of the fact that R2 is being added to the table. Likewise,
> adding new row R2 has no "knowledge" of existing row R1.
> I have simplified what actually happens slightly, but fundamentally the
> problem boils down to the issue I've just highlighted. The two SPIDS
> deadlocking on two completely different RIDs - I don't understand how
> or why?
> It's worth mentioning that both the insert and the update trigger use
> the inserted virtual table to get the ID of the item just
> added/updated. And the code does other processing using that ID.
> I know this sounds like a real long shot, but is it at all possible
> that the inserted table has got "confused" with the "wrong" ID in it
> when each SPID starts executing its insert/update trigger?
> Any help at all would be appreciated.
>|||Dan,
Thanks for replying. I guess I missed one important point out - doh!
Both SPIDs have exclusive (X) locks on each RID and they are both
requesting an update (U) lock on the opposing RID.
If it was a simple shared (S) lock, I think I could understand, but
it's the fact that it's an update lock that I really don't understand.
FYI, the problem occurs at the end of the insert/update trigger which
tries to do an UPDATE statement on the row that has just been
inserted/updated.|||Hi MisterD,
I do not have solution for you, however I have the same type of problem, how
do you trace deadlock on the Profiler, I need to see which process, what is
sql statement is causing the trouble, can you tell me what I need to trace?
"MisterD" wrote:

> Ok, I'm in the process of unravelling some deadlocks we've got in our
> current application. I've been able to work through the PAG and KEY
> deadlocks with no problems. However, I'm absolutely stumped with a
> deadlock that has two RID nodes.
> There are 2 nodes in this deadlock, each with a different unique RID as
> the resource currently deadlocked.
> Using the DBCC PAGE command shows that the page for each of the RID
> nodes is from the same table. But each individual RID node is for a
> unique row in that table (obviously).
> The deadlock wait-for graph report clearly identifies the two SPIDs
> that are deadlocking. Using the information I have from the profiler
> trace of the run I can see the history of the SQL up to and including
> the point of deadlock.
> But here's the crux of the problem I don't understand.
> The SPID for Node 1 is updating row R1 in table T.
> The SPID for Node 2 is inserting row R2 into table T.
> The deadlock occurs in the code for the update trigger and insert
> trigger that is attached to table T.
> How can these two SPIDs deadlock each other on the RID?
>
> "knowledge" of the fact that R2 is being added to the table. Likewise,
> adding new row R2 has no "knowledge" of existing row R1.
> I have simplified what actually happens slightly, but fundamentally the
> problem boils down to the issue I've just highlighted. The two SPIDS
> deadlocking on two completely different RIDs - I don't understand how
> or why?
> It's worth mentioning that both the insert and the update trigger use
> the inserted virtual table to get the ID of the item just
> added/updated. And the code does other processing using that ID.
> I know this sounds like a real long shot, but is it at all possible
> that the inserted table has got "confused" with the "wrong" ID in it
> when each SPID starts executing its insert/update trigger?
> Any help at all would be appreciated.
>|||JIM.H. wrote:
> Hi MisterD,
> I do not have solution for you, however I have the same type of problem, h
ow
> do you trace deadlock on the Profiler, I need to see which process, what i
s
> sql statement is causing the trouble, can you tell me what I need to trace
?
>
http://www.sql-server-performance.com/deadlocks.asp
I have given you this URL twice, have you actually read that article?
What more do you need to know? A selection I copied from that page:
To help identify deadlock problems, use the SQL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" trace. This
will provide you with the raw data you need to help isolate the causes
of deadlocks in your databases. [7.0]
*****
Consider turning on SQL Server deadlock tracing in order to track
deadlocks, when you are having deadlock-related issues. The overhead for
doing this is minimal.
DBCC TRACEON (3605,1204,-1)
Once this is run, then all deadlocking activity will be written to the
SQL Server log file. [7.0, 2000] Added 11-22-2004
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy, thanks for your help, I could not see how I can strat wizard to ru
n
"Identify The Cause of a Deadlock" in my Profiler. Is this in SQL Server
2000 profiler?
"Tracy McKibben" wrote:

> JIM.H. wrote:
> http://www.sql-server-performance.com/deadlocks.asp
> I have given you this URL twice, have you actually read that article?
> What more do you need to know? A selection I copied from that page:
>
> To help identify deadlock problems, use the SQL Server Profiler's Create
> Trace Wizard to run the "Identify The Cause of a Deadlock" trace. This
> will provide you with the raw data you need to help isolate the causes
> of deadlocks in your databases. [7.0]
> *****
> Consider turning on SQL Server deadlock tracing in order to track
> deadlocks, when you are having deadlock-related issues. The overhead for
> doing this is minimal.
> DBCC TRACEON (3605,1204,-1)
> Once this is run, then all deadlocking activity will be written to the
> SQL Server log file. [7.0, 2000] Added 11-22-2004
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||JIM.H. wrote:
> Hi Tracy, thanks for your help, I could not see how I can strat wizard to
run
> "Identify The Cause of a Deadlock" in my Profiler. Is this in SQL Server
> 2000 profiler?
>
No, that wizard is exclusive to SQL 7, but that wasn't the reason I
highlighted that paragraph. I highlighted it so that you would see that
you can use Profiler to identify deadlocks. A quick Google search for
"profiler" and "Identify The Cause of a Deadlock" turns up numerous
articles that will tell you what you need to do. I'm not trying to be
vague, but deadlocking is a complex topic, and you will learn more by
doing some research than you will if I just give you the answers.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Lines: 29
Thread-Topic: Deadlock on different unique RIDs in SQL Server 2000 - how?
thread-index: AcalyHXJq6eo+Jx8STe3Acz3a5gfRQ==
X-WBNR-Posting-Host: 71.16.104.203
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:5451
Hi Tracy,
I am dong search on Google, there are many options but no time to read all
of them since there are other problems are going on around here. I am using
Server 2000 profiler, is this wizard available there or do I have any other
options to go with?
"Tracy McKibben" wrote:

> JIM.H. wrote:
> No, that wizard is exclusive to SQL 7, but that wasn't the reason I
> highlighted that paragraph. I highlighted it so that you would see that
> you can use Profiler to identify deadlocks. A quick Google search for
> "profiler" and "Identify The Cause of a Deadlock" turns up numerous
> articles that will tell you what you need to do. I'm not trying to be
> vague, but deadlocking is a complex topic, and you will learn more by
> doing some research than you will if I just give you the answers.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Jim H - Get off my thread!
Go and do some research yourself (like the lady said).
Deadlocks are not easy issues to resolve. If you don't understand them
then you need to go and read about them until you do.
Goodbye!|||MisterD wrote:
> Jim H - Get off my thread!
> Go and do some research yourself (like the lady said).
> Deadlocks are not easy issues to resolve. If you don't understand them
> then you need to go and read about them until you do.
> Goodbye!
>
Lady? What lady? :-)
He's got three or four threads going, all looking for an easy answer for
deadlocking. Somebody once said "You can lead a horse to water..."
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql