Different types of locks in SQL Server
What are Different Types of Locks?
- Shared Locks: Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
- Update Locks: Used on resources that can be updated. It prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
- Exclusive Locks: Used for data-modification operations, such as INSERT, UPDATE, or DELETE. It ensures that multiple updates cannot be made to the same resource at the same time.
- Intent Locks: Used to establish a lock hierarchy. The types of intent locks are as follows: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
- Schema Locks: Used when an operation dependent on the schema of a table is executing. The types of schema locks are schema modification (Sch-M) and schema stability (Sch-S).
- Bulk Update Locks: Used when bulk-copying data into a table and the TABLOCK hint is specified.