SQL Server supports acquires different locking modes depends on the type of operation we perform on the Data. The list of locking modes acquired by SQL Sever for each type of operation is given below.
- Acquired for reading data.
- Other transactions can acquire shared lock on the same resources.
- No other transaction can modify data.
- Acquired to modify data (INSERT, DELETE and MODIFY).
- No other transaction can modify or read data.
- Acquired to execute a data modification operation but first needs to search the table.
- No other transaction can acquire an update lock or an exclusive lock.
- Modification lock - Acquired for DDL queries.
- Stability lock – Acquired when compiling queries.
Bulk Update Lock
- Acquired for performing bulk copy of data