You can set up this deadlock yourself and inspect the locks that are accumulated along the way. Start by getting two connections through ISQL/w. In the first connection, begin a transaction and select a row from publishers like this:

--  From  Connection 1
begin   tran
select  pub_id,  pub_name,  city,  state
  from  publishers
 where pub_id = “0736”

From the other connection, execute sp_lock:

sp_lock

spid   locktype                      table_id    page        dbname
------ ----------------------------------  ----------- ----------- ------
11     Sh_intent                      688005482   0          master
11     Ex_extent                     0            360        tempdb

The locks you see here are the locks required to run sp_lock. I didn’t specify HOLDLOCK, so the shared locks are immediately released, even though the query is run within a transaction. To force them to persist, I’ll run the query again back in the first connection, but I’ll include HOLDLOCK:

-- From  Connection 1
select  pub_id,  pub_name,  city,  state
  from   publishers   holdlock
 where  pub_id  =  “0736”

Now run sp_lock again. Here’s what I got:

spid   locktype                       table_id    page       dbname
------ ----------------------------------  ----------  ----------- ------
10     Sh_intent                      112003430   0           pubs
10     Sh_page                        112003430   376         pubs
11     Sh_intent                      688005482   0          master
11     Ex_extent                      0           360        tempdb

Connection 10 holds two locks, a shared page lock on a table in pubs (that’s the publishers table) and a shared intent lock on the table.

---

Note:  The shared intent lock is a bookmark used to block any UPDATE or DELETE process requiring a page-level lock. It says, “This process holds a page-level lock somewhere in this table.”

---

In your second connection, you can execute the SELECT query also.

--  From  Connection 2
begin   tran
select  pub_id,  pub_name,  city,  state
    from  publishers  holdlock
 where  pub_id  =  “0736”

Run sp_lock from either connection and you’ll get this:

spid   locktype                       table_id    page       dbname
------ ----------------------------------- ----------- ----------- ------
10     Sh_intent                      112003430   0            pubs
10     Sh_page                        112003430   376          pubs
11     Sh_intent                      688005482   0          master
11     Sh_intent                      112003430   0            pubs
11     Sh_page                        112003430   376          pubs
11     Ex_extent                      0           360        tempdb

Both users hold shared locks on the same page of the same table. Those locks will persist until the users commit, roll back, or log out (which automatically rolls back).

Can either user update the data? No. They are both blocked by the other’s lock. Try updating the row from the second connection. We’ll move the publisher to Massachusetts. Don’t worry if the connection seems to hang: that’s the point!

--  From  Connection  2
update    publishers
     set state = “MA”
 where  pub_id  =  “0736”
commit  tran

You should see a spinning globe and no results. How long will you wait? Perpetually. You are waiting for a live lock, and there is no implicit timeout for a live lock.

A live lock is a chain of locks caused by two or more connections. The live lock can be resolved by a programmer releasing a lock and removing the block from another user’s program. SQL Server does not automatically resolve live locks.

Let’s look at the output from sp_lock now. You can’t execute it from connection 2 any more: he’s waiting for his update to complete.

--  From  Connection 1
sp_lock

spid   locktype                        table_id    page      dbname
------ ----------------------------------- ----------- ----------- ------
10     Sh_intent                       688005482   0         master
10     Sh_intent                       112003430   0           pubs
10     Sh_page-blk                     112003430   376         pubs
10     Ex_extent                       0           360       tempdb
11     Ex_intent                       112003430   0           pubs
11     Sh_intent                       112003430   0           pubs
11     Sh_page                         112003430   376         pubs
11     Update_page                     112003430   376         pubs

The attempted update changed some information in sp_lock. First, the second connection (spid 11) has added two new locks. First, there’s an Ex_intent lock on the table (exclusive intent locks indicate an intention to update somewhere in the table and block table-level shared locks). Second, there’s an update lock on the same page (update locks indicate the need for an exclusive lock on a page in a table). Connection 1 (spid 10) has modified its shared page lock to a blocking lock (Sh_page-blk); this lock is preventing some other user from proceeding.

To learn more about blocking chains, you need to run sp_who.

sp_who

spid   status   loginame   hostname     blk  dbname     cmd
------ -------- ---------- ------------ -------------- ----------------
1      sleeping sa                      0    master     MIRROR HANDLER
2      sleeping sa                      0    master     LAZY WRITER
3      sleeping sa                      0    master     CHECKPOINT SLEEP
4      sleeping sa                      0    master     RA MANAGER
10     runnable sa         SYS999       0    pubs       SELECT
11     sleeping sa         SYS999       10   pubs       UPDATE

Connection 1 (spid 10) is blocking connection 2 (spid 11). (Of course, we already knew that, but at some other time this information might help you get out of a bind.) Until connection 1 clears the lock, connection 2 will remain in a lock sleep state.