How to resolve this deadlock caused by update/delete

Question

How to resolve this deadlock caused by update/delete

Jennifer Wang-317627

Old Hand

Points: 359
More actions
January 4, 2010 at 3:23 pm

#207956

I have a problem with deadlock, and the process is like the following:
Begin Transaction
update table A .....
delete from table A join table B.....
update table A join table C.....
Commit Transaction
When two of these processes run concurrently, deadlock often occurs. How do you often resolve this kind of problem? I am thinking about letting only one process entering this transaction at one time, can I just set the transaction isolation level to achieve this goal?
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

Can you give us more information? Can you reproduce the problem? If so, please enable trace flag 1222 and see what is the output. What do you read any data from table A before you update it? Are indexes used in queries that join to tables B and C?

Regards

Piotr

...and your only reply is slàinte mhath

Jennifer Wang-317627 Old Hand Points: 359 More actions · Answer 2

Both tables B and C do not have indices because they are very small temporary tables.... This problem is rare to happen because ususally there are not too many rows. However the one time which it happened I saw the blocks/deadlocks in the db, something like process 1 is waiting for 2, which is waiting for 3, which is waiting for 1.... I had to manually kill the java process to resolve the problem.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

DBCC TRACEON(1222,-1)

Block or deadlock? A deadlock you do not have to resolve. SQL has something called the deadlock detector that will find and resolve deadlocks automatically.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jennifer Wang-317627 Old Hand Points: 359 More actions · Answer 4

Thanks everyone for the reply.... I will turn on the trace flag, but it is very hard to reproduce as we don't often have two of these processes running together but once they do, it becomes a very big production problem....

Meanwhile, is there any way for me to limit one transaction to work on the table at one time? That would surely avoid the deadlocks.

By the way I feel it's a combination of deadlock/blocks. It is a 3 way deadlock in my mind and SQL server did not resolve that.

Thanks,

Jennifer

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Jennifer Wang-317627 (1/5/2010)
Meanwhile, is there any way for me to limit one transaction to work on the table at one time? That would surely avoid the deadlocks.

An exclusive table lock, but that's rather bad for concurrency

By the way I feel it's a combination of deadlock/blocks. It is a 3 way deadlock in my mind and SQL server did not resolve that.

I've seen SQL resolve a 5-way deadlock before. It can detect and resolve multi-process deadlocks. There are deadlocks that it won't resolve, but they are rare. If you have one, call customer support and file a bug report.

Are you sure it wasn't just a long blocking chain?

Did any of the processes get the 1205 error? (This process was deadlocked with another...)

If not, the traceflag won't show anything as it shows the state of things when the deadlock detector finds a deadlock.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jennifer Wang-317627 Old Hand Points: 359 More actions · Answer 6

The reason why I thought it's a deadlock is 1)There were messages in our log about deadlocks on this process, and some processes have been chosen to be killed by SQL server. However in our code we re-issue the whole transaction when this happens

2) When I looked at SQL server processes at the time, I saw process 1 waiting for 2, 2 waiting for 3 ,and 3 waiting for 1, that's why I thought it was a 3 way deadlock....

Thanks,

Jennifer

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

That looks like a deadlock. It's possible you saw it in the transient state as it was getting identified, or one of the processes was already rolling back.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jennifer Wang-317627 Old Hand Points: 359 More actions · Answer 8

So to avoid the problem, should I use tab lock for every statement in the transaction (update, delete, then update again)? Or should I just set a different isolation level for this transaction so that another one won't be able to run unless this one finishes?

Thanks,

Jennifer

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

You can add table locks if you like, if you don't mind forcing SQL to run only one thing at a time. It's not a good thing for concurrency and it's not something I'd ever recommend. (if you do, the next problem may well be that the process runs too slow because of all the locks)

I would suggest getting the deadlock graph next time it happens. (You can add traceflag 1222 to the startup parameters so it's permanently on), then analyse the cause of the deadlock and resolve it.

Just putting table locks everywhere is the sledgehammer approach.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass