A mechanism in Microsoft SQL Server that protects a database against data loss when users simultaneously attempt to modify the same database object. Locking synchronizes users access to the database and prevents concurrent data manipulation problems to ensure that data remains consistent and query results are correct.
Locking provides concurrency in a multiuser environment - that is, it enables multiple clients to simultaneously access and modify a database without the danger of the data becoming corrupted.
If one user locks a portion of the database to view or modify data, that data cannot be accessed or modified by any other user until the first user’s updates have been committed.
SQL Server version 7 uses multigranular locking, in which each database resource is locked at a level appropriate for that kind of resource. The following table shows the various database resources that can be locked in SQL Server 7, in order of decreasing granularity.
This range of granularity allows a balance between concurrency (the ability of multiple clients to simultaneously access a database) and performance (speed).
For example, highly granular locking such as row-level locking allows more concurrency (different users can simultaneously modify different rows in the same database table), but this increases system overhead because the server must manage more locks.
| Locked Resource | Description |
| DB | Locks the entire database |
| Table | Locks an entire database table, including its data and indexes |
| Extent | Locks a contiguous group of eight data pages or eight index pages |
| Page | Locks individual 8-KB data pages or index pages |
| Key | Locks a row within an index |
| RID (row identifier) | Locks individual rows in a table |
SQL Server uses a number of resource lock modes that specify how different database resources can be accessed by concurrent transactions. These include the following:
Other locking modes include update locks, bulk update locks, and intent locks.