ACID Transactions

Vishal Gaikwad — Sat, 19 Oct 2024 07:09:38 +0000

What is a Transaction?
A transaction is nothing but a collection of queries.
E.g. Account deposit (SELECT, UPDATE)

Send $100 FROM Account 1 to Account 2

Now we know what is transactions, Let's discuss the ACID consistency models.

The term ACID stands for Atomicity, Consistency, Isolation, and Durability. ACID properties are used for maintaining data integrity during transaction processing.

In order to maintain consistency before and after a transaction, relational databases follow ACID properties.

Atomicity

All operations in a transaction succeed or every operation is rolled back.

Let's say you have started a transaction as above and your system or database crashed, then the transaction will roll back all queries if one of the queries failed.

2.Consistency

Consistency guarantees that a transaction brings the database from one valid state to another. If the database is consistent before a transaction starts, it will remain consistent after the transaction is completed. Any violation of integrity constraints will result in the transaction being rolled back.

3.Isolation

Isolation is the ability of the database to concurrently process multiple transactions in a way that changes made in one does not affect the other.

To explain the isolation, I want you guys to answer a question.
Can my inflight transaction see changes made by other transactions?
There are multiple ways in which concurrent transactions can interfere with each other

Ongoing transactions see the changes made by other transactions
A committed transaction which ran in parallel with an inflight transaction.

As a result of these, we get read views, which we call Read Phenomena. Below are some of read phenomena that can occur at various isolation levels.

Dirty Reads — It happens when concurrent transactions can read the changes which has not been committed yet.

Non-repeatable Reads — It happens when transactions involving multiple reads ran in parallel, with a transaction making changes and commit in-between multiple reads.

Phantom Reads — It is similar to non-repeatable reads, applies on range queries or queries involving multiple rows, where we get different set of data due to insertion/deletion of certain rows which satisfies the query.

To solve the read phenomena, ANSI came up with 4 Isolation levels with increasing strictness to handle these read phenomena.

Isolation :- There are 4 level of Isolation.

Read uncommitted
No Isolation, any change from the outside is visible to the
transaction.

Read committed
Each query in a transaction only sees committed stuff

Repeatable Read
Each query in a transaction only sees committed updates at the
beginning of transaction

Serializable
Transactions are serialized

Isolation levels vs Read phenomena

4.Durability

Durability simply means that once a transaction commits its changes, those changes become part of the database’s permanent record, even in the event of a power outage or other system failures. Database systems usually achieve durability by moving in-memory data to non-volatile storage.

DEV Community: Vishal Gaikwad

ACID Transactions