locking

Definition of locking in The Network Encyclopedia.

What is Locking?

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.

How it works

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.

Database Resources That SQL Server 7 Can Lock

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:

  • Shared locks:
    Allow concurrent transactions to read data - for example, by using transact-SQL SELECT statements. Shared locks allow concurrent reads but lock the resource against modification until the reads are completed. After the data is read, the lock is removed unless a repeatable read is being performed.

     

  • Exclusive locks:
    Lock data so that it can be modified - for example, by using INSERT, DELETE, or UPDATE statements. No other reads or modifications can be performed on the resource while it is exclusively locked.

     

Other locking modes include update locks, bulk update locks, and intent locks.