Deadlock on single table

We have one user who enters a transaction and then does a single row
update (updates all columns but only one is changing - this is due to
the way our sql is generated in the application), at this point
another user enter a transaction and tries to update the same row (he
understandably has to sit and wait while he is blocked by the original
user). The original user then updates the same row again at this
point the second user is chosen as a deadlock victim and killed. If I
try and recreate this with any other tables(or pubs) I get my expected
behaviour of the original user just doing 2 successful updates and the
second user then completing his update once the original user has
either committed his changes or rolled back. The query plan indicates
that a drop and insert of the row is happening (this is not the case
with any other tables where we get our expected behaviour). This only
happens when the index is clustered - if we use a non-clustered index
it does not occur.

Is this expected behaviour? it seems dangerous to me as the first
user has not commited or rolled back his updates. It was only
highlighted by a fault in our application that caused the second
update to be executed.

I have some thoughts about it being something to do with a row lock
being relased due to a delete / insest of the row in the second update
(we see this in the execution plan)....

Any help much appreciated as I am struggling to get my head round how
the second user was ever able to get hold of the resource.
CODA PBC wrote:

> We have one user who enters a transaction and then does a single row
> update (updates all columns but only one is changing - this is due to
> the way our sql is generated in the application), at this point
> another user enter a transaction and tries to update the same row (he
> understandably has to sit and wait while he is blocked by the original
> user). The original user then updates the same row again at this
> point the second user is chosen as a deadlock victim and killed. If I
> try and recreate this with any other tables(or pubs) I get my expected
> behaviour of the original user just doing 2 successful updates and the
> second user then completing his update once the original user has
> either committed his changes or rolled back. The query plan indicates
> that a drop and insert of the row is happening (this is not the case
> with any other tables where we get our expected behaviour). This only
> happens when the index is clustered - if we use a non-clustered index
> it does not occur.
> Is this expected behaviour? it seems dangerous to me as the first
> user has not commited or rolled back his updates. It was only
> highlighted by a fault in our application that caused the second
> update to be executed.
> I have some thoughts about it being something to do with a row lock
> being relased due to a delete / insest of the row in the second update
> (we see this in the execution plan)....
> Any help much appreciated as I am struggling to get my head round how
> the second user was ever able to get hold of the resource.

Hi. The trouble is that there is more than one lockable object
usually involved in an update. The datarow/page, and likely one
or more index page. It is unfortunate that with the clustered index,
your two users are obtaining those locks in different orders (a function
of the different query plans), causing a deadlock. I suppose that if both
updaters used the same plan (sending the exact same SQL), you would get
the behavior you want. It is sadly ugly that the generated code is
updating every column to change only one. Particularly if the change
includes the clustered key column(s), because it tells the DBMS that the
row has to be deleted from the clustered index (the last nodes of which
are the data pages), and re-inserted where the new key values dictate.
(It might be a fond hope that the DBMS could examine the key values
could be examined and the DBMS could interpret whether the row
actually has to move, but that is in reality not possible. The plan
needs to be made before the actual table data are accessed.).

I hope this helps,
Joe Weinstein at BEA

deadlock due to transactions within a single SPID(syslockinfo table)

I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.

---

rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW

---

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000

---

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000

---

For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.

I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?
2) what is the relationship between req_transactionId column and spid column?
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) Will there be contention for locks between transactions within a single spid.

it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while

the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607

If some body could please help me it would be really helpful for me.

Thanks in advance!!

have you got a deadlock trace set on your server, or a deadlock graph from profiler (if on 2005)?

either would give alot more diagnostic info

|||

Hi, Yes this the exact situation i am facing here as well.

Point to note here is the database is TEMPDB. (i.e. rsc_dbid = 2).

To see the below text properly, please copy from here and paste in notepad then it would be easier to understand.

rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 149 0 1 87573395 00000000-0000-0000-0000-000000000000
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 58219975 149 0 1 87685718 00000000-0000-0000-0000-000000000000

As suggested by rb1n I have enabled the traces and the profiler logs as well but nothing much helpful exists there.

Basically it really requires understanding about the field "req_transactionID " in syslockinfo table. Not much information is available about this filed on Microsoft websites.

Is it right to say:

- In a normal situation one SPID will have same "req_transactionID " in syslockinfo table?

Reason for asking this is: On normal days when my SP executes (lest say under SPID X) without any problem the syslockinfo table has same "req_transactionID " for all transaction under SPID X . i.e. "req_transactionID " never changes. Days, when deadlock happens the "req_transactionID " is different and have same kind of situation as posted above.

- If not agree with the above understanding then can someone tell me in what situation the "req_transactionID " can be different for same SPID? Or simply what is the significance of column "req_transactionID ”?

- Is this something Microsoft is aware of?

Thanks very much for you time on this.|||did you get a deadlock graph logged in sql profiler? (in the TextData)|||The deadlock occurred in sql server 2000.

I have attached the error log below:

Wait-for graph

Lockeadlock Chain Deadlock Chain SPID = 112
Node:1
PAG: 2:4:103776 CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x4b713220 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:112 ECID:0
SPID: 112 ECID: 0 Statement Type: CREATE INDEX Line #: 1
Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:112 ECID:0 Ec0x33705528) Value:0x47908120 Cost51/B87FB18)

deadlock due to transactions within a single SPID(syslockinfo table)

I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.

---

rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW

---

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000

---

1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000

---

For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.

I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?
2) what is the relationship between req_transactionId column and spid column?
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) Will there be contention for locks between transactions within a single spid.

it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while

the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607

If some body could please help me it would be really helpful for me.

Thanks in advance!!

have you got a deadlock trace set on your server, or a deadlock graph from profiler (if on 2005)?

either would give alot more diagnostic info

|||

Hi, Yes this the exact situation i am facing here as well.

Point to note here is the database is TEMPDB. (i.e. rsc_dbid = 2).

To see the below text properly, please copy from here and paste in notepad then it would be easier to understand.

rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 149 0 1 87573395 00000000-0000-0000-0000-000000000000
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 58219975 149 0 1 87685718 00000000-0000-0000-0000-000000000000

As suggested by rb1n I have enabled the traces and the profiler logs as well but nothing much helpful exists there.

Basically it really requires understanding about the field "req_transactionID " in syslockinfo table. Not much information is available about this filed on Microsoft websites.

Is it right to say:

- In a normal situation one SPID will have same "req_transactionID " in syslockinfo table?

Reason for asking this is: On normal days when my SP executes (lest say under SPID X) without any problem the syslockinfo table has same "req_transactionID " for all transaction under SPID X . i.e. "req_transactionID " never changes. Days, when deadlock happens the "req_transactionID " is different and have same kind of situation as posted above.

- If not agree with the above understanding then can someone tell me in what situation the "req_transactionID " can be different for same SPID? Or simply what is the significance of column "req_transactionID ”?

- Is this something Microsoft is aware of?

Thanks very much for you time on this.|||did you get a deadlock graph logged in sql profiler? (in the TextData)|||The deadlock occurred in sql server 2000.

I have attached the error log below:

Wait-for graph

Lockeadlock Chain Deadlock Chain SPID = 112
Node:1
PAG: 2:4:103776 CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x4b713220 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:112 ECID:0
SPID: 112 ECID: 0 Statement Type: CREATE INDEX Line #: 1
Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:112 ECID:0 Ec0x33705528) Value:0x47908120 Cost51/B87FB18)