Sql-server – # of key locks while updating

lockingprimary-keysql-server-2008

I am using SQL Server 2008, RTM. I have a simple table defined as

create table t(id integer not null primary key)

This table has 50,000 records, just in case. When I issue an update transaction like

begin transaction

Update t

set id=50001

where id=5;

with no commit/rollback, it shows me one row affected. When I take a look at the result of the sp_locks, I and see there are two X locks on the KEY type granted, the Resource values for them are different from each other, the spid is the same.
Why there are two X locks on the key while there is only one row updated? Thanks.

Best Answer

Because you're taking one key value, and assigning it another key value.

It's 2017, so I'm not going to use sp_lock. I'm going to use sp_WhoIsActive.

If we run it like this: EXEC sp_WhoIsActive @get_locks = 1 we can get lock information.

CREATE TABLE t
(
    id INTEGER NOT NULL PRIMARY KEY
);

INSERT dbo.t ( id )
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY @@DBTS)
FROM (SELECT 1 AS n FROM sys.messages AS m CROSS JOIN sys.messages AS m2) AS x

If we update one key value:

BEGIN TRANSACTION;

UPDATE t
SET    id = 50001
WHERE  id = 5;

ROLLBACK

The lock information looks like this:

<Object name="t" schema_name="dbo">
      <Locks>
        <Lock resource_type="KEY" index_name="PK__t__3213E83FE76849A4" request_mode="X" request_status="GRANT" request_count="2" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK__t__3213E83FE76849A4" request_mode="IX" request_status="GRANT" request_count="2" />
      </Locks>
    </Object>

Two key locks! One key lock to get the key value we're modifying, and one lock to reserve the key value we're modifying it to.

If we modify two values...

BEGIN TRANSACTION;

UPDATE t
SET    id += 50001
WHERE  id IN (5, 10)

ROLLBACK

We end up with four key locks. This pattern will continue if we do 3 or 4. There will be two key locks for each ID touched.

<Object name="t" schema_name="dbo">
  <Locks>
    <Lock resource_type="KEY" index_name="PK__t__3213E83FE76849A4" request_mode="X" request_status="GRANT" request_count="4" />
    <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
    <Lock resource_type="PAGE" page_type="*" index_name="PK__t__3213E83FE76849A4" request_mode="IX" request_status="GRANT" request_count="2" />
  </Locks>

Hitting three values:

BEGIN TRANSACTION;

UPDATE t
SET    id += 50001
WHERE  id IN (5, 10, 15)

ROLLBACK

Hitting four values:

BEGIN TRANSACTION;

UPDATE t
SET    id += 50001
WHERE  id IN (5, 10, 15, 20)

ROLLBACK

At least up until we modify everything.

BEGIN TRANSACTION;

UPDATE t
SET    id += 1

ROLLBACK

Then the lock type changes!

   <Object name="t" schema_name="dbo">
      <Locks>
        <Lock resource_type="ALLOCATION_UNIT.BULK_OPERATION_PAGE" index_name="PK__t__3213E83FE76849A4" request_mode="S" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>

This is due to the magic of the split, sort, and collapse operations, which you can see in the query plan here.

Hope this helps!

Related Question