AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Without intervention, these two processes would sit forever waiting for each other. It cannot get that lock until process 1 finishes and releases its lock on that page.Īt this point, neither process can proceed we have a deadlock. Process 2 cannot continue until it receives the lock that it wants on page 1:1370. It cannot get that lock until process 2 finishes and releases its lock on that page. In the second step, each of the two processes requests a lock on the resource on which the competing session holds a lock that is incompatible with the requested lock.Īt this point, process 1 cannot continue until it receives the lock that it wants on page 1:4224. This will be within a transaction, explicit or not, and hence neither process will release these locks immediately. In the first, each of the two processes requests and acquires a lock. A deadlock can be viewed as a circular lock chain, where every single process in the blocking chain is waiting for one or more other processes in that same blocking chain.Ĭonsider the simplest possible deadlock, with two sessions, two processes and two resources (later sections will demonstrate deadlocks that are more complex). 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
Read More
Leave a Reply. |