ACID Property - FullstackCodingGuy/Developer-Fundamentals GitHub Wiki

Transaction is an unit of work

Atomicity

Atomicity treats an entire transaction as a single unit of work. Database transactions typically involve multiple database operations. If any of them fails, partly or completely, then the transaction’s final result may be incorrect.

Atomic transactions require the successful completion of each operational step. If part of the transaction fails, the entire transaction fails and must run again.

For example, transferring funds from one bank account to another involves subtracting the transfer amount from the first and adding it to the second. If either step fails, then at least one account balance will be wrong. An atomic transaction facing a similar operational failure would not go through at all.

Consistency

Consistency is the enforcement of business rules and data integrity constraints that govern how transactions change a database’s state. The database would become unreliable if transactions were allowed to violate these restrictions. There would be no way to tell whether any two data values were comparable.

For example, a withdrawal amount exceeding an account balance would violate a bank’s rule banning overdrafts. Consistency prevents the transaction from completing, rolling the system back to its previous state.

Isolation

Concurrent transactions, those interacting with the same data at the same time, could undermine data integrity. The ANSI/ISO SQL standard describes three situations that occur depending on the timing of two transactions’ operations:

Dirty reads: Transaction A has updated a row but has not yet committed the change when transaction B retrieves the row.

Non-repeatable reads: Transaction B retrieves a row, transaction A commits updates to the row, and transaction B retrieves the row again.

Phantom reads: Transaction B retrieves a set of rows, transaction A inserts or removes rows from that set, and transaction B retrieves the set of rows again.

ACID-compliant systems use concurrency controls to isolate transactions from each other. Lock-based controls force a new transaction to wait for the current transaction to complete. Multiversion controls use snapshot isolation, letting the new transaction act on the current state while the other transaction works. The control commits the new transaction’s changes provided there’s no conflict; otherwise, it rejects the transaction.

Database systems offer different isolation levels that balance isolation and performance. These levels dictate how read and write operations occur and whether to allow phantom, non-repeatable, or dirty reads.

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.