SQL Transactions and ACID Properties: Complete Guide

1. What is a Transaction?

A database transaction is a logical unit of work (LUW) that bundles one or more SQL statements into an indivisible execution block.

Consider a standard banking fund transfer: account A transfers $500 to account B. This business operation requires two distinct SQL statements:

Deduct $500 from account A balance.
Credit $500 to account B balance.

If a power failure, network timeout, or server crash occurs after Step 1 but before Step 2, $500 would vanish into thin air. A transaction prevents this catastrophe by guaranteeing that either both statements succeed together or the entire sequence is completely undone.

`The All-or-Nothing Principle`

Single Unit:A transaction treats multiple separate insert, update, or delete commands as an inseparable bundle.
Failure Safety:If any individual step encounters an error, the database engine returns all tables to their exact pre-transaction state.

2. ACID Properties in DBMS

To ensure reliable data processing across multi-user concurrent systems, relational database engines enforce four core guarantees known by the acronym ACID:

Property	Meaning	Implementation Mechanism
Atomicity	All statements succeed or none do (All-or-Nothing)	Undo Log / Rollback Segments
Consistency	Data moves from one valid state to another, preserving constraints	Foreign keys, unique indexes, CHECK constraints
Isolation	Concurrent transactions do not see partial uncommitted writes	Locks and Multi-Version Concurrency Control (MVCC)
Durability	Committed data survives crashes, power loss, and hardware failure	Write-Ahead Logging (WAL) and disk flushes

3. BEGIN, COMMIT, and ROLLBACK

Explicit transactions use three primary control statements:

BEGIN TRANSACTION (or START TRANSACTION): Demarcates the start of an explicit transaction block.
COMMIT: Persists all pending modifications made during the transaction permanently to disk and releases database locks.
ROLLBACK: Erases all pending modifications, restoring data rows to their state prior to the BEGIN command.

Executing Explicit Transaction with ROLLBACK

SQL

Observe how uncommitted updates are completely discarded upon rollback

`Commit Permanence`

Irreversible Point:Once COMMIT finishes, a ROLLBACK command can no longer undo those modifications.
Subsequent Reversals:To undo a committed transaction, developers must execute a compensating transaction.

4. SAVEPOINT

A SAVEPOINT establishes a named intermediate marker within an ongoing transaction. If an error occurs midway through a multi-step routine, you can roll back to the specific savepoint without aborting the entire transaction.

SAVEPOINT marker_name;
ROLLBACK TO SAVEPOINT marker_name;
RELEASE SAVEPOINT marker_name;

Partial Rollback Using SAVEPOINT

SQL

Revert downstream mistakes while preserving earlier valid transaction changes

5. Autocommit Mode

By default, almost all relational databases (MySQL, PostgreSQL, SQLite, SQL Server) operate in autocommit mode.

In autocommit mode, every individual SQL statement is treated as a self-contained transaction that commits automatically the instant it completes.

Executing UPDATE employees SET salary = 90000 WHERE id = 1; commits immediately. You cannot issue a ROLLBACK afterwards.
To execute multi-statement transactions, you must explicitly issue BEGIN TRANSACTION or disable autocommit for the current session (SET autocommit = 0; in MySQL).

6. Transaction Isolation Levels

When thousands of users interact with a database simultaneously, transactions execute concurrently. The SQL standard defines four transaction isolation levels to balance concurrency performance against data consistency:

Read Uncommitted: Lowest isolation. Transactions can read uncommitted data written by other ongoing transactions.
Read Committed: Default in PostgreSQL, SQL Server, and Oracle. A query only reads rows that were committed before the query began.
Repeatable Read: Default in MySQL InnoDB. Guarantees that if a transaction reads a row twice, it sees identical values both times.
Serializable: Highest isolation level. Emulates strict sequential execution as if transactions ran one at a time, completely preventing concurrency conflicts.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

7. Concurrency Phenomena: Dirty, Non-repeatable, and Phantom Reads

The four isolation levels protect against three specific concurrency anomalies:

Dirty Read: Transaction A reads data modified by Transaction B before B commits. If B rolls back, Transaction A holds phantom, uncommitted data.
Non-repeatable Read: Transaction A reads a row. Transaction B updates or deletes that same row and commits. When Transaction A re-reads the row, its values have changed.
Phantom Read: Transaction A reads a set of rows matching a range filter (e.g., salary > 100000). Transaction B inserts a new row matching that range and commits. When Transaction A re-executes the query, a new "phantom" row appears.

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read
Read Uncommitted	Allowed	Allowed	Allowed
Read Committed	Prevented	Allowed	Allowed
Repeatable Read	Prevented	Prevented	Allowed (Prevented in InnoDB)
Serializable	Prevented	Prevented	Prevented

8. Locks in SQL (Shared, Exclusive, Row-level, Table-level)

Database engines use locks to prevent concurrent transactions from corrupting shared data structures:

Lock Types

Shared Lock (S-Lock / Read Lock): Acquired when reading data. Multiple transactions can hold shared locks on the same row concurrently, but write operations are blocked.
Exclusive Lock (X-Lock / Write Lock): Acquired during INSERT, UPDATE, or DELETE. Only one transaction can hold an exclusive lock. All other reads and writes are blocked until the lock is released.

Lock Granularity

Row-level Lock: Locks only the specific rows being modified. Offers highest concurrency.
Page-level Lock: Locks an entire 8KB disk page containing multiple rows.
Table-level Lock: Locks the entire table, blocking all other write access.

`Two-Phase Locking (2PL)`

Growing Phase:A transaction acquires locks as it reads and updates records without releasing any locks.
Shrinking Phase:Upon COMMIT or ROLLBACK, the transaction releases all acquired locks simultaneously.

9. Deadlock in SQL

A deadlock is a mutual blocking condition where two or more transactions wait indefinitely for locks held by each other:

Transaction 1 holds a lock on Row A and requests a lock on Row B.
Transaction 2 holds a lock on Row B and requests a lock on Row A.
Neither transaction can proceed, creating an infinite waiting cycle.

How Databases Resolve Deadlocks

Database engines run background deadlock detection algorithms using a Wait-For Graph. When the engine identifies a cyclic dependency, it chooses one transaction as the deadlock victim, rolls it back automatically, and sends an error to the client, allowing the other transaction to finish.

`Deadlock Prevention Guidelines`

Consistent Access Order:Always update tables and rows in the exact same order across all application services.
Keep Transactions Short:Never include external HTTP calls or user prompts inside active transaction blocks.
Application Retries:Implement automated retry logic in backend services when encountering transient deadlock errors.

10. Optimistic vs Pessimistic Locking

When designing multi-user applications, architects choose between two primary concurrency control patterns:

Strategy	Pessimistic Locking	Optimistic Locking
Core Philosophy	Assumes conflicts will happen frequently	Assumes conflicts happen rarely
Locking Timing	Locks records upfront during read	No locks placed during read
SQL Pattern	SELECT ... FOR UPDATE	WHERE id = ? AND version = ?
Concurrency	Lower throughput due to blocking waits	High throughput; zero read blocking
Best Applied To	High-contention inventory checkout, banking	Web applications, stateless REST APIs

-- Optimistic locking update pattern
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 101 AND version = 3;

-- If row count = 0, another client modified the record; backend retries.

Knowledge Check: SQL Transactions & ACID

1. What does Atomicity guarantee in an ACID transaction?

2. Which statement permanently saves pending transaction changes?

3. What does the ROLLBACK TO SAVEPOINT command do?

4. What is a Dirty Read in database concurrency?

5. Which isolation level completely eliminates phantom reads?

6. How does optimistic locking detect conflicting updates?