June 3, 2002 at 1:24 pm
You have been chosen as dead victim!!
Dear guys,
I got this message, “You have been chosen as dead victim … “”
How can I avoid this message , I think this because of more than user try to update the same table at the same time !!
June 3, 2002 at 3:59 pm
Deadlocks can be hard to find. It's based on the order the info (or the index)is accessed. You can turn on trace flag 1204 to get some additional information about them. Ultimately while you can prevent many, they can always occur, good to trap for the error in your applications and retry when it happens.
Andy
June 3, 2002 at 5:42 pm
Also it is a good idea to build your application so it logs the actions it was doing right before the deadlock ocurred and if you don't have too many copies out there have each get their spid and store to help solve theses myteries.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 5:58 pm
If you can duplicate this, run profiler. If it happens regularly, then let profiler run and dig through it later.
As mentioned, these are hard to trace.
Steve Jones
June 4, 2002 at 9:56 am
The root cause of deadlocks is a design issue. The most common way to get a deadlock is when User1 has Table A locked and then needs a lock on Table B, while at the same time User2 has Table B locked and needs a lock on Table A. In a well designed Master Detail type system, always try to retrieve data in the same order. Going back to the root design of our system and figuring out just what we were asking SQL to do and in what order stopped 98% of our deadlocks. Adding a couple of indexes to speed up retrieval speed stopped the rest. Having said all that, you still need to handle it in your Client code and retry as needed.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
June 4, 2002 at 12:07 pm
Thanks for answers,
This problem happens when many users try to update the same table at the same time.
Example:
User 1
Update table TABLE_A set Field1 = ‘X’ where ID = ‘11111’
Update table TABLE_A set Field2 = ‘Z’ where ID = ‘11111’
Update table TABLE_A set Field3 = ‘F’ where ID = ‘11111’
Update table TABLE_A set Field4 = ‘G’ where ID = ‘11111’
Update table TABLE_A set Field5 = ‘U’ where ID = ‘11111’
Update table TABLE_A set Field6 = ‘I’ where ID = ‘11111’
Update table TABLE_A set Field7 = ‘L’ where ID = ‘11111’
User 2
Update table TABLE_A set Field1 = ‘X’ where ID = ‘22222’
Update table TABLE_A set Field2 = ‘Z’ where ID = ‘22222’
Update table TABLE_A set Field3 = ‘F’ where ID = ‘22222’
Update table TABLE_A set Field4 = ‘G’ where ID = ‘22222’
Update table TABLE_A set Field5 = ‘U’ where ID = ‘22222’
Update table TABLE_A set Field6 = ‘I’ where ID = ‘22222’
Update table TABLE_A set Field7 = ‘L’ where ID = ‘22222’
User 3
Update table TABLE_A set Field1 = ‘X’ where ID = ‘33333’
Update table TABLE_A set Field2 = ‘Z’ where ID = ‘33333’
Update table TABLE_A set Field3 = ‘F’ where ID = ‘33333’
Update table TABLE_A set Field4 = ‘G’ where ID = ‘33333’
Update table TABLE_A set Field5 = ‘U’ where ID = ‘33333’
Update table TABLE_A set Field6 = ‘I’ where ID = ‘33333’
Update table TABLE_A set Field7 = ‘L’ where ID = ‘33333’
One of these users always get Deadlock victim message
So, I still need your advice to solve this problem.
thanks
June 4, 2002 at 1:40 pm
The best way is to post the results from the error log after turning on trace flag -1204. Without seeing that I'd guess its happening due to your index plan, where one of those "field" columns is part of one or more indexes. Updating the col updates the index.
Andy
June 4, 2002 at 3:03 pm
Is ID indexed, if not the do so, and if this is the most commonly use column for queries make clustered. Then see if that relieves the issue. However as they must obtain a row lock to update this can still occurr if the same row is being updated you will have to make your app resubmit.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 4, 2002 at 4:20 pm
George - You wrote:
quote:
Example:User 1
Update table TABLE_A set Field1 = ‘X’ where ID = ‘11111’
Update table TABLE_A set Field2 = ‘Z’ where ID = ‘11111’
Update table TABLE_A set Field3 = ‘F’ where ID = ‘11111’
Update table TABLE_A set Field4 = ‘G’ where ID = ‘11111’
Update table TABLE_A set Field5 = ‘U’ where ID = ‘11111’
Update table TABLE_A set Field6 = ‘I’ where ID = ‘11111’
Update table TABLE_A set Field7 = ‘L’ where ID = ‘11111’
etc.....
One thing that seems obvious to me, not sure if your example was straight from your code or not, is that I can see no reason to do 7 separate Update statements. I would certainly combine them as in:
Update Table_A Set Field1 = 'X', Field2 = 'Z', Field3 = 'F' [ETC] Where ID = '11111'
This would reduce your number of concurrent updates from 21 to 3 in the 3 user example you gave. As long as the ID Field is indexed I just don't see that you should be getting the infamous "Deadlock Victim" message at this level of use.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
June 4, 2002 at 4:31 pm
Bob, my hats off to you - thats an excellent tip - embarrased I didnt think of it myself!
Andy
June 10, 2002 at 9:13 am
Where does the output report from trace flag 1204 appear?
June 10, 2002 at 9:24 am
In the sql error log. Its a big chunk of info, BOL has some info on deciphering it.
Andy
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply