Understanding Locking and DDL Blocking in Microsoft Fabric Data Warehouse
As adoption of Microsoft Fabric Data Warehouse accelerates, understanding its concurrency model becomes essential for developers, architects, and data engineers. In this post, we’ll explore how Fabric DW handles locking, the nuances of DDL blocking, and how these behaviors impact your workloads.
Locking Behavior in Fabric Data Warehouse
Fabric DW supports ACID-compliant transactions using standard T-SQL (BEGIN TRANSACTION, COMMIT, ROLLBACK) and enforces snapshot isolation across all operations. Locks in Fabric Data Warehouse are used to manage concurrent access to metadata and data, especially during DDL operations. Here’s how locking works:
Table-Level Lock Modes by Operation
| Statement type | Lock taken |
| SELECT | Schema-Stability (Sch-S) |
| INSERT | Intent Exclusive (IX) |
| DELETE | Intent Exclusive (IX) |
| UPDATE | Intent Exclusive (IX) |
| MERGE | Intent Exclusive (IX) |
| COPY INTO | Intent Exclusive (IX) |
| CREATE TABLE | Schema-Modification (Sch-M) |
| ALTER TABLE | Schema-Modification (Sch-M) |
| DROP TABLE | Schema-Modification (Sch-M) |
| TRUNCATE | Schema-Modification (Sch-M) |
| CTAS | Schema-Modification (Sch-M) |
| CREATE TABLE AS CLONE | Schema-Modification (Sch-M) |
Fabric DW uses table-level locking, regardless of whether a query touches one row or many. This simplifies the engine’s concurrency model but introduces some trade-offs:
Pros: Predictable behavior, easier to reason about.
Cons: Can lead to blocking in high-concurrency scenarios, specifically when DDLs are included.
DDL Blocking Behavior
Long-running transactions that have included at least one DDL operation can block concurrent transactions. This is the current design of Fabric DW. This locking behavior aligns with SqlDb behavior.
- DDL operations acquire Sch-M locks on target table for the duration of the transaction.
- These locks prevent concurrent DML operations (e.g.,
SELECT, INSERT, UPDATE, DELETE) targeting the same table from proceeding. - DDL operations also acquire an X lock on rows in sys.tables and sys.objects associated with the target table of the DDL. This will block concurrent
SELECTstatements on sys.tables and sys.objects. - Since Sch-M locks on tables and X locks on
sys.tablesrows are held for the duration of the transaction, they can be especially long-lived if the DDL is done in an explicit user transaction (i.e. insideBEGIN TRAN).
This behavior is particularly impactful in scenarios involving:
- Schema evolution during active workloads.
- Automated schema updates or migrations.
Blocking and Conflicts can impact performance, reliability, and user experience, especially in high-concurrency environments. Understanding how they work and how to mitigate them is essential for building resilient data pipelines and applications in Fabric DW.
Best Practices for Developers
- Avoid long-running transactions that hold locks for extended periods.
- Schedule DDL operations during maintenance windows to minimize blocking.
- Avoid placing DDL statements inside explicit user transactions (e.g.,
BEGIN TRAN) as this can cause blocking issues for concurrent DML operations andSELECTqueries, both on user tables and system catalog views likesys.tables. To monitor and troubleshoot potential lock conflicts, usesys.dm_tran_locks.
What’s Next
READPAST hint to mitigate DDL blocking on metadata queries – This enhancement will introduce support for the READPAST table hint when querying sys.tables This allows metadata queries to skip over rows that are currently locked with an exclusive (X) lock—typically those associated with tables undergoing DDL operations within active transactions. As a result, queries against sys.tables will no longer be blocked entirely by DDL activity and sys.tables will instead return all other available rows, improving system responsiveness and observability during schema changes.
