Tag Archives: Databases

Isolation levels of a database

In my last post I talked about ACID properties of the database. Out of these, Isolation is one of the most important (and a bit complex) property as this makes sure that no “dirty-ready” occur. There can be various levels of database isolation.

1. Read Uncommitted: Lowest level of isolation, does not implement any checks, a transaction can read while other transaction is writing and hence dirty read can happen.

2. Read Committed. Reads only committed data. Handles dirty read problem. But say in same transaction, the data for a row in table is read multiple times, and if the data gets updated by some other transaction and committed, between the gap period of the two reads in this transaction, the transaction has read different data at different time intervals for same row.

e.g. select name from employee where id=10;

— do something which takes time

select name from employee where id=10;

commit;

The data can change between the two reads

3. Repeatable Reads: This solves the problem mentioned above with the Read Committed. That is, this level does not allow any modifications or deletion in the data which is being accessed by some other transaction. So in last example, if we read the same data multiple times, it is guaranteed to return the same info. Though we cannot update or delete the data in table being accessed by a transaction, we can still add more data. So if the query was

select name from employee;

instead of select name from employee where id=10;

we still could have different data set.

4. Serializable: This is the highest level of restriction added on transactions. This states, that no updation, deletion or addition can happen on a dataset/ table which is being accessed by another transaction. This will solve the problem which is mentioned in previous example. But the solution comes at a high cost as this restriction level will slow down the rate of transactions being executed.

ACID test for your database

ACID is a set of properties that each database system needs to guarantee in order to make sure that all the transactions will be processed reliably

Atomicity: A transaction is always committed as a full. That means if the transaction had 10 statements, all 10 are executed fully and committed. A partial execution of transaction will never occur.

Consistency: Database state is always stable. So if a transaction moves database from state A to B, both states are stable.

Isolation: All transactions will execute in isolation and do not interfere with one another (one transaction is updating the data and other is reading at the same time – dirty read is handled).

Durability: Once a transaction is committed, the changes will not be reverted by some hardware or software failure.