Locking and Concurrency in SQL Server

LOCK MODES
  S   Shared         : reading. Many S locks can exist together.
  X   Exclusive      : writing. Nobody else can read (under locking) or write.
  U   Update         : "I will update soon". Prevents a common deadlock.
  IS / IX / IU       : Intent locks on higher levels (page, table) saying
                       "somewhere below I have S / X / U locks".
  Sch-S / Sch-M      : Schema stability / modification (ALTER TABLE).

LOCK GRANULARITY :  RID/KEY (row)  ->  PAGE  ->  TABLE (OBJECT)
LOCK ESCALATION  :  ~5000 row locks on one table -> converted into ONE table lock.

COMPATIBILITY (Y = allowed together)
         S   U   X
    S    Y   Y   N
    U    Y   N   N
    X    N   N   N

CONCURRENCY MODELS
  Pessimistic : lock first, then work (UPDLOCK, HOLDLOCK, SERIALIZABLE)
  Optimistic  : don't lock, detect conflicts on save (rowversion, SNAPSHOT)

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic tables

DROP TABLE IF EXISTS dbo.JobQueue;
DROP TABLE IF EXISTS dbo.Documents;
DROP TABLE IF EXISTS dbo.LockDemo;

-- For lock observation and escalation
CREATE TABLE dbo.LockDemo
(
    ID      INT IDENTITY(1,1) PRIMARY KEY,
    Payload CHAR(100) NOT NULL DEFAULT 'x',
    GroupNo INT NOT NULL
);

INSERT INTO dbo.LockDemo (GroupNo)
SELECT TOP (20000) ABS(CHECKSUM(NEWID())) % 10
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

-- For optimistic concurrency (rowversion)
CREATE TABLE dbo.Documents
(
    DocID     INT PRIMARY KEY,
    Title     VARCHAR(100) NOT NULL,
    Body      VARCHAR(1000) NOT NULL,
    UpdatedBy VARCHAR(50)  NOT NULL,
    RowVer    ROWVERSION                 -- changes automatically on every update
);

INSERT INTO dbo.Documents (DocID, Title, Body, UpdatedBy) VALUES
(1, 'Leave Policy',  'Employees get 12 casual leaves.', 'Meena'),
(2, 'Travel Policy', 'Economy class for trips < 4 hours.', 'Suresh');

-- For a work queue processed by many workers at the same time
CREATE TABLE dbo.JobQueue
(
    JobID      INT IDENTITY(1,1) PRIMARY KEY,
    JobType    VARCHAR(20)  NOT NULL,
    Status     VARCHAR(10)  NOT NULL DEFAULT 'New',      -- New / Running / Done
    WorkerName VARCHAR(20)  NULL,
    CreatedAt  DATETIME2(0) NOT NULL DEFAULT SYSDATETIME()
);

INSERT INTO dbo.JobQueue (JobType) VALUES
('SendEmail'), ('GenerateInvoice'), ('SendEmail'), ('ResizeImage'), ('SendSMS'),
('GenerateInvoice'), ('SendEmail'), ('ResizeImage'), ('SendSMS'), ('SendEmail');
GO

(20000 rows affected)
(2 rows affected)
(10 rows affected)

Part A: See the Locks Your Own TRANSACTION Holds

-- Helper view of locks for the current session
CREATE OR ALTER VIEW dbo.vw_MyLocks
AS
SELECT l.request_session_id AS SessionID,
       l.resource_type      AS ResourceType,
       l.request_mode       AS Mode,
       l.request_status     AS Status,
       l.resource_description AS Resource,
       COALESCE(OBJECT_NAME(p.object_id),
                CASE WHEN l.resource_type = 'OBJECT' THEN OBJECT_NAME(l.resource_associated_entity_id) END) AS ObjectName
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
WHERE l.resource_database_id = DB_ID();
GO

UPDATE one row -> X lock on the Key, Ix on Page and Object

BEGIN TRAN;
    UPDATE dbo.LockDemo SET Payload = 'updated' WHERE ID = 10;
    SELECT * FROM dbo.vw_MyLocks WHERE SessionID = @@SPID AND ObjectName = 'LockDemo';
ROLLBACK;

(1 row affected)

SELECT under READ COMMITTED -> S locks are released immediately (nothing shown)

BEGIN TRAN;
    SELECT * FROM dbo.LockDemo WHERE ID = 10;
    SELECT 'READ COMMITTED' AS Level_, * FROM dbo.vw_MyLocks WHERE SessionID = @@SPID AND ObjectName = 'LockDemo';
