ACID & Database Transactions 101: Keeping Data Consistent in Concurrent Systems

Concurrency Control: Pessimistic vs. Optimistic

When multiple transactions might conflict, the database needs a strategy to handle it:

Pessimistic Concurrency Control (Locks)

Pessimistic approach assumes conflicts are likely, so it actively prevents them by locking data up front.

When a transaction wants to read or modify some data, the database will lock that data (like a specific row, set of rows, or even a whole table) so that other transactions cannot modify it until the lock is released (usually when the transaction commits or aborts).

It’s like putting a “do not disturb” sign on the data.

• If Transaction A has a write lock on a row, Transaction B wanting to read or write that row must wait until A finishes (commits or rolls back). This guarantees no other transaction can change or even see intermediate changes to that row.

• Read locks (also called shared locks) may allow multiple concurrent reads on the same data, but block any writes until the reading is done.

Pessimistic locking is very safe – it stops conflicts before they happen by essentially doing serial execution when needed.

Our flight seat example under pessimistic control: if User A’s transaction locks the seat record to book it, User B’s transaction attempting the same seat will be paused (blocked) until A commits.

Only one succeeds and the other will either wait or get a failure, ensuring no double-booking.

The downside is reduced concurrency: transactions can be forced to wait, and if not carefully managed, locks can lead to deadlock (two transactions waiting on each other’s locks).

Optimistic Concurrency Control (Versioning)

Optimistic approach assumes conflicts are rare, so it lets transactions proceed without locks and only checks for conflicts at the end (when trying to commit).

Each transaction works on the data as if alone, and before committing, the database verifies that no other transaction meddled with the same data in the meantime.

If a conflict is detected, the committing transaction will abort (and typically retry). This way, no locking overhead is incurred during the transaction, which can improve performance in low-contention scenarios.

Commonly, this is implemented with data versions or timestamps. For example, every row might have a version number or last-updated timestamp. Transaction A reads a row (with version N) and makes some changes. When A goes to commit, the database checks the current version of that row:

• If it’s still N, no other transaction touched it, so A’s commit succeeds (the version is incremented to N+1).

• If it’s not N (meaning some other transaction updated that row and it’s now version N+1 or more), then a conflict occurred. A will fail to commit and might have to restart using the new data.

Using our seat booking example optimistically: both User A and User B could “think” they booked the last seat because neither locked it.

When both try to finalize the booking, the system sees that one seat record was already changed by the first commit – thus it rejects the second user’s transaction (which would then retry or show an error “sorry, seat no longer available”).

Optimistic control avoids upfront locking, which means higher throughput and no waiting when conflicts truly are infrequent.

However, if conflicts do happen often, transactions will keep aborting and retrying, which can become inefficient. It’s best in scenarios like many read-only operations or mostly disjoint writes.

In practice, databases often use Multiversion Concurrency Control (MVCC), a form of optimistic control where readers see a snapshot of the database and writers create new versions of data.

MVCC (used in systems like PostgreSQL, MySQL InnoDB, Oracle, etc.) allows readers and writers to not block each other: readers get the old version of data while a writer is working, and upon commit the new version is saved.

This yields snapshot isolation (no dirty reads, etc.) and often balances performance and consistency well.

Further Learning

To deepen your understanding of system design and databases (especially for technical interviews), check out these courses by DesignGurus:

1. Grokking System Design Fundamentals – Foundational system design concepts for scalable systems.

2. Grokking the System Design Interview – Prepare for system design interview questions with real-world examples.

3. Grokking Database Fundamentals for Tech Interviews – Learn core database concepts (like ACID, indexing, etc.) in an interview-focused way.

4. Grokking SQL for Tech Interviews – Practical SQL query and database problem-solving skills for coding interviews.

5. Grokking Relational Database Design and Modeling for Software Engineers – Master database schema design, normalization, and modeling techniques.

FAQs

Q1. What does ACID mean in databases?

ACID stands for Atomicity, Consistency, Isolation, Durability. These properties ensure that database transactions are processed reliably. Atomicity means all-or-nothing execution of each transaction. Consistency means transactions preserve database rules/constraints. Isolation means transactions don’t interfere with each other (concurrent transactions act as if executed one by one). Durability means once a transaction commits, its changes persist permanently.

Q2. Why are transactions important in databases?

Transactions guarantee data integrity and consistency. They ensure that a series of operations either all succeed or all fail together, so the database isn’t left in a partial state. This is critical for scenarios like financial transfers, orders, or any multi-step process – you wouldn’t want money deducted without being deposited elsewhere, or an order placed without inventory being updated. Transactions also allow recovery from errors: if something goes wrong, the database can roll back to a safe state.

Q3. What’s the difference between pessimistic and optimistic concurrency control?

Pessimistic concurrency control uses locks to prevent conflicts before they happen – it assumes collisions are likely. For example, if one transaction is editing a row, others must wait (it’s like one person at a time). Optimistic concurrency control assumes conflicts are rare and doesn’t lock data during the transaction. Instead, it lets transactions execute concurrently and checks at commit time for conflicts. If a conflict is detected (another transaction changed the data first), the committing transaction will abort and retry. Pessimistic = safer but can slow things down with waiting; optimistic = faster when few conflicts but may require retries if conflicts occur.

Q4. What are isolation levels in a database?

Isolation levels are settings that control how much a transaction is isolated from others. Common levels (from lowest to highest isolation) are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Lower levels allow more concurrency but risk phenomena like dirty reads or non-repeatable reads. Higher levels (Serializable being the strictest) ensure transactions execute with full isolation (as if one after the other) to prevent anomalies, at the cost of throughput. For instance, Read Committed prevents reading uncommitted data (no dirty reads), and Serializable prevents all of the anomalies but may use more locking or abort more transactions to do so.

Q5. How do databases ensure durability?

Databases ensure durability by writing data to stable storage when a transaction commits. Techniques include WAL (Write-Ahead Logging) and transaction logs that record changes before they are applied, so if a crash happens, the database can recover committed transactions from the log. Many systems also use replication (writing data to multiple servers/disks) and periodic backups. Essentially, once you get a “commit succeeded” message, the database has made sure it can recreate that data even if hardware fails immediately after.