Saturday, November 20, 2010

Transactions - ACID

set of properties that guarantee database transactions are processed reliably ie. Transaction

A - atomicity, 
C - consistency, 
I - isolation, 
D - durability

 For example, a transfer of funds from one bank account to another, even though that might involve multiple changes (such as debiting one account and crediting another), is a single transaction.

Atomic - requires that database modifications must follow an "all or nothing" rule. Each transaction is said to be atomic.

Consistent - ensures that any transaction the database performs will take it from one consistent state to another. The consistency rule applies only to integrity rules that are within its scope. Mechanisms include:
  • abort the transaction, rolling back to the consistent, prior state;
  • delete all records that reference the deleted record (this is known as cascade delete); or,
  • nullify the relevant fields in all records that point to the deleted record.
Isolation - requirement that other operations cannot access data that has been modified during a transaction that has not yet completed.
The question of isolation occurs in case of concurrent transactions (multiple transactions occurring at the same time). Each transaction must remain unaware of other concurrently executing transactions, except that one transaction may be forced to wait for the completion of another transaction that has modified data that the waiting transaction requires. If the isolation system does not exist, then the data could be put into an inconsistent state => leads to dirty reads.

Durability - ability of the DBMS to recover the committed transaction updates against any kind of system failure (hardware or software).
guarantee that once the user has been notified of a transaction's success the transaction will not be lost, the transaction's data changes will survive system failure, and that all integrity constraints have been satisfied, so the DBMS won't need to reverse the transaction. Many DBMSs implement durability by writing transactions into a transaction log that can be reprocessed to recreate the system state right before any later failure. A transaction is deemed committed only after it is entered in the log.

Achieving ACID:
  • Locking vs multiversioning
  • Distributed transactions
Locking vs multiversioning 
Locking means that the transaction marks the data that it accesses so that the DBMS knows not to allow other transactions to modify it until the first transaction succeeds or fails. The lock must always be acquired before processing data, including data that are read but not modified. Non-trivial transactions typically require a large number of locks, resulting in substantial overhead as well as blocking other transactions. For example, if user A is running a transaction that has to read a row of data that user B wants to modify, user B must wait until user A's transaction completes. Two phase locking is often applied to guarantee full isolation.
In Multiversioning, database provides each reading transaction the prior, unmodified version of data that is being modified by another active transaction. This allows readers to operate without acquiring locks. I.e., writing transactions do not block reading transactions, and readers do not block writers. Going back to the example, when user A's transaction requests data that user B is modifying, the database provides A with the version of that data that existed when user B started his transaction. User A gets a consistent view of the database even if other users are changing data. Snapshot isolation is similar to multiversioning where reference done on a snapshot of the data.
Distributed transactions
2-phase commit is commonly used solution. Here,  in the first phase, one node (the coordinator) interrogates the other nodes (the participants) and only when all reply that they are prepared does the coordinator, in the second phase, formalize the transaction.
unit of work performed within a enterprise system a database and treated in a coherent and reliable way independent of other transactions.
Follows ACID properties.

Also, refer related blogs:
Snapshot Isolation
Compensation Transaction

1 comment: