- Allow concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared (S) locks exist on the resource. It is used for READ-ONLY operations.
- The lock is released as soon as the read operation completes (except the case when isolation level is repeatable reads (or higher) or hints are used.
- It doesn’t prevent other read-only queries from accessing the data simultaneously because the integrity of the data isn’t compromised by the concurrent reads.
- Indicates that the data is read for modification (associated with an UPDATE statement).
- Only one transaction can acquire an update lock at a time.
- Instead of acquiring an exclusive right while reading the data, the data to be modified is read and an (U) lock is acquired on the data. When data is modified, the (U) lock is converted to an exclusive lock for modification. If no modification is required, then the (U) lock is released.
|Exclusive||X||- Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.|
|Intent||IS IX IU SIX SIU UIX||- The intent lock shows the future intention of the lock manager to acquire locks on a specific unit of data for a particular transaction.
- If the intent locks were not used, then a transaction trying to acquire a lock at a higher level would have to scan through the lower levels to detect the presence of lower level locks.
|Schema modification||Sch-M||Used when a DDL or DML operation is performed.|
|Schema stability||Sch-S||Used when queries are being compiled. Other transactinal locks are not blocked (S, U, X), but a Sch-M lock cannot be acquired.|
|Bulk Update||Used when you are bulk copying data into a table. Allow multiple threads to copy data concurrently into the same table while preventing access to that table by any other process.|
|Key-Range||Lock index rows in case of serializable transactions. No rows whose key falls withing the range of the locked index keys can be inserted, updated or deleted.
RangeS-S - Shared range, shared resource lock; serializable range scan.
RangeS-U - Shared range, update resource lock; serializable update scan.
RangeI-N - Insert range, null resource lock; used to test ranges before inserting a new key into an index.
RangeX-X - Exclusive range, exclusive resource lock; used when updating a key in a range.