Ryoma Taniyama

paying with cash

Transaction Isolation Levels

A Transaction and ACID Properties

A transaction is a unit of work. It bundles multiple steps into a single, all-or-nothing operation. In the context of DBMS, a transaction is composed of one or more SQL statements.

There’re four desirable properties we want transactions to have, which are known as ACID properties; atomicity, consistency, isolation, and durability.

  • Atomicity ensures that all changes included in a transaction are performed as if they are a single operations.
  • Consistency takes a database from one consistent state to another consistent state.
  • Isolation ensures that the intermediate state of a transaction is invisible to other transactions. In other words, isolation aims to guarantee consistency in terms of multiple concurrent transactions. Isolation can be achieved through concurrency control methods that we’ll discuss.
  • Durability ensures that changes made by a transaction are permanent, even after a system is impaired.

Concurrency Control Models

There’re two well-known concurrency control models for a database; optimistic concurrency control, and pessimistic concurrency control (pessimistic locking).

In optimistic concurrency control, multiple users are allowed to attempt to update the same record without checking if others are updating simultaneously. Their attempts are validated only when committing, so if another user is in progress to update, they are informed that their attempts fail due to conflicts.

On the other hand, in pessimistic concurrency control, also known as pessimistic locking, a transaction holds a lock on data during its update in order to prevent other transactions from updating the same data simultaneously. Other transactions must wait until data locked by another transaction is unlocked. Additionally, two types of lock are used in pessimistic concurrency control; shared lock, and exclusive lock. A shared lock, also known as a read lock, is used to ensure read consistency, which prevents other transactions from updating during its reading. Whereas, an exclusive lock, or a write lock prevents other transactions from reading and updating so that others wouldn’t read inconsistent data during its updating.

While some DBMS softwares ensure their isolation through pessimistic concurrency control, others do through multiversion concurrency control model (MVCC). In MVCC, each SQL statement sees a snapshot of data as it was some time ago.

Regardless of whether they adopt locking or MVCC, databases typically provide several options with respect to the extent to which one transaction is isolated from another, which are known as transaction isolation levels.

Undesirable Phenomena

Transaction isolation levels represent the extent to which a transaction isolation succeeds. Each transaction isolation level is defined by the presence or absence of the following phenomena, which are not desirable in terms of individual transactions’ consistency; dirty read, non-repeatable read, and phantom read.

Transaction \(A\) reads data that has been modified and haven’t yet committed by transaction \(B\). Therefore, if transaction \(B\) rollbacks that modification after transaction \(A\) reads it, data will be inconsistent in terms of the entire transactions. This phenomenon is called a dirty read.

Next, suppose that transaction \(A\) is supposed to read the same data twice. Between the initial read and re-read by transaction \(A\), if transaction \(B\) modifies the data, it leads to inconsistency. This phenomenon is known as a non-repeatable read.

And then, transaction \(A\) is supposed to read data using the same WHERE condition twice. Between the first and second search by transaction \(A\), if transaction \(B\) modifies the data which satisfy the condition, it leads to inconsistency, known as a phantom read.

Transaction Isolation Levels

The read-committed level ensures that a dirty read won’t happen. In the repeatable-read level, dirty-reads and non-repeatable reads won’t occur. Finally, we are guaranteed that any phenomena won’t occur under serializable level.

Each transaction isolation level can be implemented using the concurrency control model adopted by each DBMS, as we discussed earlier.

Reference

Posted

in

Tags: