ACID Properties in RDBMS

If there exists something with name Database then it should some properties. First was Codd's Rules. Then it should be in such a way that it undergoes Normalization. But these two come under creation segment of database mainly. After database is designed, data modifications are done obviously. Such data modifications in RDBMS are done through Transactions. Every update, insert etc are considered as a transaction. These transactions ensure the consistency of your database. Every transaction should adhere to some properties called ACID properties. ACID is an acronym for Atomicity, Consistency, Isolation and Durability. In this post, we'll see about ACID.

Atomicity

A Transaction should be atomic in nature. This means, multiple changes to the database are executed as a single unit by transaction. If a transaction executes 'n' number of changes to database then all should be committed if everything goes right, otherwise no single change should be committed if anything goes wrong in the transaction. If one change is committed and remaining are not committed then such transaction is not said to be atomic because this results in inconsistent state of database. The ability of committing or rolling back a transaction is achieved by Atomicity.

Consistency

Every transaction should leave the database in a consistent state after its completion (A transaction is said to be completed if it is committed or rolled back). The best scenario to explain this property is given in Microsoft SQL Server 2012. I'm reproducing the same scenario here.

Assume there are two tables Table1 and Table2. If Column1 of Table1 is used as foreign key in Table2 then there shouldn't be a scenario where a transaction cannot update column1 in Table1 with a value but it updates the same column in Table2 with the same value. This results in inconsistency of database as there is a violation of Foreign Key.

When a transaction starts, it can take the database into inconsistent state but at its completion it should bring database back to consistent state.

Isolation

The word ISOLATION itself states that a transaction should be independent from other transactions. One transaction should not affect another transaction. RDBMS like SQL Server has different isolation levels like READ COMMITTED,READ UNCOMMITTED, SERIALIZABLE etc. By default database resides in level READ COMMITTED. This means if a transaction is making changes to data then another transaction cannot even read that data until the previous transaction gets completed. Transactions impose locks at table level, row level to ensure that no other transaction can red or modify the data that is being changed by them. Other RDBMS like Oracle, MySQL have locking mechanism imposed on tables.

Durability

Durability is a property which ensures the changes made by a committed transaction reside stable in the database. This can be achieved by writing the changes to a stable disk. For this, there exists a Transaction Log which records every transaction made in the database. Changes made are recorded in Transaction Log before writing them to the disk.