Database Management Systems: ACID Properties

Introduction

Have you ever sent someone money using mobile money (or just transferred money electronically), and the recipient claims that they have not received it, yet your account has been debited? If so, you may want to know more about ACID properties in databases.

ACID stands for Atomicity Consistency Isolation Durability, and is based on the concept of DBMS Transaction.

Look at this screenshot for some reflection about the seriousness of a related incident from some country called Nigeria (which I have never been to). I just hear about Nigeria and the stories. Money lost, JUST LIKE THAT!!! Note that this may not have been caused exactly by the lack of ACIDity in the database, but that cannot be ruled out as well.

Transaction and ACID

A Transaction is a sequence of database operations that satisfies the ACID properties (which can be perceived as a single logical operation on the data). For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.

A Transaction is a single logical unit of work that accesses and/or modifies the contents of a database. Transactions access data using read and write SQL operations.

If a transaction consists of T1 and T2, then the pseudocode is:

Start transaction
T1 (e.g. debit sender’s account)
T2 (e.g. credit recipient’s account)
Commit transaction (or Rollback)

ACID plays an important role in maintaining database consistency before and after execution of transactions. Developers are aware that the transactions they implement in a database must follow ACID properties, otherwise we would end up in chaos.


Atomicity

Atomicity means that either the entire transaction takes place at once or doesn’t take place at all – All or Nothing. A transaction is considered as one unit and must therefore run successfully to completion (commit), or else is does not get executed at all (rollback).

A Transaction is often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single unit, which either succeeds completely or fails completely:

So, when you send mobile money, your account cannot be debited (T1) without the recipient’s account being credited (T2).


Consistency (Correctness)

This means that integrity constraints must be maintained so that the database is consistent before and after the transaction.

Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. A possible rule could be that the total value of all accounts must be 100, which must be maintained before and after a transaction. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct.

Inconsistency can occur if T1 completes but T2 fails. As a result, the transaction is incomplete.


Isolation

Isolation ensures that multiple transactions can occur concurrently without leading to the inconsistency of the database state. Transactions occur independently without interference.

Transactions are often executed concurrently (e.g., multiple transactions reading and writing to a table at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.

Consequently, changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction has been committed.


Durability

Durability is about persistence. It means that once the transaction has been committed, the updates and modifications to the database are stored permanently and they persist even in spite of system failures.