ROLLBACK;

SELECT with HOLDLOCK (= SERIALIZABLE) -> range S locks kept until end of transaction

BEGIN TRAN;
    SELECT * FROM dbo.LockDemo WITH (HOLDLOCK) WHERE ID BETWEEN 10 AND 12;
    SELECT 'HOLDLOCK' AS Level_, * FROM dbo.vw_MyLocks WHERE SessionID = @@SPID AND ObjectName = 'LockDemo';
ROLLBACK;

SELECT with UPDLOCK -> U locks (others can read, but cannot take U or X)

BEGIN TRAN;
    SELECT * FROM dbo.LockDemo WITH (UPDLOCK) WHERE ID = 10;
    SELECT 'UPDLOCK' AS Level_, * FROM dbo.vw_MyLocks WHERE SessionID = @@SPID AND ObjectName = 'LockDemo';
ROLLBACK;
GO

Part B: Lock Escalation

Update ~2000 rows: many Key locks

BEGIN TRAN;
    UPDATE dbo.LockDemo SET Payload = 'g1' WHERE GroupNo = 1;
    SELECT Mode, ResourceType, COUNT(*) AS LockCount
    FROM dbo.vw_MyLocks WHERE SessionID = @@SPID AND ObjectName = 'LockDemo'
    GROUP BY Mode, ResourceType;
ROLLBACK;

(1965 rows affected)

Update All 20000 rows: escalates to One table X lock

BEGIN TRAN;
    UPDATE dbo.LockDemo SET Payload = 'all';
    SELECT Mode, ResourceType, COUNT(*) AS LockCount
    FROM dbo.vw_MyLocks WHERE SessionID = @@SPID AND ObjectName = 'LockDemo'
    GROUP BY Mode, ResourceType;
ROLLBACK;

(20000 rows affected)

Control escalation per table: Table (default) | Auto (partition level) | Disable

ALTER TABLE dbo.LockDemo SET (LOCK_ESCALATION = TABLE);
SELECT name, lock_escalation_desc FROM sys.tables WHERE name = 'LockDemo';

-- Tip : update big tables in BATCHES to avoid escalation and long blocking
DECLARE @Rows INT = 1;
WHILE @Rows > 0
BEGIN
    UPDATE TOP (2000) dbo.LockDemo SET Payload = 'batched' WHERE Payload <> 'batched';
    SET @Rows = @@ROWCOUNT;
END;
SELECT Payload, COUNT(*) AS Cnt FROM dbo.LockDemo GROUP BY Payload;
GO

(2000 rows affected)
(2000 rows affected)
(2000 rows affected)
(2000 rows affected)
(2000 rows affected)
(2000 rows affected)
(2000 rows affected)
(2000 rows affected)
(2000 rows affected)
(2000 rows affected)
(0 rows affected)

Part C: Table Hints (use carefully!)

• NOLOCK: read without S locks (dirty reads, may skip/duplicate rows)
• READPAST: skip rows that are locked by others
• UPDLOCK: take U locks when reading (reserve rows for update)
• HOLDLOCK: keep locks until the end of transaction (= SERIALIZABLE)
• ROWLOCK / PAGLOCK / TABLOCK / TABLOCKX: choose granularity

Job queue with many workers: each worker grabs the next Free job.

READPAST skips jobs locked by other workers, UPDLOCK reserves ours.

(Open 2 windows and run this block at the same time to see them get DIFFERENT jobs.)

DECLARE @Worker VARCHAR(20) = CONCAT('Worker-', @@SPID);
DECLARE @Taken TABLE (JobID INT, JobType VARCHAR(20));

BEGIN TRAN;
    WITH NextJob AS
    (
        SELECT TOP (1) JobID, JobType, Status, WorkerName
        FROM dbo.JobQueue WITH (UPDLOCK, READPAST, ROWLOCK)
        WHERE Status = 'New'
        ORDER BY JobID
    )
    UPDATE NextJob
    SET Status = 'Running', WorkerName = @Worker
    OUTPUT inserted.JobID, inserted.JobType INTO @Taken;

    -- WAITFOR DELAY '00:00:10';   -- uncomment to keep the lock while you test the second window
COMMIT;

SELECT 'I took job' AS Info, * FROM @Taken;
SELECT * FROM dbo.JobQueue;
GO

(1 row affected)

TABLOCK for fast bulk insert into a staging table (minimal logging possible)

