Azure SQL Database 2022: Query Timeout Enforcement Across Multi-Statement Batches and Distributed Transactions

Timeout and Cancellation Behavior

In Azure SQL Database, a client-configured timeout (such as SqlCommand.CommandTimeout) does not act as a server-side governor but as a client-side timer. When this timer expires, the driver sends an attention signal to the server to cancel the current execution.

Multi-Statement Batches

A client-set timeout aborts the entire batch at the point of expiration. It does not skip the current statement to move to the next; rather, it terminates the execution of the remaining statements in that specific batch. However, the timeout itself does not trigger an automatic transaction rollback unless SET XACT_ABORT ON is configured. If XACT_ABORT is OFF (the default), statements executed prior to the timeout within the same transaction may remain uncommitted but active, potentially holding locks until the session expires or a manual rollback is issued.

Distributed Transactions and Cancellation

When SqlCommand.Cancel() is invoked within a distributed transaction, the following logic applies:

• Local Execution: The attention signal stops the local query execution once the server reaches a safe cancellation point.
• Global State: The cancellation of a single request does not automatically roll back the entire distributed transaction. The transaction remains open in a "prepared" or "active" state.
• Rollback Determination: The transaction only rolls back if the application explicitly calls Transaction.Rollback() or if the transaction coordinator (e.g., MSDTC or the Azure SQL elastic transaction coordinator) determines the participant is unreachable or has failed.

Technical Assumptions and Verification

This behavior assumes the use of modern ADO.NET or JDBC drivers. Older drivers may handle attention signals inconsistently, occasionally failing to notify the server of a timeout until the connection is physically dropped.

To verify the state of your transactions during a timeout, use the following scoped query to identify orphaned sessions or open transactions:

SELECT session_id, open_transaction_count, status 
FROM sys.dm_exec_sessions 
WHERE open_transaction_count > 0;

Required Diagnostic Detail: To provide a more specific recommendation on rollback behavior, please specify if you are using SET XACT_ABORT ON in your session initialization scripts.