Transaction Fundamentals

A transaction represents a logical unit of work comprising multiple SQL statements that must either execute entirely or not at all. Storage engines handle transaction implementation; InnoDB is the most prominent MySQL engine offering full transaction support.

Commit and Rollback Operations

A commit finalizes the changes made by all SQL statements within a transaction. A rollback reverses all modifications made during the transaction if a failure occurs, restoring the database to its pre-transaction state.

Manual and Automatic Transaction Management

Transactions can be explicitly defined using initialization and finalization commands:

BEGIN;
  -- Data manipulation queries
COMMIT;

By default, MySQL operates with autocommit enabled. Every individual SQL statement is treated as a standalone transaction and automatically committed. Disabling autocommit requires explicit COMMIT commands to persist changes:

SET autocommit = 0; -- Requires explicit COMMIT
SET autocommit = 1; -- Auto-commits each statement

ACID Properties Deep Dive

Atomicity Implementation: Undo Log

When a transaction alters data, InnoDB generates corresponding undo logs. If the transaction fails or a rollback is triggered, the system uses these undo logs to revert the data to its original state.

Durability Implementation: Redo Log

InnoDB uses a Buffer Pool to cache data pages. Modified pages in memory (dirty pages) are periodically flushed to disk. To prevent data loss during a crash before flushing, InnoDB employs Write-Ahead Logging (WAL). Changes are recorded in the redo log prior to updating the Buffer Pool.

Redo log writing is significantly faster than disk flushing because:

• It uses sequential I/O (appending) rather than random I/O.
• It logs only the specific bytes modified, whereas page flushing requires writing entire 16KB data pages.

Redo Log vs. Binary Log

InnoDB uses a two-phase commit protocol to maintain consistency between redo logs and binary logs, ensuring both logs synchronize correctly upon commit.

Transaction Isolation and Concurrency Issues

Concurrent transactions can introduce several read anomalies:

Isolation levels mitigate these anomalies:

InnoDB defaults to Repeatable Read (RR) and specifically prevents phantom reads in this level, unlike the SQL standard expectation.

Snapshot Read vs. Current Read

Current Read reads the latest committed data and acquires locks on the scanned rows. It is invoked by:

SELECT balance FROM accounts WHERE account_id = 99 FOR UPDATE;
SELECT balance FROM accounts WHERE account_id = 99 LOCK IN SHARE MODE;
-- UPDATE, DELETE, INSERT also use current read

Current reads utilize Next-Key Locks (a combination of record locks and gap locks) to prevent other transactions from modifying the read data.

Snapshot Read executes standard SELECT queries without locking, relying on MVCC to retrieve a consistent historical version of the data.

Multi-Version Concurrency Control (MVCC)

MVCC enables non-blocking reads by maintaining multiple versions of a row. It operates in Read Committed and Repeatable Read isolation levels. InnoDB implements MVCC using:

• Hidden Columns: Each row contains a transaction ID and a rollback pointer.
• Undo Log Version Chain: Rollback pointers link current data to previous versions stored in the undo log.
• ReadView: A snapshot of active transactions at a specific moment. When a snapshot read occurs, the system traverses the version chain, comparing transaction IDs against the ReadView to determine which version is visible to the current transaction.

Database Locking Strategies

Table-Level Locking

MyISAM relies strictly on table locks, separating them into shared read locks and exclusive write locks. Read locks allow concurrent reads but block writes, while write locks block all other reads and writes. InnoDB also supports table locks but primarily uses row locks; however, if a query fails to use an index, InnoDB will escalate to a table lock.

Row-Level Locking and InnoDB

InnoDB implements row locks by locking index entries. Without proper indexing, queries default to table locks. InnoDB offers Shared (S) and Exclusive (X) row locks.

Gap Locks and Next-Key Locks

To prevent phantom reads during current reads, InnoDB uses locks that target ranges of records:

• Gap Lock: Locks the space between two index records (e.g., locking the gap between IDs 100 and 200).
• Next-Key Lock: A combination of a record lock and a gap lock, forming a closed interval (e.g., locking the range from 100 to 200 inclusive).

Example of range locking:

SELECT * FROM orders WHERE order_id > 500 AND order_id < 600 FOR UPDATE;
-- Applies a Next-Key Lock on the interval, preventing inserts into this range.

Under the Repeatable Read isolation level, InnoDB prevents phantom reads by combining MVCC for snapshot reads and Next-Key Locks for current reads.

Clustered Indexes in InnoDB

Data rows are physically stored in the leaf nodes of a clustered index. InnoDB designates one index as the clustered index based on the following priority:

1. The primary key, if defined.
2. The first UNIQUE index with no NULL values, if no primary key exists.
3. A hidden row ID generated internally by InnoDB.