September 29, 2005 at 7:13 am
I have a table. I am trying to run the following queries and deadlock occurs. How to avoid deadlock. What hint works the best under such situations.
Open Query Analyzer and run the following query:
WHILE 1 = 1
UPDATE Table1 SET STATE = 1 WHERE SEQUENCE = 1
Leave it running...
Now Open another query analyzer and run the following query:
UPDATE Table1 SET STATE = 1 WHERE SEQUENCE = 1
This will deadlock.
Thanks
September 29, 2005 at 7:21 am
What happens if you stop the first loop after you started the second window's update?
September 29, 2005 at 7:30 am
It gets executed
September 29, 2005 at 7:33 am
So it's just locked... waiting for the loop to stop sending requests (the loop is part of the statement to execute so it acts as a single operation, not like a long series of update statements sent one after the other).
September 29, 2005 at 8:16 am
If u execute any DML command on a table and at the same time u issue another command on the same table .
SQL Server put a lock on that table becasue the first query is getting executed and second will be on hold and wait till the lock removes from that table.
September 29, 2005 at 10:22 am
Here is what we are seeing.
We have Table1 with Col1,Col2,Col3,Col4.
In the update trigger of the Table1, we have update statement to update Col2 = getdate() for the updated record.
1. Process 1 issues an Update and gets a row lock on the table
2. The update trigger begins
3. Process 2 issues an Update and is now waiting on row lock
4. The update trigger for Process 1 gets to Line #23 and attempts another update...but cannot obtain the lock
5. Deadlock
Any ideas?
September 30, 2005 at 6:27 am
1) Why is there a trigger to update a column to getdate() ? Why is that not being handled in the update statement itself ?
2) Also, you mentioned in #4, that when the triggers reaches line #23...but in the start you are stating that since you are updating a single record (am assuming this update is being done based on the primary key or a unique key and that is properly indexed), which means that the trigger should be written such that it should be updating only that single record so what is line #23 ?
3) Any update statement has to first read a record internally to get the physical position of the row and thus takes a S lock on the record..in order to update the record, this lock then needs to be converted to a X lock. If there are two sessions trying to do the same thing simultaneously, then both will have their S locks (taken implicitly for a brief duration as part of the udpate statement itself) but will wait for the other before converting it into an X lock and thus a deadlock can occur.
Please remember to always do a select with (UPDLOCK, ROWLOCK) before firing off the update. That takes a U (update lock) on the record and prevents a common form of deadlock known as lock conversion deadlock. The UPDLOCK locking hint takes the U lock - the ROWLOCK is to prevent any lock escalations (this is not needed if you are using a PK or a good cardinality indexed column in the where clause for the update statement).
4) Please ensure that the where clause on the basis of which the update is being made is on good indexed columns. If not, then even though only one record might be getting updated finally, the statement itself will take coarser level locks.
5) If still in doubt, then turn on trace flags, 1204, 1205 and 3605 and take a look at the deadlock trace file that gets generated. That will yield a lot of information that will help you to troubleshoot this further.
Hth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply