Atomic Blocks in Native Procedures
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
BEGIN ATOMIC is part of the ANSI SQL standard. SQL Server supports atomic blocks at the top-level of natively compiled stored procedures, as well as for natively compiled, scalar user-defined functions. For more information about these functions, see Scalar User-Defined Functions for In-Memory OLTP.
Every natively compiled stored procedure contains exactly one block of Transact-SQL statements. This is an ATOMIC block.
Non-native, interpreted Transact-SQL stored procedures and ad hoc batches do not support atomic blocks.
Atomic blocks are executed (atomically) within the transaction. Either all statements in the block succeed or the entire block will be rolled back to the savepoint that was created at the start of the block. In addition, the session settings are fixed for the atomic block. Executing the same atomic block in sessions with different settings will result in the same behavior, independent of the settings of the current session.
Transactions and Error Handling
If a transaction already exists on a session (because a batch executed a BEGIN TRANSACTION statement and the transaction remains active), then starting an atomic block will create a savepoint in the transaction. If the block exits without an exception, the savepoint that was created for the block commits, but the transaction will not commit until the transaction at the session level commits. If the block throws an exception, the effects of the block are rolled back but the transaction at the session level will proceed, unless the exception is transaction-dooming. For example a write conflict is transaction-dooming, but not a type casting error.
If there is no active transaction on a session, BEGIN ATOMIC will start a new transaction. If no exception is thrown outside the scope of the block, the transaction will be committed at the end of the block. If the block throws an exception (that is, the exception is not caught and handled within the block), the transaction will be rolled back. For transactions that span a single atomic block (a single natively compiled stored procedure), you do not need to write explicit BEGIN TRANSACTION and COMMIT or ROLLBACK statements.
Natively compiled stored procedures support the TRY, CATCH, and THROW constructs for error handling. RAISERROR is not supported.
The following example illustrates the error handling behavior with atomic blocks and natively compiled stored procedures:
-- sample table CREATE TABLE dbo.t1 ( c1 int not null primary key nonclustered ) WITH (MEMORY_OPTIMIZED=ON) GO -- sample proc that inserts 2 rows CREATE PROCEDURE dbo.usp_t1 @v1 bigint not null, @v2 bigint not null WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english', DELAYED_DURABILITY = ON) INSERT dbo.t1 VALUES (@v1) INSERT dbo.t1 VALUES (@v2) END GO -- insert two rows EXEC dbo.usp_t1 1, 2 GO -- verify we have no active transaction SELECT @@TRANCOUNT GO -- verify the rows 1 and 2 were committed SELECT c1 FROM dbo.t1 GO -- execute proc with arithmetic overflow EXEC dbo.usp_t1 3, 4444444444444 GO -- expected error message: -- Msg 8115, Level 16, State 0, Procedure usp_t1 -- Arithmetic overflow error converting bigint to data type int. -- verify we have no active transaction SELECT @@TRANCOUNT GO -- verify rows 3 was not committed; usp_t1 has been rolled back SELECT c1 FROM dbo.t1 GO -- start a new transaction BEGIN TRANSACTION -- insert rows 3 and 4 EXEC dbo.usp_t1 3, 4 -- verify there is still an active transaction SELECT @@TRANCOUNT -- verify the rows 3 and 4 were inserted SELECT c1 FROM dbo.t1 WITH (SNAPSHOT) ORDER BY c1 -- catch the arithmetic overflow error BEGIN TRY EXEC dbo.usp_t1 5, 4444444444444 END TRY BEGIN CATCH PRINT N'Error occurred: ' + error_message() END CATCH -- verify there is still an active transaction SELECT @@TRANCOUNT -- verify rows 3 and 4 are still in the table, and row 5 has not been inserted SELECT c1 FROM dbo.t1 WITH (SNAPSHOT) ORDER BY c1 COMMIT GO -- verify we have no active transaction SELECT @@TRANCOUNT GO -- verify rows 3 and 4 has been committed SELECT c1 FROM dbo.t1 ORDER BY c1 GO
The following error messages specific to memory-optimized tables are transaction dooming. If they occur in the scope of an atomic block, they will cause the transaction to abort: 10772, 41301, 41302, 41305, 41325, 41332, 41333, and 41839.
The session settings in atomic blocks are fixed when the stored procedure is compiled. Some settings can be specified with BEGIN ATOMIC while other settings are always fixed to the same value.
The following options are required with BEGIN ATOMIC:
|TRANSACTION ISOLATION LEVEL||Supported values are SNAPSHOT, REPEATABLEREAD, and SERIALIZABLE.|
|LANGUAGE||Determines date and time formats and system messages. All languages and aliases in sys.syslanguages (Transact-SQL) are supported.|
The following settings are optional:
|DATEFORMAT||All SQL Server date formats are supported. When specified, DATEFORMAT overrides the default date format associated with LANGUAGE.|
|DATEFIRST||When specified, DATEFIRST overrides the default associated with LANGUAGE.|
|DELAYED_DURABILITY||Supported values are OFF and ON.
SQL Server transaction commits can be either fully durable, the default, or delayed durable.For more information, see Control Transaction Durability.
The following SET options have the same system default value for all atomic blocks in all natively compiled stored procedures:
|Set Option||System Default for Atomic Blocks|
Uncaught exceptions cause the atomic block to roll back, but not cause the transaction to abort unless the error is transaction dooming.
Submit and view feedback for