ACID properties of transactions
Atomicity – Either all modifications are performed, or none
Consistency – When completed, transactions leave all data and all related structures in a consistent state
Isolation – Transactions don’t see other’s transaction’s uncompleted work (intermediate state)
Durability – Transactions persist despite of system failure
ACID transactions in SQL server
- Handled automatically across databases on the same instance
- Handled through MSDTC automatically or explicitly across instances
- Controlled by Transaction Mode of the session
- Can be defined explicitly, at connection level and at server level
- Requires error handling logic
- Programming errors do not cause transaction rollback unless XACT_ABORT setting on
Transactions mode in SQL Server 2008
- Autocommit transactions (default)
- – Statement level implicit transaction
- – Each statement commits as a single unit
- Explicit transactions (user-defined)
- – BEGIN TRANSATION
- – COMMIT / ROLLBACK TRANSACTION
- Implicit transactions
- – Session Level Setting SET IMPLICIT_TRANSACTIONS ON
Related Posts
- Oracle PL/SQL: Transactions
- SQL Server 2008 Concurrency Problems and Isolation
- Automatic Backup Compression in SQL Server 2008
- An Introduction to Microsoft SQL Server 2008 – Part 1
- Consistency Errors in MS SQL Server 2000
Tags: ACID Property, ACID Transaction, SQL Server 2008, Transaction Modes in SQL Server 2008
