The following table describes the three different types of locks that SQL Server uses:
| Lock Type | Description |
|---|---|
| Shared | SQL Server usually uses shared locks for operations that do not change or update data, such as a SELECT statement. |
| Update | SQL Server uses update locks when it intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes. |
| Exclusive | SQL Server uses exclusive locks for the data modification operations UPDATE, INSERT, or DELETE. |
These are the different types of items that can be locked by SQL Server:
| Item | Description |
|---|---|
| Page | A 2K data page or index page. This is the most common type of lock. |
| Extent | A contiguous group of 8 2K data pages or index pages. This lock is only used for allocation. |
| Table | An entire table, including all data and indexes. |
| Intent | A special type of table lock to indicate the type of page locks currently placed on the table. |
This table describes the compatibility of different types of page locks with each other:
| Shared | Update | Exclusive | |
|---|---|---|---|
| Shared | Yes | Yes | No |
| Update | Yes | No | No |
| Exclusive | No | No | No |
An entry of "Yes" means that these locks are compatible. An entry of "No" means these locks are incompatible. While a particular type of lock is held on a specific page, only compatible lock types can be placed on that same page.
For example, while an exclusive lock is held, no other transaction can acquire a lock of any kind (shared, update, or exclusive) on that item until the exclusive lock is released at the end of the first transaction.
If a shared lock has been applied to an item, other transactions can also acquire a shared lock or an update lock on that item ¾ even if the first transaction hasn't completed. However, other transactions cannot acquire an exclusive lock until the shared lock has been released.