The sp_lock Example

Now that you know a little about the locks that are available, I’ll step through the sp_lock output from the title update example. Here is the output again:

spid    locktype              table_id      page        dbname
-----   ---------------       ---------     -----       --------
10      Sh_intent             688005482     0           master
10      Ex_intent             192003715     0           pubs
10      Ex_page               192003715     400         pubs
10      Ex_page               192003715     688         pubs
10      Update_page           192003715     400         pubs
10      Ex_extent             0             368         tempdb

The first row in the table is a shared intent lock. If you change to the master database and execute select object_name(688005482), you will find that you, too, have an object going by that ID. This is the spt_values table. In order to retrieve the lock type names, the sp_lock procedure must retrieve the data from this table. Any time you run sp_lock, you will see a shared intent lock on this table.

The next lock is an exclusive intent lock on object 192003715. This object is the titles table in my pubs database. This lock was acquired when I updated the titles table. It says to other connections, “Hey, I’m working here. If you want to work in titles, too, you’re gonna need page locks.” If another connection updated a different page in titles, it would acquire an intent lock and then separate page locks.

The intent lock is followed by two exclusive page locks on the titles table. One of these is on the table itself, and another one is on the index. This information can be obtained by running the DBCC page() command.

---

NOTE:  DBCC stands for Database Consistency Checker. DBCC commands are the exclusive domain of the system administrator. You must be the sa to run them. This book won’t go into DBCC commands at all. If you want details and you are an administrator, try the books online, or run this query:

dbcc traceon(3604) -- Output to terminal
go
dbcc help(“page”) -- Get help on dbcc page

---

The index page is locked because you are changing the value of an indexed column. There is a nonclustered index on the title column. When the value changes, the index must be reorganized to reflect the change.

The update lock is a vestige of the UPDATE statement’s running, between the time I updated and the time I ran sp_lock. It is irrelevant now, because I have an exclusive lock on the page. Exclusive locks are more restrictive—they allow no other locks of any type—so this lock takes precedence over the update lock.

The extent lock in tempdb is a result of the sp_lock procedure, too. The procedure sorts locks according to process ID and requires a worktable in tempdb to do so.

How Do I Affect Locking?

You cause locks by issuing SELECT, INSERT, UPDATE, and DELETE statements. In most cases, locking is automatic.

However, when you are selecting data, you can request that the server hold its shared locks until your transaction completes. By default, shared locks are held only long enough to read the page, even when they occur inside a transaction. This example demonstrates the difference:

begin   tran
select   *   from authors
exec   sp_lock
select   *   from authors holdlock
exec   sp_lock
commit   tran

au_id        au_lname   au_fname …
----------   --------   --------------------
172-32-1176  White      Johnson
213-46-8915  Green      Marjorie
[…]
998-72-3567  Ringer     Albert
(23 row(s) affected)
spid   locktype             table_id    page       dbname
-----  ------------------   ----------  ------     -------
12     Sh_intent            688005482   0          master
12     Ex_extent            0           368        tempdb
(1 row(s) affected)
au_id         au_lname   au_fname …
--------      --------   --------------------
172-32-1176   White      Johnson
213-46-8915   Green      Marjorie
[…]
998-72-3567   Ringer     Albert
(23 row(s) affected)
spid    locktype                 table_id     page       dbname
-----   --------------------     ----------   --------   ------
12      Sh_intent                688005482    0          master
12      Sh_table                 16003088     0          pubs
12      Ex_extent                0            368        tempdb

The first sp_lock showed no SELECT locks on the authors table, because by the time the stored proc ran, all of the shared locks had already come and gone. When I request a HOLDLOCK, the shared locks last until the transaction completes. In this case, because I was reading the entire table, the server gave me a shared table lock on the authors table. The familiar shared intent lock on master..spt_values and the extent lock in tempdb are a result of the sp_lock stored procedure.

HOLDLOCK can be useful inside a transaction when you want to ensure that data you read does not change before your transaction completes. The problem it introduces, though, is that when a shared lock is on a page, no other processes can update it. Those processes are forced to wait, or, in some cases, may abort with a deadlock.