Understanding SQL Server Locks
SQL Server uses locks to implement transaction isolation and to ensure the information
stored in a database is consistent. Locks prevent one user from reading or changing a row
that is being changed by another user. For example, when you update a row, a row lock is
placed on that row to prevent another user from updating the row at the same time.
Types of SQL Server Locks
SQL Server uses many types of locks, some of which are shown in Table 14.5. This table
shows the locks in ascending order of locking granularity, which refers to the size of the
resource being locked. For example, a row lock has a finer granularity than a page lock.
Table 14.5: SQL Server Lock Types
LOCK
TYPE
DESCRIPTION
Row (RID) Placed on a row in a table. Stands for row identifier. Used to uniquely
identify a row.
Key (KEY) Placed on a row within an index. Used to protect key ranges in serializable
transactions.
Page (PAG) Placed on a page, which contains 8KB of row or index data.
Extent
(EXT)
Placed on an extent, which is a contiguous group of 8 data or index pages.
Table
(TAB)
Placed on a table and locks all the rows and indexes in that table.
Database
(DB)
Used to lock the whole database when the database administrator puts it
into single user mode for maintenance.
SQL Server Locking Modes
SQL Server uses different locking modes that determine the level of locking placed on the
resource. These locking modes are shown in Table 14.6. You'll see these locking modes
in the next section.
Table 14.6: SQL Server Locking Modes
LOCKING
MODE
DESCRIPTION
Shared (S) Indicates that a transaction is going to read from the resource using a
Table 14.6: SQL Server Locking Modes
LOCKING
MODE
DESCRIPTION
SELECT statement. Prevents other transactions from modifying the
locked resource. A shared lock is released as soon as the data has been
read-unless the transaction isolation level is set to REPEATABLE
READ or SERIALIZABLE.
Update (U) Indicates that a transaction intends to modify a resource using an
INSERT, UPDATE, or DELETE statement. The lock must be
escalated to an exclusive lock before the transaction actually performs
the modification.
Exclusive (X) Allows the transaction to modify the resource using an INSERT,
UPDATE, or DELETE statement. No other transactions can read from
or write to a resource on which an exclusive lock has been placed.
Intent shared
(IS)
Indicates that the transaction intends to place a shared lock on some of
the resources with a finer level of granularity within that resource. For
example, placing an IS lock on a table indicates that the transaction
intends to place a shared lock on some of the pages or rows within that
table. No other transactions may place an exclusive lock on a resource
that already has an IS lock on it.
Intent exclusive
(IX)
Indicates that the transaction intends to place an exclusive lock on a
resource with a finer level of granularity. No other transactions may
place an exclusive lock on a resource that already has an IX lock on it.
Shared with
intent exclusive
(SIX)
Indicates that the transaction intends to read all of the resources that
have a finer level of granularity and modify some of those resources.
For example, placing a SIX lock on a table indicates that the
transaction intends to read all the rows in that table and modify some of
those rows. No other transactions may place an exclusive lock on a
resource that already has a SIX lock on it.
Schema
modification
(Sch-M)
Indicates that a Data Definition Language (DDL) statement is going to
be performed on a schema resource, for example, DROP TABLE. No
other transactions may place a lock on a resource that already has a
Sch-M lock on it.
Schema stability
(Sch-S)
Indicates that a SQL statement that uses the resource is about to be
performed, such as a SELECT statement for example. Other
transactions may place a lock on a resource that already has a Sch-S
lock on it; only a schema modification lock is prevented.
Bulk update
(BU)
Indicates that a bulk copy operation to load rows into a table is to be
performed. A bulk update lock allows other processes to bulk-copy
Table 14.6: SQL Server Locking Modes
LOCKING
MODE
DESCRIPTION
data concurrently into the same table, but prevents other processes that
are not bulk-copying data from accessing the table. For further
information on bulk-copying data to a table, see the SQL Server Books
Online documentation.
Viewing SQL Server Lock Information
You can view the lock information in a database using SQL Server Enterprise Manager.
You open the Management folder, open the Current Activity node, and then open either
the Locks/Process ID node or the Locks/Object node. The Locks/Process ID node shows
you the locks placed by each process; each process has a SPID number that is assigned by
SQL Server to identify the process. The Locks/Object node shows you the locks placed
on each resource by all processes.
Tip You can also view lock information by executing the sp_lock stored procedure,
although Enterprise Manager organizes the information in a more readable format.
Assume you've started the following transaction (using Query Analyzer, for example)
with the following T-SQL statements:
USE Northwind
BEGIN TRANSACTION
UPDATE Customers
SET CompanyName = 'Widgets Inc.'
WHERE CustomerID = 'ALFKI'
This places a shared lock on the Northwind database and a number of locks on the
Customers table, which you can view using Enterprise Manager. Figure 14.3 shows these
locks using the Locks/ Process ID node of Enterprise Manager. The SPID of 51
corresponds to Query Analyzer where I ran the previous T-SQL statements. As you can
see from this figure, a number of locks are placed by the previous T-SQL statements.
Figure 14.3: Viewing the locks using the Locks/ Process ID node of Enterprise Manager
To roll back the previous transaction, perform the following T-SQL statement:
ROLLBACK TRANSACTION
To release the locks, perform the following T-SQL statement:
COMMIT TRANSACTION
The information in the right pane of Figure 14.3 shows the locks, and this information is
divided into the following columns:
• Object The object being locked.
• Lock Type The type of lock, which corresponds to one of the types shown earlier
in Table 14.5.
• Mode The locking mode, which corresponds to one of the modes shown earlier in
Table 14.6.
• Status The lock status, which is either GRANT (lock was successfully granted),
CNVT (lock was converted), or WAIT (waiting for lock).
• Owner The owner type of the lock, which is either Sess (session lock) or Xact
(transaction lock).
• Index The name of the index being locked (if any).
• Resource The resource identifier of the object being locked (if any).
Transaction Blocking
One transaction may block another transaction from obtaining a lock on a resource. For
example, let's say you start a transaction using the following T-SQL, which is identical to
the T-SQL in the previous section:
USE Northwind
BEGIN TRANSACTION
UPDATE Customers
SET CompanyName = 'Widgets Inc.'
WHERE CustomerID = 'ALFKI'
As you saw in the previous section, this places a number of locks on the Customers
object.
If you then attempt to update the same row-without ending the previous transaction-using
the following T-SQL statements:
USE Northwind
UPDATE Customers
SET CompanyName = 'Alfreds Futterkiste'
WHERE CustomerID = 'ALFKI'
then this UPDATE will wait until the first transaction has been committed or rolled back.
Figure 14.4 shows these two transactions being started in Query Analyzer. The first
transaction, which is shown in the upper part of Figure 14.4, is blocking the transaction
on the bottom.
Figure 14.4: The transaction on the top part is blocking the transaction on the bottom.
To commit the previous transaction and release the locks for the first transaction, you
may perform the following T-SQL statement:
COMMIT TRANSACTION