可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

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.

回答1:

Deadlocks can occur for many reasons and sometimes troubleshooting deadlocks can be more of an art than a science.

What I use to find and get rid of deadlocks, outside of plain SQL Profiler, is a lightweight tool that gives a graphical depiction of deadlocks as they occur. When you see a deadlock, you can drill down and get valuable information. Deadlock Detector -- http://www.sqlsolutions.com/products/sql-deadlock-detector

It's a simple tool, but for me, it does exactly what it is supposed to do. One thing: the first time I used it, I had to wait 15 minutes for the tool to gather enough metrics to start showing deadlocks.

回答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?

回答3:

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)

回答4:

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.

回答5:

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

https://www.sqlskills.com/blogs/jonathan/category/deadlock/

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.