how to solve deadlock problem?

Question

i have read this dead lock problem When database tables start accumulating thousands of rows and many users start working on the same table concurrently, SELECT queries on the tables start producing lock contentions and transaction deadlocks.

Is this deadlock problem related with TransactNo updlock? If you know this problem, let me know pls. Thanks in advance.

Answer 1

You have not supplied enough information to answer your question directly.

But most locking and blocking can be reduced (or even eliminated) by having the 'correct' indexes to cover your query workload.

Due you have a regular index maintainance job scheduled?

If you have SELECTs that do not need to be 100% accurate (i.e. allow dirty reads etc) then you can run some SELECTS with WITH(NOLOCK), which is the same as an isolation level of READ UNCOMMITED. Please Note: I'm not suggesting you place WITH(NOLOCK) everywhere; just on those SELECTS that do not need 100% intact data.

Answer 2

A deadlock can happen for many many reasons so you would have to do a little bit of homework first if you want to be helped and tell us what is causing the deadlock, ie. what are the batches involve din the deadlock executing, what resources are involved and so on and so forth. The Profiler deadlock event graph is always a great place to start the investigation.

If I'd venture a shot in the dark what happens is that your queries and indexes are not tuned properly so most of your read operations (and perhaps some of the writes) are full table scans and thus are guaranteed to collide with updates. This can cause deadlocks by order of index access, deadlock by order of operations, deadlock by escalation and so on and so forth.

Once you identify the cause of the deadlock then the proper action to remove it can be taken. The cases when he proper action is to resort to dirty reads are extremely rare.

BTW I'm not sure what you mean by 'TransactNo updlock'. Are you specifically asking about the S-U/U-S asymmetry of the U locks?

Answer 3

"Deadlock Troubleshooting, Part 1"

http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

"When Index Covering Prevents Deadlocks"

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/03/when-index-covering-prevents-deadlocks.aspx

Answer 4

A common issue with high isolation is lock escalation deadlocks due the the following scenario; i.e. (where X is any resource, such as a row)

• SPID a reads X - gets a read lock
• SPID b reads X - gets a read lock
• SPID a attempts to update X - blocked by b's read lock, so has to wait
• SPID b attempts to update X - blocked by a's read lock, so has to wait

Deadlock! This scenario can be avoided by taking more locks:

• SPID a reads X with (UPDLOCK) specified - gets an exclusive lock
• SPID b attempts to reads X - blocked by a's exclusive lock, so has to wait
• SPID a attempts to update X - fine
• ... (SPID a commits/rolls-back, and releases the lock at some point)
• ... (SPID b does whatever it wanted to do)

Answer 5

I'll throw my own articles and posts into the mix about deadlocks:

http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Deadlock/default.aspx

I also have a series of videos on troubleshooting deadlocking on JumpstartTv.com as well:

http://jumpstarttv.com/profiles/1379/Jonathan-Kehayias.aspx

Deadlocks can be difficult to resolve, but unless you post your deadlock graph information, there isn't anyway we can do more than offer up links to posts and information on solving deadlocks.