ACID Properties

A transaction consists of set of operations that perform a single logical unit of work in a database environment. It may be an an entire program, or part of it or a single SQL Command and it may involve any number of operations on the database. If the transaction is completely successfully, then the database moves from one consistent state to another.

Four properties of a transaction are:

Atomicity: A transaction is regarded as a single logical unit of work in the database rather than collection of separate operations.So, only when all the separate operations succeed does a transaction succeed and is committed to the database. On the other hand, if a single operation fails during the transaction, everything is considered to have failed and must be undone (rolled back) if it has already taken place. In the case of the order-entry system of the Northwind database, when you enter an order into the Orders and Order Details tables, data will be saved together in both tables, or it won’t be saved at all.

Atomicity is maintained in the presence of deadlocks, database software failures, application software failures, CPU failures, disk failures. Atomicity can be turned off at the system level or the session level.

Consistency: The transaction should leave the database in a consistent state — whether or not it completed successfully.In the case of Northwind, you can’t have rows in the Order Details table without a corresponding row in the Orders table, as this would leave the data in an inconsistent state.

Isolation: Every transaction has a well-defined boundary—that is, it is isolated from another transaction.Data modifications made by one transaction must be isolated from the data modifications made by all other transactions. A transaction sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it doesn’t see an intermediate state.

Durability: Data modifications that occur within a successful transaction are kept permanently within the system regardless of what else occurs. A system crash or any other failure must not be allowed to lose the results of a transaction or the contents of the database.Transaction logs are maintained so that should a failure occur the database can be restored to its original state before the failure. As each transaction is completed, a row is entered in the database transaction log.

Example of ACID Properties:

Transaction to transfer $50 from account A to account B:

1. read(A)
2. A := A – 50
3. write(A)
4. read(B)
5. B := B + 50
6. write(B)

Consistency requirement – the sum of A and B is unchanged by the execution of the transaction.

Atomicity requirement — if the transaction fails after step 3 and before step 6, the system should ensure that its updates are not reflected in the database, else an inconsistency will result.

Durability requirement — once the user has been notified that the transaction has completed (i.e., the transfer of the $50 has taken place), the updates to the database by the transaction must persist despite failures.

Isolation requirement — if between steps 3 and 6, another transaction is allowed to access the partially updated database, it will see an inconsistent database (the sum A + B will be less than it should be). Isolation can be ensured trivially by running transactions serially, that is one after the other. However, executing multiple transactions concurrently has significant benefits.

References:
1. Service-Architecture
2. CWI
3. Beginning C# 2008 Databases: From Novice to Professional By Vidya Vrat Agarwal, James Huddleston, Ranga Raghuram, Syed Fahad Gilani, Jon Reid, Jacob Hammer Pedersen