Transactions and Concurrency

ACID Properties

• In relational databases, a transaction is a sequence of operations that must be treated as a single unit. Either everything succeeds, or nothing does.

• ACID (Atomicity, Consistency, Isolation, and Durability) is a set of four guarantees that ensure data remains correct, consistent, and reliable, even when multiple users access it at the same time.

Atomicity

• Ensures that a transaction is fully completed or fully rolled back. If any part of the transaction fails, the entire transaction fails.

• For instance, when transferring $100 between two bank accounts:

  • Subtract 100 from Account A.

  • Add 100 to Account B.

  • If the second step fails, the first step must not be saved.

-- If step 2 fails, the system ROLLBACKS everything

BEGIN;
-- Step 1
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Step 2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Consistency

• Ensures a transaction cannot violate rules defined in the database such as:

  • Constraints (NOT NULL, CHECK, UNIQUE).

  • Foreign Key (FK) relationships.

  • Data types.

  • Business rules.

• For instance, if a table requires age >= 0, sql below will fail because it violates a rule. The database will not apply the change and remains consistent.

-- Failure due to invalid value

INSERT INTO users (name, age) VALUES ('John', -5);

Isolation

• Ensures that concurrent transactions behave as if they were executed one at a time, even though they may run simultaneously.

• For instance, two users buying the last ticket at the same time:

  • Without isolation both might see the item as available and both get the ticket.

  • With proper isolation one succeeds, the other fails or waits.

• Databases use locking, MVCC (Multi-Version Concurrency Control), and isolation levels (Read Committed, Repeatable Read, Serializable) to enforce this.

Durability

• Guarantees that once a transaction is committed, the data is permanently stored, even if:

  • The system crashes.

  • Power fails.

  • The server restarts.

• Databases achieve this using:

  • Write-ahead logs (WAL).

  • Redo logs.

  • Disk persistence.

• After COMMIT, the transaction is safely stored. even if power goes out immediately after, the data is recoverable.

Summary

Property	Meaning	Example
Atomicity	All or nothing	Bank transfer must complete fully
Consistency	Must follow rules	Cannot insert invalid data
Isolation	Transactions don’t conflict	Two people updating the same row
Durability	Data survives crashes	Committed data is safe

Locking Basics

• Databases use locks to control how multiple transactions access the same data at the same time.

• Locks prevent conflicts like:

  • Lost updates.

  • Dirty reads (Uncommitted changes).

  • Inconsistent results.

There are two common types of locks:

Shared Lock (Read Lock)

• Applied when reading data.

• Multiple transactions can hold shared locks on the same row.

• But no one can write to the row while shared locks are active.

• Example:

  • Transaction A reads a row => shared lock is placed.

  • Transaction B can also read => shared lock allowed.

  • Transaction C tries to update => must wait until both shared locks are released.

Exclusive Lock (Write Lock)

• Applied when updating / deleting a row.

• Only one transaction can hold it.

• Blocks other reads and writes to prevent inconsistent data.

• Example:

• Transaction A updates a row => exclusive lock created.

• Transaction B tries to read => must wait until A finishes.

Why Locking Matters

• Without locking, transactions could interfere and create inconsistent / incorrect results.

• Databases combine locks with isolation levels to manage concurrency safely.

Isolation Levels

• Isolation levels define how much one transaction is allowed to see the intermediate changes of another transaction.

There are 4 standard isolation levels, but this course will cover Read Committed, Repeatable Read and Serializable.

Read Committed

• Guarantees:

  • You cannot read uncommitted data (No dirty reads).

  • But you can read data modified by another transaction after it commits.

  • This means repeat queries might see different results.

  • Good balance of safety and performance.

• Example:

  • Transaction A reads a product price and sees $100.

  • Transaction B updates price to $120 and commits.

  • Transaction A reads again and sees $120.

Repeatable Read

• Guarantees:

  • No dirty reads (Uncommitted data).

  • No non-repeatable reads.

  • A transaction sees a consistent snapshot.

  • If you read a row twice, you will always see the same value, even if another transaction commits a change.

• Example:

  • Transaction A reads a product price and sees $100.

  • Transaction B updates price to $120 and commits.

  • Transaction A reads again and still sees $100 (Snapshot data).

Serializable (Strictest)

• This level simulates transactions as if they ran one after another.

• Strongest isolation, but most expensive.

• May result in more transaction rollbacks due to conflicts.

• Prevents:

  • Dirty reads.

  • Non-repeatable reads.

  • Phantom reads.

  • Conflicts in general.

• Example:

  • Transaction A Queries "How many seats left for event X?" => 1 seat left.

  • Transaction B: Tries to insert a new reservation => database blocks / aborts it because A's query logically conflicts.

Isloation Summary Table

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Description
Read Committed	No	Yes	Yes	Most common, balances performance and safety
Repeatable Read	No	No	Depends (MySQL handles phantoms)	Consistent snapshot
Serializable	No	No	No	Safest, strictest, slowest

tip

• Low isolation: More concurrency, less safety.

• High isolation: More safety, lower concurrency.