SQL Server Lock Types

What are the lock types?

SQL server supports following locks:
Shared lock
Update lock
Exclusive lock

Shared lock

- Shared Lock allows simultaneous access of record by multiple Select statements.
- Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading.
- If update process is going on then read command will have to wait until updating process finishes.

Update locks

This lock is used with the resources to be updated.

Exclusive locks

This kind of lock is used with data modification operations like update, insert or delete.

What are the lock types?

Main lock types:- Shared: Applied to read only operations where the data is not modified. E.g.: Select statements.
- Update: Applied to resources which can be updated. It resolves dead locks in case of multiple sessions are reading, locking or updating resources later.
- Exclusive: Used for operations involving data modification. E.g.: Insert, Update, and Delete. This ensures that multiple updates are not made to the same data at the same time.
- Intent: Establishes a lock hierarchy. E.g.: Intent shared Intent exclusive and Shared with intentexclusive.
- Schema: Used when schema dependent operations are being executed. E.g.: Schema modification and Schema stability.
- Bulk update: Used while bulk copying of data and Tablock is specified.