Transactional DDL rollback behavior under concurrent load

Question

Transactional DDL Rollback Behavior
Goal: Determine if rolling back a large schema change is truly safe when other sessions are active.
Constraints: The rollback must not hold Sch‑M locks for an unacceptable duration, and should not cause significant blocking of readers or writers on the affected object.
Open Issues
SQL Server supports transactional DDL, but undoing a large change can be as expensive as the original operation and keeps Sch‑M locks until completion. The impact on concurrent workloads is unclear.

Will a rollback of a large ALTER TABLE executed inside a transaction hold Sch‑M locks long enough to block other sessions?
How does the rollback duration scale with the size of the table or the complexity of the change?
Are there version‑specific limits or recommended patterns to mitigate blocking during rollback?

Tasadduq Burney · Answer

Direct Answer
Yes, rolling back a large ALTER TABLE inside an explicit transaction holds a Sch‑M (schema modification) lock for the entire rollback duration, blocking all concurrent readers and writers on that object. The lock is not released early, and snapshot isolation does not prevent this blocking.

Confirmed Behavior

Sch‑M lock duration: The exclusive Sch‑M lock is acquired at the start of the rollback and held until every log record generated by the forward operation is undone and the transaction transitions to the aborted state. No internal mechanism yields or downgrades this lock.
Blocking scope: All sessions attempting SELECT, INSERT, UPDATE, DELETE, or any other access to the target table (or its indexes) will wait on LCK_M_SCH_M until the rollback completes.
Rollback scaling: Rollback time correlates with the physical work logged during the forward operation—page allocations, data movement, index rebuilds, and log record generation. A metadata‑only change (e.g., adding a nullable column) rolls back in milliseconds; a clustered index rebuild or data‑type change on a 1 TB table can take hours.
Version notes (2016–2022 / Azure SQL): Online and resumable index rebuild (ONLINE = ON, RESUMABLE = ON) avoid Sch‑M during the forward operation, but they do not apply to an explicit ROLLBACK of a transaction that has not yet committed. Once you issue ROLLBACK, the same Sch‑M lock is held for the full undo.

Practical Verification Steps

Run a controlled test in a non‑production environment:
BEGIN TRAN;
ALTER TABLE dbo.BigTable REBUILD;
-- In another session, monitor:
SELECT request_session_id, resource_type, resource_associated_entity_id, request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT' AND request_mode = 'Sch-M';
ROLLBACK;
-- Watch rollback progress:
SELECT percent_complete, estimated_completion_time, command
FROM sys.dm_exec_requests
WHERE command LIKE '%ROLLBACK%';

Capture wait stats during rollback to quantify LCK_M_SCH_M impact on concurrent workloads.
Check @@VERSION and the specific DDL statement’s ONLINE support matrix for your edition.

Recommended Mitigation Patterns

Avoid wrapping large data‑movement DDL in explicit transactions when concurrency is required.
Use ONLINE = ON (and RESUMABLE = ON where available) for index rebuilds and compatible ALTER TABLE operations so the forward path does not block.
Schedule maintenance windows for unavoidable large schema changes; test rollback duration on a representative data volume beforehand.
Keep DDL transactions as short as possible—separate schema changes from long‑running data modifications.
Monitor transaction log growth (DBCC SQLPERF(LOGSPACE)) during the forward operation to estimate rollback log volume.

One Diagnostic Detail Needed
What is the exact ALTER TABLE statement (including options like ONLINE, RESUMABLE, column nullability, default values) and the approximate table size (row count, data + index space)? This determines whether the forward operation itself acquires Sch‑M and how the rollback will scale.

Transactional DDL rollback behavior under concurrent load