Different Types of Lock in SQL Server

SQL Server has different types of Locks which are being used at different situations. Let’s see the definition of each lock.

1)   SHARED Lock - This lock is used during READ operation where the data is not updated. Example is SELECT statement.

2)   UPDATE Lock – It is locked on those resources that can be updated. This lock prevents the common form of dead lock that occurs when multiple sessions are locking the data so that they can update it later.

3)   EXCLUSIVE Lock – It is used for data-modification operations, such as INSERT, UPDATE, or DELETE. It also ensures that multiple updates cannot be made to the same resource at the same time.

4)   INTENT Lock – It is used to establish a lock hierarchy. The different types of intent locks are

a.   Intent shared

b.   Intent exclusive

c.    Shared with intent exclusive

5)   SCHEMA Lock – It is used when an operation dependent on the schema of a table is executing. There are two types of SCHMEA locks which are

a.   Schema modification

b.   Schema stability

6)   BULK UPDATE Lock – This lock is applied when there is a bulk copying of data and the TABLOCK is applied.

7)   KEY RANGE Lock – It protects the range of rows read by a query when using the serializable transaction isolation level. It ensures that other transactions cannot insert rows on range of values that KEY RANGE locks evaluate, till the end of transaction.