Behind locking, there must be a queuing mechenism implemented in the base framework. In C#, if multiple threads are running the same code below, when the first thread successfully acquired a lock on syncObject and continues the code within the brace, the second thread will be waiting for the lock on syncObj to be released. If the third thread comes in, it will wait until the second thread finishes the execution of the code in the brace bracket.

lock(syncObj)
{
	//You code here
}

Whenever you use locks like the code above, a FIFO (first-in-first-out) queue is implicitly created internally; every requests will be queued until the previous request dequeued and finishes its work. SQL Server locking system has such concept as well but it’s more sophisticated. In my this example, lock(){} is equivalent to applying exclusive lock to a resource(syncObj above) in SQL Server.

Shared, Update, and Exclusive locks are the basic locks in SQL Server. When a session holds a Shared lock on a resource (for instance, record) , other sessions can have Shared or Update lock granted on the resource. Since there is no conflicts, waiting is not needed. However the request of exclusive locks on the same resource will be blocked until incompatible locks are released. If a resource already has exclusive lock granted, other sessions can request Shared or Update lock but will be blocked.

What would happen if a Shared lock is granted on a resource from one session, an exclusive lock waiting on the shared lock on the same resource from different session, and another session is requesting a shared lock on the same resource? The last session will wait until the blocking session that acquiring exclusive lock to be released even the lock requesting from last session is compatible with the lock held by the first session because there is an incompatible block waiting in the middle. What’s the reason of doing that? In a busy read intensive system with occasional writing activities, shared locks are constantly hold. If a compatible lock always takes the priority, there is a chance that the session requesting incompatible locks will be blocked for ever. Implementing such queuing system can prevent locks from starving.
The is only one exception. When user connects to a database, a shared lock is granted to the database to prevent the database from being dropped. If there is a session trying drop the database, the session will try to acquire a exclusive lock on the database. However, a shared lock has been granted to the database preventing it from happening, so the session dropping the database will wait. The fact different the regular locking behaviors is that the waiting exclusive lock will NOT prevent other sessions from acquiring Shared lock on the database. Aha, the session trying to drop the database is experiencing lock starvation. SQL Server use timeout to resolve it.
You can get a bigger lock compapatibility matrix as shown on right from MS website. In this matrix,

  • NL is no lock, which means it don’t conflict with other locks.
  • SCH-S is schema stability lock, you can treat it as a shared lock on the schema of table. This lock will be granted whenever a session reads data from a table even it uses (nolock) hint.
  • SCH-M is schema modification lock, you can treat it as an exclusive lock over the schema of table. This lock is used when a table is modified. Since the infomation of table schema such as column definitions is saved in system tables. While modifying the definition of the table, IX lock and X lock will be applied to the rows in system tables and pages in addition to the SCH-M lock on the table.
  • S, U, X, IS, IU, and IX locks are explained before. Intent locks are only applied to the resource higher than the row level in the hierarchy
  • BU locks are used for performing bulk load operation. It’s compatible with itself. This means that multiple bulk loading processes can be run at the same time to load the data to the same table, which makes parallel data loading becomes possible.
  • SIX, UIX, and SIU, they are composit lock. Let’s take SIX lock as an example. If a S lock has been applied to a resource, within the same scope (session or transaction or other scopes), an IX lock requested subsequentially, then you will get SIX lock. Vice versa, if an IX lock is hold and a S lock comes in within the same scope, a composit lock SIX will be granted on the resource. Now you should know the rest of the composit locks: UIX–>U + IX, SIU–>S + IU.
  • Rx-xx locks are range locks. I will show them in my demos in next post.

Locks are convertable within the same scope, for instance, a shared lock is on a row, any subsequent X lock request will convert the existing Shared lock to eXclusive lock on the same resource within the same scope. If an exclusive lock is granted before a shared lock requested, subsequent shared lock request will be converted to exclusive lock on the same resource. This conversion is not revertable. Count of reference will be increased. If a convertion can’t take place, such as S and IX, composit locks (SIX) will be generated. The matrix I draw on right is not a full matrix. Locks there are supported locks by sp_getapplock.
The next topic I want to walk you through is Update lock. Update lock is mainly for DML Update and Delete. Both operation needs to perform searching on a table to position the record then execute the moditication. A row has to be read and checked by the engine to determine if it’s the one to be modified. While processing, any modification should not be taken place by other sessioins. 3 type of locks can be utilized for searching operation, X, S, and U.
First of all, can an exclusive lock be used for searching a record? Answer is no. Because once a record is entered (into the screen), other sessions are not able to access it until it’s unlocked even though the record is not being modified, which will generate too many blockings. Therefore it should not be used — concurrency issue.
Secondly, let’s check if it’s feasible to use Shared lock to perform the searching. It seems ok because while a record is being entered, other session can still access that record. If the record is the record to be modified, the lock can be escalated from Shared lock to eXclusive lock. But it doesn’t work well in one scenario, . Let’s say 2 sessions running the same update statement to update the same record at the same time. While the first update statement uses Shared lock and reaches the record, the second update use the same way positions the same record. At this moment, there is no conflict — a records has Shared lock from 2 different sessions. Now the first session tries to convert the Shared lock to eXclusive lock. A blocking happens due to the Shared lock holding by Session 2. Thus the session 1 waits the Shared lock to be released. Now the session 2 finds out the record is the one it needs, then trying to escalates the lock to an eXclusive lock for modification. But it will wait because a Shared lock from session 1 is on the row. Now both sessions are waiting for each other and deadlock each other.
Finally, let’s see how update lock helps the scenario I just described. Session 1 finds the records by using Update lock. Any records are touched by Session 1 will not generate any concurrency issue to any read only operation because Update lock is compatible with Shared lock. At the moment Session 1 positions the record to be updated, an Update lock is on the row. Session 2 use the same way reaches the record with Update lock hold by Session 1. Session 2 has to wait since Update lock conflicts with Update locks. Session 1 then turns the Update lock into Exclusive lock and performs modification. Session 2 will be still waiting until modification by Session 1 complete. Once it’s finished, Session 2 is able to access newly updated record.
Thanks for Update locks.

Locking and Blocking (4) – How It Works

You May Also Like

4 thoughts on “Locking and Blocking (4) – How It Works

  1. I don’t think it is completely true.All isolation levels except serializable will allow the behavior you explained above because in other isolation levels IX locks are placed on the resource allowing other transactions to take shared locks while in serializable X locks are placed on the resource which prevents taking shared locks by other transactions.

    1. All isolation levels except snapshot related ones are implemented by locks. Under serializable isolation level, if there is no index, a full lock will be on the table. When there’re related indexes, range locks might be used.

  2. Hi, I am looking to set DeadLock_Priority for a connection pool via sqlConnection. Could you help me with the parameters please?
    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.