Edit

Transactions in Warehouse tables in Microsoft Fabric

  • Article

Applies to: SQL analytics endpoint and Warehouse in Microsoft Fabric

Similar to their behavior in SQL Server, transactions allow you to control the commit or rollback of read and write queries.

You can modify data that is stored in tables in a Warehouse using transactions to group changes together.

  • For example, you could commit inserts to multiples tables, or, none of the tables if an error arises. If you're changing details about a purchase order that affects three tables, you can group those changes into a single transaction. That means when those tables are queried, they either all have the changes or none of them do. Transactions are a common practice for when you need to ensure your data is consistent across multiple tables.

Transactional capabilities

The same transactional capabilities are supported in the SQL analytics endpoint in Microsoft Fabric, but for read-only queries.

Transactions can also be used for sequential SELECT statements to ensure the tables involved all have data from the same point in time. As an example, if a table has new rows added by another transaction, the new rows don't affect the SELECT queries inside an open transaction.

Important

Only the snapshot isolation level is supported in Microsoft Fabric. If you use T-SQL to change your isolation level, the change is ignored at Query Execution time and snapshot isolation is applied.

Cross-database query transaction support

Warehouse in Microsoft Fabric supports transactions that span across databases that are within the same workspace including reading from the SQL analytics endpoint of the Lakehouse. Every Lakehouse has one read-only SQL analytics endpoint. Each workspace can have more than one lakehouse.

DDL support within transactions

Warehouse in Microsoft Fabric supports DDL such as CREATE TABLE inside user-defined transactions.

Locks for different types of statements

This table provides a list of what locks are used for different types of transactions, all locks are at the table level:

Statement type Lock taken
SELECT Schema-Stability (Sch-S)
INSERT Intent Exclusive (IX)
DELETE Intent Exclusive (IX)
UPDATE Intent Exclusive (IX)
COPY INTO Intent Exclusive (IX)
DDL Schema-Modification (Sch-M)

These locks prevent conflicts such as a table's schema being changed while rows are being updated in a transaction.

You can query locks currently held with the dynamic management view (DMV) sys.dm_tran_locks.

Conflicts from two or more concurrent transactions that update one or more rows in a table are evaluated at the end of the transaction. The first transaction to commit completes successfully and the other transactions are rolled back with an error returned. These conflicts are evaluated at the table level and not the individual parquet file level.

INSERT statements always create new parquet files, which means fewer conflicts with other transactions except for DDL because the table's schema could be changing.

Transaction logging

Transaction logging in Warehouse in Microsoft Fabric is at the parquet file level because parquet files are immutable (they can't be changed). A rollback results in pointing back to the previous parquet files. The benefits of this change are that transaction logging and rollbacks are faster.

Limitations

  • Distributed transactions are not supported.
  • Save points are not supported.
  • Named transactions are not supported.
  • Marked transactions are not supported.
  • ALTER TABLE is not supported within an explicit transaction.
  • At this time, there's limited T-SQL functionality in the warehouse. See TSQL surface area for a list of T-SQL commands that are currently not available.
  • If a transaction has data insertion into an empty table and issues a SELECT before rolling back, the automatically generated statistics can still reflect the uncommitted data, causing inaccurate statistics. Inaccurate statistics can lead to unoptimized query plans and execution times. If you roll back a transaction with SELECTs after a large INSERT, update statistics for the columns mentioned in your SELECT.

Related content