Recently, I encountered topics about isolation levels, so I write this article to revise some basic concepts in transaction and transaction isolation levels.
In a database data operation, a transaction is defined as a single unit of work, which may consist of one or multiple SQL statements. It guarantees the single unit work can be wholly committed to the database or rolled back if any statement in it fails. A transaction should be atomic, consistent, isolated, and durable (ACID).
ACID
Atomicity:
A transaction is a single unit of work that should not be divided into smaller units. It means there are only two results of a transaction: whole commit or whole failure (rollback).
Consistency:
The database is always in a consistent state, ie. the data in the DB can be in the state that the transaction is not committed or the state that the transaction is wholly committed. Before and after transactions, the rules (constraints, cascades, triggers, etc) of the database are always met.
It is tricky to put consistency into ACID. Actually, consistency is correlated with the other three concepts, and AID is applied to guarantee the consistency in a certain level.
Isolation:
The concurrent running transactions are isolated. For example, we have a row row_a being modified by transaction t_a (not committed yet, but a few update statements have been executed). At the same time, if the transaction t_b tries to read row_a, the data has been always in the state before the starting of the whole transaction t_a.
Durability:
The data will remain so after the commit of a transaction, even occurring power loss, crashes or errors. The transactions must be recorded in a non-volatile memory. The non-volatile memory guarantees that the committed transaction result will be written in the disk immediately instead of being stored in the disk cache.
Types of Concurrency Problems in Data Accessing
Lost Update
Transactions overwrite each others' updates.
Dirty Reads
The data is changed by a transaction, but during this process another transaction reads the old value.
Non-repeatable Reads
In a transaction, there is more than one read action, but during these reads, changes are made to the data in other transactions. As a result, two read actions get different results, ie. the read actions are non-repeatable.
Phantom Reads
In a transaction, there is more than one read action, but during these reads, new rows are added in other transactions, which may cause different read results.
Types of Locks
To resolve the concurrency problems, databases usually use locks which will be stored in memory. The choices of lock types introduce several transaction isolation levels to achieve a performance trade-off.
Shared Lock
Exclusive Lock
Update Lock
Transaction Isolation Level
There are four common transaction isolation levels that prevent concurrency problems in data accessing.
Read Uncommitted
Dirty rows (not committed) are allowed to be returned. It provides good performance, but can cause dirty reads.
Read Committed
Read action will wait for the completion of the deletion, updating, or inserting by another transaction.
Repeatable Read
Read action blocks other transaction's update and delete.
Serializable
Read action blocks other transaction's update, delete and insert.
Figure. Capabilities of Isolation Levels to Prevent Concurrency Problems
Recently, I encountered topics about isolation levels, so I write this article to revise some basic concepts in transaction and transaction isolation levels.
In a database data operation, a transaction is defined as a single unit of work, which may consist of one or multiple SQL statements. It guarantees the single unit work can be wholly committed to the database or rolled back if any statement in it fails. A transaction should be atomic, consistent, isolated, and durable (ACID).
ACID
Atomicity:
A transaction is a single unit of work that should not be divided into smaller units. It means there are only two results of a transaction: whole commit or whole failure (rollback).
Consistency:
The database is always in a consistent state, ie. the data in the DB can be in the state that the transaction is not committed or the state that the transaction is wholly committed. Before and after transactions, the rules (constraints, cascades, triggers, etc) of the database are always met. It is tricky to put consistency into ACID. Actually, consistency is correlated with the other three concepts, and AID is applied to guarantee the consistency in a certain level.
Isolation:
The concurrent running transactions are isolated. For example, we have a row
row_abeing modified by transactiont_a(not committed yet, but a fewupdatestatements have been executed). At the same time, if the transactiont_btries to readrow_a, the data has been always in the state before the starting of the whole transactiont_a.Durability:
The data will remain so after the commit of a transaction, even occurring power loss, crashes or errors. The transactions must be recorded in a non-volatile memory. The non-volatile memory guarantees that the committed transaction result will be written in the disk immediately instead of being stored in the disk cache.
Types of Concurrency Problems in Data Accessing
Lost Update
Transactions overwrite each others' updates.
Dirty Reads
The data is changed by a transaction, but during this process another transaction reads the old value.
Non-repeatable Reads
In a transaction, there is more than one read action, but during these reads, changes are made to the data in other transactions. As a result, two read actions get different results, ie. the read actions are non-repeatable.
Phantom Reads
In a transaction, there is more than one read action, but during these reads, new rows are added in other transactions, which may cause different read results.
Types of Locks
To resolve the concurrency problems, databases usually use locks which will be stored in memory. The choices of lock types introduce several transaction isolation levels to achieve a performance trade-off.
Shared Lock
Exclusive Lock
Update Lock
Transaction Isolation Level
There are four common transaction isolation levels that prevent concurrency problems in data accessing.
Read Uncommitted
Dirty rows (not committed) are allowed to be returned. It provides good performance, but can cause dirty reads.
Read Committed
Read action will wait for the completion of the deletion, updating, or inserting by another transaction.
Repeatable Read
Read action blocks other transaction's update and delete.
Serializable
Read action blocks other transaction's update, delete and insert.
Figure. Capabilities of Isolation Levels to Prevent Concurrency Problems