DROP TABLE IF EXISTS dbo.StagingBulk;
CREATE TABLE dbo.StagingBulk (ID INT, Payload CHAR(100));
INSERT INTO dbo.StagingBulk WITH (TABLOCK) (ID, Payload)
SELECT ID, Payload FROM dbo.LockDemo;
SELECT COUNT(*) AS LoadedRows FROM dbo.StagingBulk;
DROP TABLE dbo.StagingBulk;
GO

(20000 rows affected)

Part D: Lost UPDATE Problem and how to prevent it

Two users read Qty = 10, both sell 1, both write 9 -> one sale is "lost".

BAD (read, then write with an old value):

DECLARE @q INT = (SELECT Qty FROM dbo.WarehouseStock WHERE ItemID = 1);
UPDATE dbo.WarehouseStock SET Qty = @q - 1 WHERE ItemID = 1;

GOOD 1 : do it in one atomic statement
    UPDATE dbo.WarehouseStock SET Qty = Qty - 1 WHERE ItemID = 1 AND Qty >= 1;

GOOD 2 : pessimistic lock while reading
    BEGIN TRAN;
      SELECT Qty FROM dbo.WarehouseStock WITH (UPDLOCK) WHERE ItemID = 1;
      UPDATE ...;
    COMMIT;

GOOD 3 : optimistic concurrency with ROWVERSION (below)

Optimistic concurrency: the app remembers RowVer when it Reads the row,

and the UPDATE only succeeds if nobody changed the row in between.

-- User A and User B both open document 1 and remember its version
DECLARE @VersionSeenByA BINARY(8) = (SELECT RowVer FROM dbo.Documents WHERE DocID = 1);
DECLARE @VersionSeenByB BINARY(8) = @VersionSeenByA;

-- User A saves first -> succeeds, RowVer changes
UPDATE dbo.Documents
SET Body = 'Employees get 15 casual leaves.', UpdatedBy = 'UserA'
WHERE DocID = 1 AND RowVer = @VersionSeenByA;
SELECT 'User A save' AS Action_, @@ROWCOUNT AS RowsUpdated;

-- User B saves later with the OLD version -> 0 rows -> conflict detected
UPDATE dbo.Documents
SET Body = 'Employees get 10 casual leaves.', UpdatedBy = 'UserB'
WHERE DocID = 1 AND RowVer = @VersionSeenByB;

IF @@ROWCOUNT = 0
    SELECT 'User B save' AS Action_, 'CONFLICT: document was changed by someone else. Reload and try again.' AS Message;

SELECT DocID, Body, UpdatedBy, RowVer FROM dbo.Documents;
GO

(1 row affected)

(0 rows affected)

Part E: Application Locks (lock a "name", not a row)

Useful to make sure only ONE copy of a job runs at a time.

BEGIN TRAN;
    DECLARE @r INT;
    EXEC @r = sys.sp_getapplock @Resource = 'NightlyInvoiceJob', @LockMode = 'Exclusive',
                                @LockOwner = 'Transaction', @LockTimeout = 0;
    IF @r >= 0
        PRINT 'Got the lock - running nightly job...';     -- a second window would get -1 (timeout)
    ELSE
        PRINT 'Another session is already running the job.';

    SELECT resource_type, resource_description, request_mode
    FROM sys.dm_tran_locks WHERE request_session_id = @@SPID AND resource_type = 'APPLICATION';
COMMIT;    -- releases the app lock
GO

Got the lock - running nightly job...

Part F: Useful Monitoring Queries

Top waits on the server (LCK_M_* waits = lock blocking)

SELECT TOP (10) wait_type, waiting_tasks_count, wait_time_ms / 1000.0 AS WaitSeconds
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'LCK%'
ORDER BY wait_time_ms DESC;

Lock and latch statistics per index (which index has most row lock waits?)

SELECT OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName,
       s.row_lock_count, s.row_lock_wait_count, s.page_lock_count, s.index_lock_promotion_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY s.row_lock_count DESC;
GO

Practice Questions

1. Run A1 with PAGLOCK and TABLOCKX hints and compare the locks you see.
2. Open two windows, run C1 in both with the WAITFOR uncommented - confirm they take different jobs.
3. Remove READPAST from C1 and repeat. What happens to the second window?
4. Simulate a lost update with two windows using the BAD pattern, then fix it with UPDLOCK.
5. Add a RowVer column to dbo.Products and write a "safe price update" procedure using it.