Understanding Locking and DDL Blocking in Microsoft Fabric Data Warehouse

[twcyril]

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 SELECT statements on sys.tables and sys.objects.
• Since Sch-M locks on tables and X locks on sys.tables rows 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. inside BEGIN 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 and SELECT queries, both on user tables and system catalog views like sys.tables. To monitor and troubleshoot potential lock conflicts, use sys.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.