deAttach database: single

Showing posts with label single. Show all posts

Wednesday, March 21, 2012

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 on a single table but multiple processes

Hi! We have a third party application that calls same stored procedure
simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
Deadlock trace shows both spids are running exactly same statement within
the procedure. Depending upon input parameter the statement does either
insert or update. But the deadlock trace shows that deadlock happens when
both are running update statements. Multiple thread supposed to update same
table but different rows (at most couple of rows).
The object (key) they are deadlocking on is a non clustered index used to
search data for update. Update statement doesn't modify any column that
belongs to this non clustered index. Database is running on default
(read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
the stored procedrue, so I assume that the statement is not a part of
explicit transaction.
Questions:
1. Why sql server is using update lock (And not the shared lock) on the non
clustered index which used to search the data. The update statement doesn't
modify this non clustered index. In below statement Index id 5 is on
position_id, security_alias and long_short_indicator.
2. Why deadlock and not just blocking? What is a fix for this?
Below is the update_statement that both SPID are running:
UPDATE CA
SET CANCEL_STATUS = 'Y',
UPDATE_SOURCE = @.in_update_source,
UPDATE_DATE = GETDATE()
from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
WHERE POSITION_ID = @.nTargetPositionId
AND SECURITY_ALIAS = @.in_security_alias
AND LONG_SHORT_INDICATOR = 'L'
AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
AND STAR_TAG25 = @.in_event_id
AND CASH_BAL_INST = @.in_event_sequence
AND CANCEL_FLAG = 'N'
AND REFLEXIVE_FLOW = 'Y'
Below is output of deadlock trace:
Deadlock encountered ... Printing deadlock information
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Wait-for graph
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:1
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
CleanCnt:2 Mode: X Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 3::
2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:589 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:470 ECID:0 Ec

0x72B99520) Value:0xb61fa660 Cost

0/7280)
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:2
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
CleanCnt:2 Mode: U Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 2::
2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x0
Ref:0 Life:00000001 SPID:470 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec