Database Reference
In-Depth Information
You can check that the transaction is still active and committable with this select: select @@TRANCOUNT as
[@@TRANCOUNT], XACT_STATE() as [XACT_STATE()] . It would return the following results:
@@TRANCOUNT XACT_STATE()
----------- ------------
1 1
If you commit the transaction and check the content of the table, you will see that the data reflects the changes
caused by the first stored procedure call. Even though the first update statement from the second call succeeded, SQL
Server rolled it back because the natively-compiled stored procedure executed as an atomic block. You can see the
data in the table below.
ID Value
----------- -----------
1 -1
2 -2
As a second example, let's trigger a critical error, which dooms the transaction, making it uncommittable. One
such situation is a write/write conflict. You can trigger it by executing the code in Listing 33-4 in two different sessions.
Listing 33-4. Atomic blocks and transactions: Write/write conflict
begin tran
exec dbo.AtomicBlockDemo 1, 0, null, null
When you run the code in the second session, it triggers the following exception:
Msg 41302, Level 16, State 110, Procedure AtomicBlockDemo, Line 13
The current transaction attempted to update a record that has been updated since this
transaction started. The transaction was aborted.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
If you check @@TRANCOUNT in the second session, you will see that SQL Server terminates the transaction as follows.
@@TRANCOUNT
-----------
0
You should specify that the natively-compiled stored procedure is an atomic block by using BEGIN ATOMIC..END
at the top level of the stored procedure. You should also specify the isolation level for a block and a language, which
dictates the date/time format and system messages language. Finally, you can use three optional properties, such as
DATEFORMAT , DATEFIRST , and DELAYED_DURABILITY to specify the formats and transaction durability option.
Note
atomic blocks are not supported in interpreted t-SQL stored procedures.
 
 
Search WWH ::




Custom Search