It may be waiting for some other resource, such as a latch, memory, or IO, but at least one session will not be waiting for a lock, and the blocking chain will clear as soon as the head blocker can continue processing.Ī deadlock is different it occurs when two or more sessions are waiting for each other, in such a way that none can complete. However, at the head of the blocking chain will be a head ‘blocker’ that is not waiting for a lock. It is possible to have extensive blocking chains where multiple sessions are blocked waiting for a session that itself is blocked waiting for another session that is blocked and so on, repeating multiple time. This is a transient situation and can be completely resolved by the session B completing its work and releasing its locks. See Further Reading at the end of the article for some useful references. I don’t have space in this piece for a fuller discussion of lock modes and compatibility. S locks and IX locks are incompatible, and so session B’s thread blocks session A’s until the former completes its work and releases the locks. However, session B’s process holds an IX lock on one of the pages that contains some of the rows session A needs. Its associated process acquires an Intent-Shared (IS) lock on the table (since IS and IX lock mode are compatible) and then attempts to acquire an S lock on the pages it needs to read. Simultaneously, session A needs to read a few pages on same table. The session’s associated process (thread) currently holds an Intent-Exclusive (IX) lock on both the table and the page that contains the row, and an X lock on the row. In my experience, developers and DBAs often think that their SQL Server instance is experiencing deadlocks when, really, it is experiencing severe blocking.īlocking occurs when session A requests a lock on a resource (typically a row, page or table), but SQL Server cannot grant that lock because session B already holds a non-compatible lock on that resource.įor example, let’s assume that session B is in the process of modifying a row in the Invoices table. The Difference between Severe Blocking and Deadlocking We’ll also consider the root causes of each type of deadlock, the code patterns that make them a possibility, how to avoid them recurring, and the need to deal with deadlocks, and all other SQL Server errors gracefully, with error handling and retries. For each type of deadlock, we’ll review ‘typical’ deadlock graphs and discuss the signature that distinguishes each one, so that you can recognize it if you see it on your own systems.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |