Table Hint UPDLOCK and LOCK ESCALATION

When using table hints, a few rules should be recommende, which – if ignored – can quickly lead to performance problems. In the database of one of our customer’s software, UPDLOCK is used as a table hint to force a serialization of processes. Sometimes this process leads to no one being able to access the objects in question until the process is completed.

Table Hint UPDLOCK

The UPDLOCK table hint in SQL Server is used to apply update locks when performing read operations. It ensures that a resource (such as a row or page) remains locked with an update lock while the transaction is active. This is particularly useful in scenarios where you want to avoid potential deadlocks or race conditions during reads that are part of an eventual update.

By holding an update lock instead of a shared lock during a read operation, UPDLOCK prevents other transactions from acquiring incompatible locks (like exclusive locks) on the same resource. Other transactions can still read the resource, but they can’t modify it until the current transaction completes.

Why is this option used?

Database developers like to use such „tactics“ to serialize processes.

• In a transaction, a SELECT is performed on a resource.
• To protect the record from changes, an UPDATE lock is used using UPDLOCK as a table hint.
• After the lock has been successfully set, the actual process begins, which relates to the data of the locked record.

BEGIN TRANSACTION
GO
	SELECT * FROM dbo.customers WITH (UPDLOCK)
	WHERE	c_custkey = 5;
	GO

    /* Let's see what resources have been locked inside the transaction */

    /*
        The used function is part of the framework of the demo database ERP_Demo.
        Download: https://www.db-berater.de/downloads/ERP_DEMO_2012.BAK
    */
	SELECT	DISTINCT
            object_name,
            resource_description,
            resource_type,
            request_mode,
            request_status,
            sort_order
	FROM	dbo.get_locking_status(@@SPID)
    WHERE   object_name = N'[dbo].[customers]'
            OR
            (
                resource_type = N'OBJECT'
                AND resource_description = 'customers'
            )
    ORDER BY
            sort_order ASC;
    GO

A record is provided with an LCK_M_U lock. The lock hierarchy locks the data page with an LCK_M_IU lock to signal that an LCK_M_U lock is used on the page.
The table itself is provided with an LCK_M_IX lock to enable possible lock escalation!

SQL Server creates a lock chain that starts at the table level. A table is locked with an INTENT EXCLUSIVE Lock because an LCK_M_U lock implies a write operation. The data page is provided with an INTENT UPDATE Lock to signal that there is a record on the data page that should be provided with an UPDATE Lock.

The good news – an LCK_M_U lock allows other processes to continue reading the locked data. A writing process is no longer able to block the resource. A LCK_M_X lock is not compatible with a LCK_M_U lock.

While process 52 holds an LCK_M_U lock on the data record, process 56 can read it (LCK_M_S).

Regardless of the type of access, the lock hierarchy always applies. The mutual locks on the different levels are compatible with each other and do not interfere with each other.

Lock Escalation

Lock Escalation in SQL Server is the process of converting many fine-grained locks into coarse-grained locks. Tracking thousands of individual locks can consume significant memory overhead. Lock Escalation helps prevent this scenario by reducing the total number of locks held by a single transaction. The thresholds for lock escalation in SQL Server depend on two factors. The number of locks and the memory available for managing them.

Lock Count Threshold

Lock escalation typically occurs when a single query or transaction holds at least 5,000 locks on a single resource. This is a general guideline, and SQL Server might trigger escalation earlier under specific conditions, such as memory pressure. SQL Server checks every 1,250 locks to see if a lock escalation can be performed.

Memory Threshold

If the system experiences low memory for lock management, SQL Server may escalate locks even if the number of locks held by a transaction is below the typical count threshold.

Complete lockout upon lock escalation

When using UPDLOCK table hint, Microsoft SQL Server uses an INTENT EXCLUSIVE lock on the table object, not an INTENT UPDATE lock! This has serious consequences if SQL Server performs a lock escalation. At this point, the table is exclusively locked, and neither read nor write operations are possible on the object.

BEGIN TRANSACTION
GO
	SELECT * FROM dbo.customers WITH (UPDLOCK)
	WHERE	c_custkey <= 6150;
	GO

If another process wants to read data from the table it has to wait.

Process 52 holds an EXCLUSIVE Lock on the table object. Process 55 must wait until the lock is released, before it can access the object for a read operation.

Thank you for reading.