LCK_M_BU Wait Type Explained

The Bulk Update (BU) lock is a specialized lock mode acquired specifically by bulk operations that use the TABLOCK hint, such as BULK INSERT with TABLOCK or bcp with TABLOCK. BU locks allow multiple concurrent bulk load operations on the same table while preventing non-bulk operations from accessing the table. Lock escalation does not promote to BU locks; BU locks are directly acquired by the bulk operation mechanism.

Operations triggering BU locks include BULK INSERT with TABLOCK hint and bcp operations with TABLOCK hint. The lock manager places BU locks at the table level, allowing other BU locks (enabling parallel bulk loads) but blocking shared and exclusive locks from non-bulk operations. This differs from exclusive locks which block all access including other bulk operations.

In SQL Server 2016 and later, the lock manager improved BU lock compatibility with online index operations. SQL Server 2019 introduced accelerated database recovery which can reduce BU lock duration during index rebuilds. The buffer pool manager coordinates with the lock manager to ensure consistent page access during bulk operations.

BU locks are not the result of lock escalation. They are explicitly acquired when bulk operations use the TABLOCK hint to enable minimally logged inserts. The lock duration corresponds to the bulk operation's transaction scope.

-- Current BU lock waits and blocking chains
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    s.login_name,
    r.command,
    t.text,
    r.database_id,
    DB_NAME(r.database_id) as database_name
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_BU'
ORDER BY r.wait_time DESC;

-- Active BU locks and their resources
SELECT 
    l.resource_type,
    l.resource_database_id,
    DB_NAME(l.resource_database_id) as database_name,
    l.resource_associated_entity_id,
    OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) as object_name,
    l.request_mode,
    l.request_status,
    s.session_id,
    s.login_name,
    s.program_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
WHERE l.request_mode = 'BU'
ORDER BY l.resource_database_id, l.resource_associated_entity_id;

-- Historical BU lock waits from wait stats
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / waiting_tasks_count as avg_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LCK_M_BU'
AND waiting_tasks_count > 0;

-- Lock escalation events on tables (from index operational stats)
SELECT 
    DB_NAME(database_id) as database_name,
    OBJECT_NAME(object_id, database_id) as object_name,
    index_id,
    lock_escalation_count,
    page_lock_count,
    row_lock_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL)
WHERE lock_escalation_count > 0
ORDER BY lock_escalation_count DESC;

-- Current bulk operations that may hold BU locks
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    s.login_name,
    t.text,
    r.database_id,
    DB_NAME(r.database_id) as database_name
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN ('SELECT INTO', 'CREATE INDEX', 'ALTER INDEX', 'BULK INSERT')
OR t.text LIKE '%BULK INSERT%'
OR t.text LIKE '%SELECT%INTO%';

Kill blocking bulk operation session Terminates a session holding BU locks when the operation appears hung or is blocking critical processes.

-- Replace session_id with actual blocking session from diagnostic queries
-- WARNING: This will terminate the session and rollback the transaction
KILL 52; -- Replace 52 with actual session_id

-- Verify the session is terminated
SELECT session_id, status, login_name 
FROM sys.dm_exec_sessions 
WHERE session_id = 52;

Test in development first. This immediately terminates the session and may cause data loss if the operation was near completion.

Stagger concurrent bulk operations on the same table Reduces BU lock contention by coordinating concurrent bulk operations that compete for the same table.

-- Verify current bulk operations holding BU locks
SELECT 
    l.resource_associated_entity_id,
    OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) as object_name,
    l.request_session_id,
    l.request_status,
    s.login_name,
    s.program_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
WHERE l.request_mode = 'BU';

BU locks are acquired directly by bulk operations with TABLOCK, not through lock escalation. Serialize bulk loads or reduce concurrency when contention is high.

Add READPAST hint to queries encountering BU locks Allows SELECT statements to skip rows locked by BU locks instead of waiting.

-- Example: Replace blocking SELECT with READPAST hint
SELECT column1, column2, column3
FROM YourTable WITH (READPAST)
WHERE your_conditions;

-- For applications that can tolerate potentially missing recent inserts
-- This bypasses BU lock waits entirely

Only use when the application can tolerate potentially incomplete result sets. Document this behavior change thoroughly.

Implement table hints for bulk operations Reduces BU lock duration by using more specific locking hints during bulk operations.

-- Use TABLOCK for faster bulk inserts with shorter BU lock duration
BULK INSERT YourTable
FROM 'C:\data\yourfile.txt'
WITH (
    TABLOCK,
    ROWS_PER_BATCH = 10000,
    BATCHSIZE = 1000
);

-- Alternative: Use TABLOCKX for exclusive access during bulk operations
SELECT * INTO NewTable 
FROM SourceTable WITH (TABLOCKX);

TABLOCK reduces lock overhead but prevents concurrent modifications. TABLOCKX provides fastest performance but blocks all access.

Schedule bulk operations during maintenance windows to minimize contention with other workloads. When multiple bulk loads target the same table, use TABLOCK to allow concurrent bulk inserts via compatible BU locks.

Implement proper indexing strategies for bulk operations. Create clustered indexes before bulk loading data to enable minimally logged inserts. Use columnstore indexes for large analytical bulk loads to minimize row-level locking.

Schedule bulk operations during low-activity periods using SQL Server Agent jobs. Stagger multiple bulk operations across different databases or filegroups to prevent resource contention. Monitor sys.dm_os_wait_stats for LCK_M_BU trends and adjust scheduling accordingly.

Set appropriate batch sizes for bulk operations. Use ROWS_PER_BATCH and BATCHSIZE options in BULK INSERT to control lock duration. Implement checkpoints in large data migration scripts to periodically release locks. Configure applications to use smaller transaction scopes for bulk modifications.

Establish monitoring alerts for sustained LCK_M_BU waits exceeding 10 seconds. Create custom performance counters tracking bulk operation frequency and duration. Implement automated scripts to identify and resolve lock escalation patterns on frequently accessed tables.