22 October 2014

About Batabase Transactions

DB Transactions has 4 attributes, which is expressed as ACID, where:

A -> Atomicity, means transaction run as an atomic single unit of work, either all is successes and committed or all rollbacked in case of failure.

C -> Consistency, means the transactions should leave the DB data in consistent state, regardless of its success or fail. so, this attribute is ensured using the first attribute.

I -> Isolation, means the user transaction should run in isolation from other users transaction, and no one should affected by others during the single transaction. also this ensures DB consistency.

D -> Durable, means the transaction should be written permanent to the DB after the transaction committed, even if the system crashes afterwards.

When two ore more transactions are operate concurrently on the same data, the following errors might happen:
  1. The first transaction write some data to the DB but still the transaction not committed, a second transaction come and read the modified data, then the first transaction rolled-back.
    This called "Dirty reads", and can prevented by applying the "Isolation" attribute, so every transaction should be isolated from other transactions in terms on data modifications.

  2. The first transaction read some data, then a second transaction come and modify that data and then commit, so the data written to the database. meanwhile the first transaction is still running and when come to read the same data again, it find it changed.
    This called "Non repeatable reads" which means multiple reads by some transaction to the same data is differ. and this can prevented by having some row-level locks on the database, so once a transaction start reading/modifying some data no other transaction cannot use until this transaction ends.

  3. The first transaction read some rows using a certain where condition, then a second transaction come and insert a new row that reside in the where condition area and then committed. When the first transaction come and re-query the first select with that certain where condition, it finds the rows number changed.
    This called "Phantom reads" and this cab be prevented by applying table-level lock whenever a transaction come and start reading/modifying some data in a table, the transaction acquire full-table lock.
This leads to talk about Isolation levels, which are (from less control to more control, and more control means low performance):
  • Read Uncommitted
    Read dirty data, data before being committed by other transactions, leads to all there "Dirty reads", "Non repeatable reads" and "Phantom reads"
  • Read Committed
    Read only committed data, lead to only "Non repeatable reads" and "phantom reads".
  • Repeatable Read (lock data)
    Transaction acquire lock on the data being read (either cell-level or row-level locks), so nobody can change the data until transaction commit or rollback. but still "Phantom reads" can happen.
  • Serializable (lock table)
    Table-level lock. Nobody can touch the whole table until the transaction committed or rolled-back. no isolation violations can happen.

Read more here:
http://en.wikipedia.org/wiki/ACID 
http://en.wikipedia.org/wiki/Isolation_(database_systems)


Source (with my modifications) Spring in action 3rd ed. ch 06



No comments: