Types of Locks
SQL Server uses locks to provide concurrency while maintaining data consistency and integrity. Whenever you read data, SQL Server locks the data while reading, to prevent other connections from modifying provide the data at the same time.
If you modify data, SQL Server locks that data as long as the transaction lasts to avoid dirty reads in other connections. These locks are called exclusive locks, and they are incompatible with any other locks.
SQL Server uses other types of locks to prevent specific concurrency problems and to guarantee the consistency of internal operations.
Locks are maintained as long as they are necessary, depending on the isolation level selected. It is the programmer's responsibility to design short transactions that execute quickly and do not lock the data for a long time.
If a connection is trying to get a lock in a resource that is locked by another connection, the connection has to wait until the resource is free or is locked in a compatible mode to continue the execution. SQL Server does not produce any error message when a connection is being blocked by another transaction. Usually, this blocking disappears shortly and the transaction continues.
If a connection is blocked for a longer time than the query timeout specified in the client database access library, the application can determine whether the transaction should continue or should be aborted, perhaps with the user's consent. In this case, it is the responsibility of the client application to search for the reasons for this timeout, because SQL Server does not report specifically the existence of any blocking situation.
If necessary, SQL Server can lock any of the following resources:
A row in a table, or RID.
A key in an index page.
A page in a table.
A page in an index.
An extent in a database. SQL Server locks extents during the process of allocation and deallocation of new extents and pages.
An entire table.
An index.
A database. SQL Server always grants a shared lock on a database to the process that connects to a database. This is helpful to detect whether users are connected to a database before trying intrusive actions, such as dropping or restoring the database.
In some cases, SQL Server decides to escalate locks to an upper level to keep locking costs at an appropriate level. By using locking hints (PAGLOCK, ROWLOCK, TABLOCK, TABLOCKX), you can suggest that Query Optimizer use locks at a certain level.
You can use the sp_lock system stored procedure to list the locks currently existing in the server. Specifying the SPID (system process ID) of an existing process when calling sp_lock, you will retrieve the list of locks that belong to a specific process. Listing 13.18 shows how to use sp_lock to get information about locks.
Code Listing 13.18. Use sp_lock to Get Information About Lockings
|
USE Northwind
GO
BEGIN TRAN
DECLARE @UP money
-- Read and lock the data to prevent updates
SELECT @UP = AVG(UnitPrice)
FROM [Order Details] (REPEATABLEREAD)
WHERE ProductID = 37
-- Update the Product 37
UPDATE Products
SET UnitPrice = @UP
WHERE ProductID = 37
-- List locks server wide
EXECUTE sp_lock
-- List locks for this connection only
EXECUTE sp_lock @@SPID
-- Cancel changes
ROLLBACK TRAN
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
52 8 0 0 DB S GRANT
53 6 0 0 DB S GRANT
54 6 0 0 DB S GRANT
56 6 0 0 DB S GRANT
56 6 325576198 4 KEY (84006690ff8d) S GRANT
56 6 117575457 0 TAB IX GRANT
56 6 325576198 1 KEY (cd0058f4cb88) S GRANT
56 6 325576198 1 KEY (fb00d3668ea6) S GRANT
56 6 117575457 1 KEY (2500ef7f5749) X GRANT
56 6 325576198 1 PAG 1:148 IS GRANT
56 6 325576198 1 KEY (4000a82ec576) S GRANT
56 6 325576198 1 PAG 1:182 IS GRANT
56 6 325576198 1 PAG 1:181 IS GRANT
56 6 325576198 4 PAG 1:198 IS GRANT
56 6 325576198 1 PAG 1:200 IS GRANT
56 6 325576198 1 PAG 1:208 IS GRANT
56 6 325576198 4 KEY (38008da3b95f) S GRANT
56 6 117575457 1 PAG 1:276 IX GRANT
56 6 325576198 4 KEY (86009acb8f9e) S GRANT
56 6 325576198 1 KEY (840022f8faea) S GRANT
56 6 325576198 0 TAB IS GRANT
56 6 325576198 4 KEY (cd00166aca95) S GRANT
56 1 85575343 0 TAB IS GRANT
56 6 325576198 4 KEY (fb00d769ae1d) S GRANT
56 6 325576198 1 KEY (860044a822ed) S GRANT
56 6 325576198 1 KEY (3800a93e5703) S GRANT
56 6 325576198 4 KEY (400055e1cf9b) S GRANT
57 6 0 0 DB S GRANT
58 8 0 0 DB S GRANT
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
56 6 0 0 DB S GRANT
56 6 325576198 4 KEY (84006690ff8d) S GRANT
56 6 117575457 0 TAB IX GRANT
56 6 325576198 1 KEY (cd0058f4cb88) S GRANT
56 6 325576198 1 KEY (fb00d3668ea6) S GRANT
56 6 117575457 1 KEY (2500ef7f5749) X GRANT
56 6 325576198 1 PAG 1:148 IS GRANT
56 6 325576198 1 KEY (4000a82ec576) S GRANT
56 6 325576198 1 PAG 1:182 IS GRANT
56 6 325576198 1 PAG 1:181 IS GRANT
56 6 325576198 4 PAG 1:198 IS GRANT
56 6 325576198 1 PAG 1:200 IS GRANT
56 6 325576198 1 PAG 1:208 IS GRANT
56 6 325576198 4 KEY (38008da3b95f) S GRANT
56 6 117575457 1 PAG 1:276 IX GRANT
56 6 325576198 4 KEY (86009acb8f9e) S GRANT
56 6 325576198 1 KEY (840022f8faea) S GRANT
56 6 325576198 0 TAB IS GRANT
56 6 325576198 4 KEY (cd00166aca95) S GRANT
56 1 85575343 0 TAB IS GRANT
56 6 325576198 4 KEY (fb00d769ae1d) S GRANT
56 6 325576198 1 KEY (860044a822ed) S GRANT
56 6 325576198 1 KEY (3800a93e5703) S GRANT
56 6 325576198 4 KEY (400055e1cf9b) S GRANT
Using the SET LOCK_TIMEOUT statement, you can specify the number of milliseconds that a session will wait for any lock to be released before reporting a timeout error in the current session, independently of the query timeout defined in the client application. Use the @@TIMEOUT system function to get the current lock timeout value, which can be
@@TIMEOUT = 0—SQL Server cancels the query as soon as it detects a blocking situation, sending the error 1222 "Lock request time out period exceeded."
@@TIMEOUT = -1—SQL Server waits until the block disappears.
@@TIMEOUT > 0—Indicates the number of milliseconds that SQL Server will wait before sending the error 1222.
When the lock timeout occurs, the batch is cancelled and the execution goes to the next batch. Listing 13.19 shows an example of LOCK_TIMEOUT for two connections.
Code Listing 13.19. Use LOCK_TIMEOUT to Control How Much Time a Connection Will Wait for Locks to Be Released
|
-- Connection A
USE Northwind
GO
BEGIN TRANSACTION
SELECT *
FROM Products (HOLDLOCK)
-- Note that we do not terminate the transaction.
---------------
-- Connection B
---------------
USE Northwind
GO
-- Specify 2000 milliseconds (2 seconds) as lock timeout
SET LOCK_TIMEOUT 2000
GO
UPDATE Products
SET UnitPrice = UnitPrice * 0.9
GO
IF @@ERROR = 1222
PRINT CHAR(10) + 'Lock Timeout produced'+ CHAR(10)
SELECT GetDate() as Now
Server: Msg 1222, Level 16, State 1, Line 1
Lock request time out period exceeded.
Lock Timeout produced
Now
------------------------------------------------------
2000-12-11 00:55:48.210
SQL Server uses the following lock types:
Shared locks
Exclusive locks
Update locks
Intent locks
Schema locks
Bulk Update locks
Note
Bulk Update locks are used to prevent other connections from using a table while importing data in the table using the TABLOCK hint or setting the Table lock on Bulk Load option for the target table with the sp_tableoption system stored procedure.
SQL Server can grant a lock in a resource depending on other existing locks. Figure 13.6 shows the compatibility between locks.
Figure 13.6. This table shows the compatibility between locks.
Caution
The locks produced in a process do not affect any statement in the same process, so it is impossible to get blocked by any action performed in the same process.
A transaction is not affected by its own locks.
Shared Locks
During the process of reading data, SQL Server uses shared locks (S) to prevent changes to the data from other processes. However, shared locks do not prevent reading operations from other connections. SQL Server releases shared locks as soon as possible, unless the reading operation is inside a user-defined transaction with an isolation level of REPEATABLE READ or SERIALIZABLE.
If a query reads many rows from a table, in a transaction with the default isolation level, only portions of that table are locked at any given time. In this case, as soon as the statement terminates, the shared locks are released without waiting to finish the transaction. Use the HOLDLOCK lock hint to maintain these shared locks until the end of the transaction.
Note
Any DML statement (SELECT, UPDATE, INSERT, or DELETE) can produce shared locks, as long as these statements are reading data from one or more tables.
Listing 13.20 shows how, after executing a SELECT statement, locks are released; however, after the INSERT statement, you can see shared locks in the Products and Orders tables, because you use them for the HOLDLOCK and REPEATABLEREAD lock hints.
Code Listing 13.20. Examples of Shared Locks
|
USE Northwind
GO
-- Start a transaction
BEGIN TRAN
-- Get OBJECT_IDs to interpret the sp_lock output
SELECT OBJECT_ID('Order details') as 'Order details'
SELECT OBJECT_ID('Orders') as 'Orders'
SELECT OBJECT_ID('Products') as 'Products'
-- Get current locks to use as a baseline to further executions of sp_lock
PRINT 'Initial lock status'+ CHAR(10)
EXEC sp_lock
-- Execute a standard SELECT statement
SELECT MAX(OrderID)
FROM Orders
WHERE CustomerID = 'ALFKI'
-- Get locks status after the SELECT statement
PRINT CHAR(10) + 'Lock status after SELECT'+ CHAR(10)
EXEC sp_lock
-- Perform an INSERT statement in Order details,
-- reading from Orders and Products
INSERT [Order Details]
(OrderID, ProductID, Unitprice, Quantity, Discount)
SELECT
(SELECT MAX(OrderID)
FROM Orders (HOLDLOCK)
WHERE CustomerID = 'ALFKI'),
ProductID, UnitPrice, 10, 0.1
FROM Products (REPEATABLEREAD)
WHERE ProductName = 'Tofu'
-- Get locks status after the INSERT statement
PRINT CHAR(10) + 'Lock status after INSERT'+ CHAR(10)
EXEC sp_lock
ROLLBACK TRAN
-- Get locks status after the ROLLBACK statement
PRINT CHAR(10) + 'Lock status after ROLLBACK'+ CHAR(10)
EXEC sp_lock
Order details
-------------
325576198
(1 row(s) affected)
Orders
-----------
21575115
(1 row(s) affected)
Products
-----------
117575457
(1 row(s) affected)
Initial lock status
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 1 85575343 0 TAB IS GRANT
55 6 0 0 DB S GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
-----------
11011
(1 row(s) affected)
Lock status after SELECT
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 1 85575343 0 TAB IS GRANT
55 6 0 0 DB S GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
(1 row(s) affected)
Lock status after INSERT
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 6 117575457 0 TAB IS GRANT
55 6 0 0 DB S GRANT
55 6 325576198 5 PAG 1:189 IX GRANT
55 6 325576198 4 PAG 1:187 IX GRANT
55 6 325576198 3 PAG 1:202 IX GRANT
55 6 325576198 2 PAG 1:201 IX GRANT
55 6 325576198 1 PAG 1:211 IX GRANT
55 6 21575115 3 PAG 1:248 IS GRANT
55 6 21575115 3 KEY (d300d99e322e) RangeS-S GRANT
55 6 117575457 1 PAG 1:276 IS GRANT
55 6 117575457 4 PAG 1:281 IS GRANT
55 6 21575115 0 TAB IS GRANT
55 6 325576198 1 KEY (1100203d3739) X GRANT
55 6 325576198 2 KEY (1100203d3739) X GRANT
55 6 325576198 3 KEY (1100203d3739) X GRANT
55 6 325576198 0 TAB IX GRANT
55 6 21575115 3 KEY (18013d14dae8) RangeS-S GRANT
55 6 117575457 4 KEY (c80063450f36) S GRANT
55 1 85575343 0 TAB IS GRANT
55 6 325576198 5 KEY (11004b34faa9) X GRANT
55 6 325576198 4 KEY (11004b34faa9) X GRANT
55 6 117575457 1 KEY (0e00d057643e) S GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
Lock status after ROLLBACK
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 1 85575343 0 TAB IS GRANT
55 6 0 0 DB S GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
Exclusive Locks
SQL Server uses exclusive locks (X) to protect the modified data from other processes until the transaction terminates. This lock mode is incompatible with any other lock. In the preceding section, you can see in Listing 13.20 how an INSERT statement produces exclusive locks.
SQL Server creates exclusive locks when necessary, regardless of the selected isolation level. Inserted, deleted, or updated resources are always locked exclusively until the transaction terminates.
You can still see these resources from other processes using the READ UNCOMMITTED isolation level or the NOLOCK locking hint.
Tip
You can use mock updates to produce an exclusive lock in a row without modifying it. To SQL Server, you have modified the data, even if the new values are the same as the old ones, as in Listing 13.21.
Code Listing 13.21. Modifying Data Produces Exclusive Locks
|
-- Start a transaction
BEGIN TRAN
-- Get OBJECT_ID to interpret the sp_lock output
SELECT OBJECT_ID('Products') as 'Products'
-- Get current locks to use as a baseline to further executions of sp_lock
PRINT 'Initial lock status'+ CHAR(10)
EXEC sp_lock
-- Execute a mock UPDATE statement
UPDATE Products
SET UnitPrice = UnitPrice
WHERE ProductID = 28
-- Get locks status after the mock UPDATE statement
PRINT CHAR(10) + 'Lock status after the mock UPDATE'+ CHAR(10)
EXEC sp_lock
-- Execute a real UPDATE statement
UPDATE Products
SET UnitPrice = UnitPrice * 1.5
WHERE ProductID = 35
-- Get locks status after the real UPDATE statement
PRINT CHAR(10) + 'Lock status after the real UPDATE'+ CHAR(10)
EXEC sp_lock
ROLLBACK TRAN
Products
-----------
117575457
(1 row(s) affected)
Initial lock status
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 1 85575343 0 TAB IS GRANT
55 6 0 0 DB S GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
(1 row(s) affected)
Lock status after the mock UPDATE
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 6 117575457 0 TAB IX GRANT
55 6 0 0 DB S GRANT
55 6 117575457 1 PAG 1:276 IX GRANT
55 1 85575343 0 TAB IS GRANT
55 6 117575457 1 KEY (1c00c4c874c4) X GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
(1 row(s) affected)
Lock status after the real UPDATE
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 6 117575457 0 TAB IX GRANT
55 6 0 0 DB S GRANT
55 6 117575457 1 KEY (230033203c6c) X GRANT
55 6 117575457 1 PAG 1:276 IX GRANT
55 1 85575343 0 TAB IS GRANT
55 6 117575457 1 KEY (1c00c4c874c4) X GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
Note
Modifying a row in a table with no clustered index produces a RID (Row ID) lock. If the table has a clustered index, the index leaf level is the collection of data pages; this is why the row lock is considered a key lock in the sp_lock output.
Update Locks
Update locks are an intermediate state between a shared lock and an exclusive lock. You use an update lock when you are reading some data with intentions of modifying the same data. To prevent modifications from other connections, you could lock the data exclusively, but in that case, you are preventing readings from other connections. Using update locks prevents undesired modifications, while allowing read access to the data from other connections.
If you used only a shared lock in the resource lock, and other connection gets another shared lock in the same resource, you can get a deadlock situation if both connections tried to convert their shared lock into exclusive locks.
In Listing 13.22, you can see an example of update locks. To execute this example, you must establish two connections to SQL Server:
Execute the first part of the script in Connection A, which reads a row from the Products table, using the UPDLOCK locking hint.
Execute an UPDATE statement from Connection A. Because this is the connection that holds the update lock, it is possible to update the data directly. In this case, the update lock is converted into an exclusive lock.
Connection B is still waiting because the existing exclusive lock is incompatible with the requested exclusive lock.
Code Listing 13.22. An Example of How to Use UPDLOCK
|
USE Northwind
GO
-- Start a transaction
BEGIN TRAN
-- Get OBJECT_ID to interpret the sp_lock output
SELECT OBJECT_ID('Products') as 'Products'
-- Get current locks to use as a baseline to further executions of sp_lock
PRINT 'Initial lock status'+ CHAR(10)
EXEC sp_lock
SELECT ProductID, ProductName, UnitPrice
FROM Products (UPDLOCK)
WHERE ProductID = 28
PRINT CHAR(10) + 'lock status after SELECT'+ CHAR(10)
EXEC sp_lock
-- Change to connection B and execute:
/*
UPDATE Products
SET UnitPrice = UnitPrice
WHERE ProductID = 28
*/
-- Change to connection A and execute:
PRINT CHAR(10) + 'lock status after UPDATE from Connection B'+ CHAR(10)
EXEC sp_lock
-- Execute a mock UPDATE statement
UPDATE Products
SET UnitPrice = UnitPrice
WHERE ProductID = 28
-- Get locks status after the mock UPDATE statement
PRINT CHAR(10) + 'Lock status after the mock UPDATE'+ CHAR(10)
EXEC sp_lock
ROLLBACK TRAN
Products
-----------
117575457
(1 row(s) affected)
Initial lock status
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 6 0 0 DB S GRANT
55 1 85575343 0 TAB IS GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
58 6 0 0 DB S GRANT
ProductID ProductName UnitPrice
----------- ---------------------------------------- ---------------------
28 Rössle Sauerkraut 45.6000
lock status after SELECT
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 6 0 0 DB S GRANT
55 6 117575457 0 TAB IX GRANT
55 6 117575457 1 PAG 1:276 IU GRANT
55 1 85575343 0 TAB IS GRANT
55 6 117575457 1 KEY (1c00c4c874c4) U GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
58 6 0 0 DB S GRANT
lock status after UPDATE from Connection B
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 6 0 0 DB S GRANT
55 6 117575457 0 TAB IX GRANT
55 6 117575457 1 PAG 1:276 IU GRANT
55 1 85575343 0 TAB IS GRANT
55 6 117575457 1 KEY (1c00c4c874c4) U GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
58 6 0 0 DB S GRANT
58 6 117575457 1 KEY (1c00c4c874c4) X WAIT
58 6 117575457 1 PAG 1:276 IX GRANT
58 6 117575457 0 TAB IX GRANT
Lock status after the mock UPDATE
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 6 0 0 DB S GRANT
55 6 117575457 0 TAB IX GRANT
55 6 117575457 1 PAG 1:276 IX GRANT
55 1 85575343 0 TAB IS GRANT
55 6 117575457 1 KEY (1c00c4c874c4) X GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
58 6 0 0 DB S GRANT
58 6 117575457 1 KEY (1c00c4c874c4) X WAIT
58 6 117575457 1 PAG 1:276 IX GRANT
58 6 117575457 0 TAB IX GRANT
Intent Locks
SQL Server can lock resources at different hierarchical levels: rows, pages, extents, tables, and databases. If one process holds a lock in a row, a different process could get a lock on the page where this row is stored, compromising the lock in the other connection. Another process could get a lock on the table, adding more complexity to this locking situation.
To prevent this situation, SQL Server uses intent locks at a higher level in the hierarchy, preventing incompatible levels from other connections.
If SQL Server grants shared locks at row level to a process, it grants an intent share (IS) lock at page and table level also, protecting the requested shared row lock. Figure 13.7 shows this case.
Figure 13.7. SQL Server uses intent locks to protect granted locks at lower hierarchical levels.
To protect an exclusive (X) row lock, SQL Server grants an intent exclusive (IX) lock at page and table level.
In some cases, a SQL Server transaction requests shared locks on some rows and exclusive locks on other rows. In these cases, SQL Server will produce intent shared (IS) locks to protect the data pages that have only rows with shared locks. However, if a data page has at least one row with an exclusive lock, SQL Server will protect this page with an intent exclusive (IX) lock. Because some pages have intent shared locks and other pages have exclusive shared locks, at the table level SQL Server will use the shared with intent exclusive (SIX) lock.
Schema Locks
When SQL Server receives Data Definition Language (DDL) statements, it locks the object being created or modified with a schema modification lock (Sch-M), as in the example of Listing 13.23.
Code Listing 13.23. SQL Server Uses Schema Modification Locks to Protect Objects During Creation or Modification
|
USE Northwind
GO
BEGIN TRAN
CREATE TABLE TestSchema
(ID int)
-- Show names of the objects 1, 2 and 3 of Northwind,
-- because they are used in sp_lock
SELECT object_name(1) AS ID1
SELECT object_name(2) AS ID2
SELECT object_name(3) AS ID3
-- Show the ID of TestSchema table to identify locks on this table
SELECT Object_ID('TestSchema') AS TestSchemaID
EXEC sp_lock
ROLLBACK TRAN
ID1
-------------
sysobjects
ID2
-------------
sysindexes
ID3
-------------
syscolumns
TestSchemaID
------------
1813581499
(1 row(s) affected)
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 6 0 0 DB S GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
57 6 1 0 TAB IX GRANT
57 6 3 0 TAB IX GRANT
57 6 2 0 TAB IX GRANT
57 6 1 3 KEY (bb006c403485) X GRANT
57 6 2 1 KEY (bb00da29dced) X GRANT
57 6 1813581499 0 TAB Sch-M GRANT
57 6 1 2 KEY (f00169258f34) X GRANT
57 6 3 2 KEY (04018aa462b1) X GRANT
57 1 85575343 0 TAB IS GRANT
57 6 1 1 KEY (bb00194052c1) X GRANT
57 6 3 1 KEY (bc00403fd981) X GRANT
58 6 0 0 DB S GRANT
As you saw in the output of Listing 13.23, there is a schema modification (Sch-M) lock on the table TestSchema and exclusive locks on some rows of the system tables sysobjects, syscolumns, and sysindexes.
During the process of compiling a query plan, SQL Server uses a schema stability (Sch-S) lock on the objects used in the query plan to prevent modifications on their definition that could potentially invalidate the query plan being compiled.
Changes to objects referenced in an existing compiled query plan force the query plan to be invalid, and SQL Server marks the query plan to be recompiled next time it is executed.
Key-Range Locks
SQL Server uses key-range locks to avoid phantom reads. As commented in the "Concurrency Problems" section earlier in this chapter, this is a special case of inconsistent analysis because of the insertion of rows from other processes in the range of rows affected by the process.
Key-range locks help serialize the following operations:
Queries restricted by a range of values, to prevent other rows from being inserted in this range by other processes.
Fetching a nonexistent row, with intentions of inserting the row at a later point in the transaction.
Insert operations.
SQL Server implements range locks by locking keys in an index when you select the SERIALIZABLE isolation level, as you can see in Listing 13.24.
Code Listing 13.24. Use the SERIALIZABLE Locking Hint to Force Range Locks
|
USE Northwind
GO
-- Start a transaction
BEGIN TRAN
-- Get OBJECT_ID to interpret the sp_lock output
SELECT OBJECT_ID('Products') as 'Products'
-- Get current locks to use as a baseline to further executions of sp_lock
PRINT 'Initial lock status'+ CHAR(10)
EXEC sp_lock
SELECT ProductID, ProductName, UnitPrice
FROM Products (SERIALIZABLE)
WHERE CategoryID = 3
PRINT CHAR(10) + 'lock status after SELECT'+ CHAR(10)
EXEC sp_lock
ROLLBACK TRAN
Products
-----------
117575457
(1 row(s) affected)
Initial lock status
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 6 0 0 DB S GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
58 6 0 0 DB S GRANT
61 6 0 0 DB S GRANT
61 1 85575343 0 TAB IS GRANT
62 6 0 0 DB S GRANT
63 6 0 0 DB S GRANT
68 6 0 0 DB S GRANT
69 6 0 0 DB S GRANT
ProductID ProductName UnitPrice
----------- ---------------------------------------- ---------------------
16 Pavlova 17.4500
19 Teatime Chocolate Biscuits 9.2000
20 Sir Rodney's Marmalade 81.0000
21 Sir Rodney's Scones 10.0000
25 NuNuCa Nuß-Nougat-Creme 14.0000
26 Gumbär Gummibärchen 31.2300
27 Schoggi Schokolade 43.9000
47 Zaanse koeken 9.5000
48 Chocolade 12.7500
49 Maxilaku 20.0000
50 Valkoinen suklaa 16.2500
62 Tarte au sucre 49.3000
68 Scottish Longbreads 12.5000
(13 row(s) affected)
lock status after SELECT
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 10 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 6 0 0 DB S GRANT
56 10 0 0 DB S GRANT
57 6 0 0 DB S GRANT
58 6 0 0 DB S GRANT
61 6 0 0 DB S GRANT
61 6 117575457 0 TAB IS GRANT
61 6 117575457 1 KEY (310027bf2c96) S GRANT
61 6 117575457 1 KEY (3200c9109984) S GRANT
61 6 117575457 1 KEY (3e0071af4fce) S GRANT
61 6 117575457 1 KEY (2f008b9fea26) S GRANT
61 6 117575457 2 KEY (1700bde729cb) RangeS-S GRANT
61 6 117575457 2 KEY (1e00ebf74a93) RangeS-S GRANT
61 6 117575457 1 KEY (1b007df0a359) S GRANT
61 6 117575457 1 KEY (14002be0c001) S GRANT
61 6 117575457 1 PAG 1:276 IS GRANT
61 6 117575457 2 PAG 1:277 IS GRANT
61 6 117575457 2 KEY (32001d9803ec) RangeS-S GRANT
61 6 117575457 2 KEY (4100e7a8a604) RangeS-S GRANT
61 6 117575457 2 KEY (3400b1b8c55c) RangeS-S GRANT
61 6 117575457 2 KEY (35005f17704e) RangeS-S GRANT
61 6 117575457 1 PAG 1:360 IS GRANT
61 1 85575343 0 TAB IS GRANT
61 6 117575457 1 KEY (440089efcdca) S GRANT
61 6 117575457 1 KEY (300042d8902e) S GRANT
61 6 117575457 2 KEY (1c00603f4339) RangeS-S GRANT
61 6 117575457 2 KEY (1d008e90f62b) RangeS-S GRANT
61 6 117575457 2 KEY (160004dffe56) RangeS-S GRANT
61 6 117575457 2 KEY (1300ea704b44) RangeS-S GRANT
61 6 117575457 2 KEY (1800d8809573) RangeS-S GRANT
61 6 117575457 1 KEY (15004e877cb9) S GRANT
61 6 117575457 1 KEY (130092d8179c) S GRANT
61 6 117575457 1 KEY (10007c77a28e) S GRANT
61 6 117575457 1 KEY (1900f638aaf3) S GRANT
61 6 117575457 1 KEY (1a0018971fe1) S GRANT
61 6 117575457 2 KEY (3300d4df79e4) RangeS-S GRANT
61 6 117575457 2 KEY (0f006da996c9) RangeS-S GRANT
61 6 117575457 2 KEY (47001fe82400) RangeS-S GRANT
62 6 0 0 DB S GRANT
63 6 0 0 DB S GRANT
68 6 0 0 DB S GRANT
69 6 0 0 DB S GRANT