On Transactions and Atomic Operations
On transactions, and Atomic
Operations
Gail Shaw
Entelect
Transactions
The boundary of a unit of work
A transaction completes entirely, or not at all
A transaction can be committed, made part of the permanent database state,
or rolled back, undone and leaving the database as if it had never run.
Transactional Properties
A unit of work must exhibit four properties to be considered a transaction
Atomicity (A)
Consistency (C)
Isolation (I)
Durability (D)
Together these are referred to as ACID
Transactional Types
In SQL Server, transactions have three forms
Auto-commit. Default mode. Every statement is an individual transaction
Implicit Transactions. First statement starts a transaction, has to be explicitly committed
Explicit Transactions. Start a transaction with BEGIN TRANSACTION, has to be explicitly
committed.
Transaction Guarantees
If the COMMIT is run, all changes made within the transaction are fully durable
i.e. part of the persistent database state
If the connection terminates without the COMMIT having run, the transaction is
rolled back
All changes made within the transaction are undone
If SQL Server restarts, upon restart it will undo all data modifications which were
part of transactions that did not commit
Nested transactions
If transactions are nested, only the outer-most BEGIN TRANSACTION actually
starts a transaction
Subsequent ones just increment a counter
Only the final commit actually commits the transaction
The earlier ones just decrement a counter
A single rollback anywhere, however, rolls back everything
Nested transactions are a lie!
Demo
Named Transactions
A name specified on a BEGIN TRANSACTION is for documentation purposes
It will appear in a deadlock graph
It will appear in transaction-related DMVs
It does not change the behaviour of the transaction
Demo
Transactions and Locking
Locks taken by a data modification are held until the end of the transaction
In the default isolation level, shared locks are released at the end of the
statement
Need REPEATABLE READ isolation level for shared locks to be held until the end of the
transaction
There is nothing about a transaction which prevents other people from reading
what you are reading
Beware of the read and then update pattern
Demo
Transactions and Errors
From the earlier discussion, it would be easy to conclude that errors
encountered during a transaction cause a rollback.
Books Online seems to imply that as well.
However that is not the case
Demo
Transactions and Errors
When wrapping multiple data modifications within a transaction for atomicity,
error handling is essential
Two ways of doing it
Automatic, let SQL handle things
Manual, explicitly defining what happens when there’s an error
Manual error handling
TRY … CATCH blocks
Any error which occurs in the TRY block causes the execution to be transferred
to the CATCH block
In the CATCH block, the error can be checked and either the transaction retried
or rolled back.
Can also re-throw the error for the application, or log it
Demo
Caveats, exceptions and special cases
There are some database operations which do not roll back.
Page splits, identity column increments, sequence increments
Table variables do not participate in user transactions
The DELAYED DURABILITY database option on SQL Server 2014 allows for
committed transactions to be lost on a restart
There are some errors which DO automatically roll back transactions, most DDL-
related errors
In Conclusion
Transactions provide a way for a number of data modifications be treated as a
single atomic operation
A transaction is automatically rolled back if the connection is terminated before
it completes
Errors do not cause a transaction to roll back by default
Can be set to happen
Can do manual error handling
Resources
Demos on Github
https://github.com/GilaMonsterZA/Demos
Mini blog series on transactions
http://sqlinthewild.co.za/index.php/2015/11/03/a-mess-of-nested-transactions/
(first in
series)
Concurrency is hard
http://source.entelect.co.za/why-is-this-upsert-code-broken
Automatic error handling
Controlled by the XACT_ABORT setting
If that setting is on, any error which occurs within a transaction will result in the
transaction being rolled back
Still needs to be some error handling, but that can be in the calling app
Transactions in databases are crucial for ensuring data integrity. A unit of work is completed entirely or rolled back if needed, preserving consistency. Transactions exhibit properties like atomicity, consistency, isolation, and durability - forming the ACID model. Explore different types of transactions in SQL Server and understand the guarantees they provide, including dealing with nested transactions. Learn about named transactions and their impact on database operations.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
On transactions, and Atomic Operations Gail Shaw Entelect
Transactions The boundary of a unit of work A transaction completes entirely, or not at all A transaction can be committed, made part of the permanent database state, or rolled back, undone and leaving the database as if it had never run.
Transactional Properties A unit of work must exhibit four properties to be considered a transaction Atomicity (A) Consistency (C) Isolation (I) Durability (D) Together these are referred to as ACID
Transactional Types In SQL Server, transactions have three forms Auto-commit. Default mode. Every statement is an individual transaction Implicit Transactions. First statement starts a transaction, has to be explicitly committed Explicit Transactions. Start a transaction with BEGIN TRANSACTION, has to be explicitly committed.
Transaction Guarantees If the COMMIT is run, all changes made within the transaction are fully durable i.e. part of the persistent database state If the connection terminates without the COMMIT having run, the transaction is rolled back All changes made within the transaction are undone If SQL Server restarts, upon restart it will undo all data modifications which were part of transactions that did not commit
Nested transactions If transactions are nested, only the outer-most BEGIN TRANSACTION actually starts a transaction Subsequent ones just increment a counter Only the final commit actually commits the transaction The earlier ones just decrement a counter A single rollback anywhere, however, rolls back everything Nested transactions are a lie!
Named Transactions A name specified on a BEGIN TRANSACTION is for documentation purposes It will appear in a deadlock graph It will appear in transaction-related DMVs It does not change the behaviour of the transaction
Transactions and Locking Locks taken by a data modification are held until the end of the transaction In the default isolation level, shared locks are released at the end of the statement Need REPEATABLE READ isolation level for shared locks to be held until the end of the transaction There is nothing about a transaction which prevents other people from reading what you are reading Beware of the read and then update pattern
Transactions and Errors From the earlier discussion, it would be easy to conclude that errors encountered during a transaction cause a rollback. Books Online seems to imply that as well. However that is not the case
Transactions and Errors When wrapping multiple data modifications within a transaction for atomicity, error handling is essential Two ways of doing it Automatic, let SQL handle things Manual, explicitly defining what happens when there s an error
Manual error handling TRY CATCH blocks Any error which occurs in the TRY block causes the execution to be transferred to the CATCH block In the CATCH block, the error can be checked and either the transaction retried or rolled back. Can also re-throw the error for the application, or log it
Caveats, exceptions and special cases There are some database operations which do not roll back. Page splits, identity column increments, sequence increments Table variables do not participate in user transactions The DELAYED DURABILITY database option on SQL Server 2014 allows for committed transactions to be lost on a restart There are some errors which DO automatically roll back transactions, most DDL- related errors
In Conclusion Transactions provide a way for a number of data modifications be treated as a single atomic operation A transaction is automatically rolled back if the connection is terminated before it completes Errors do not cause a transaction to roll back by default Can be set to happen Can do manual error handling
Resources Demos on Github https://github.com/GilaMonsterZA/Demos Mini blog series on transactions http://sqlinthewild.co.za/index.php/2015/11/03/a-mess-of-nested-transactions/ (first in series) Concurrency is hard http://source.entelect.co.za/why-is-this-upsert-code-broken
Automatic error handling Controlled by the XACT_ABORT setting If that setting is on, any error which occurs within a transaction will result in the transaction being rolled back Still needs to be some error handling, but that can be in the calling app
