473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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.
Jul 20 '05 #1
1 5334


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

Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Steve Thorpe | last post by:
Hi I have a deadlock situation and I am trying to debug my Trace Log. How do I find out what is the cause ? I can see from the trace I have an exclusive lock on a RID, but how can I find out...
8
by: Anita | last post by:
Hi All, Can multiple updates on one table using single query generate deadlock ? For example, at the same time, there are 2 users run 2 queries as follows : User1 runs : update tab1 set...
11
by: hendershot | last post by:
Using SQL Server 2000 SP3a, I run the following in 2 query analizer windows on the Northwind database, the second one always gets the deadlock Msg 1205: Window 1: declare @cnt int select...
7
by: Andrew Mayo | last post by:
Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into...
3
by: Nigel Robbins | last post by:
Hi There, I'm getting a deadlock when I have two clients running the following statement. DELETE FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid There is a compound index on...
1
by: Rohit Raghuwanshi | last post by:
Hello all, we are running a delphi application with DB2 V8.01 which is causing deadlocks when rows are being inserted into a table. Attaching the Event Monitor Log (DEADLOCKS WITH DETAILS) here....
15
by: Zeng | last post by:
Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried)...
2
by: Hugo Flores | last post by:
Hi, I'm getting a deadlock on my database. Let me first tell you that this is a test database on a Win XP Professional. The SP where I'm getting the deadlock is this: PROCEDURE...
3
by: ThunderMusic | last post by:
Hi, We have a web application developped in asp.net (I think it's not relevant, but well, it's so you know)... Yesterday, we received the following message "Transaction (Process ID 69) was...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.