Lock Modes in SQL Server Part 2

Lock Modes in SQL Server ensures the concurrency and data integrity for multiple users. This is essential because we want all users to be able to use the database without any delays. Every thing to know about Levels of Locking in SQL Server read below link
Read More : Locking in SQL Server Part 1 

Lock Modes in SQL Server

Following are the Lock Modes in SQL Server which that can engage with the resources :

  • Exclusive (X)
  • Shared (S)
  • Update (U)

Exclusive lock (X)

This lock type, when imposed, will ensure that a page or row will be reserved exclusively for the transaction that imposed the exclusive lock, as long as the transaction holds the lock. The exclusive lock will be imposed by the transaction when it wants to modify the page or row data, which is in the case of DML statements DELETE, INSERT and UPDATE. An exclusive lock can take place to a page or row only if there is no other share or exclusive lock imposed already on the target. This practically means that only one exclusive lock can be excise to a page or row, and once it is there , no other lock can be imposed on locked resources

Shared lock (S) 

This lock type, when excise, will reserve a page or row to be available only for reading, which means that any other transaction will be prevented to modify the locked record as long as the lock is active . However, a shared lock can be imposed by several transactions at the same time over the same page or row. This way several transactions can share the ability for data reading since the reading process itself will not affect anyhow the actual page or row data. In addition, a shared lock will allow write operations, but no DDL changes will be allowed

Update lock (U)

This lock is similar to an exclusive lock but it is more flexible in a way. An update lock can lock on a record that already has a shared lock. In such a case, the update lock will impose another shared lock on the target row. Once the transaction that holds the update lock is ready to change the data, the update lock (U) will go to a transformation into an exclusive lock (X). It is important to understand that update lock is asymmetrical in regards of shared locks. While the update lock can be excise on a record that has the shared lock, the shared lock cannot be excise on the record that already has the update lock.

Intent locks (I)

This lock is a means used by a transaction to inform another transaction about its intention to acquire a lock. The purpose of such lock is to ensure data modification to executed properly by preventing another transaction to acquire a lock on the next in hierarchy object. In practice, when a transaction wants to acquire a lock on the row, it will acquire an intent lock on a table, which is a higher hierarchy object. By acquiring the intent lock, the transaction will not allow other transactions to acquire the exclusive lock on that table (otherwise, exclusive lock imposed by some other transaction would cancel the row lock).

This is an important lock type from the performance aspect as the SQL Server database engine . Because, this will inspect intent locks only at the table level to check if it is possible for transaction to acquire a lock in a safe manner in that table. Therefore , intent lock eliminates need to inspect each row/page lock in a table to make sure that transaction can acquire lock on entire table

These are all the basics of Lock Modes in SQL Server . See you with next article.

Leave a Reply Cancel reply