START TRANSACTION ... WITH CONSISTENT SNAPSHOT

Explore enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT. This section details how these improvements aid in achieving consistent backups and replication in highly active environments.

The START TRANSACTION WITH CONSISTENT SNAPSHOT statement begins a new transaction and, for the InnoDB storage engine, immediately establishes a consistent read view of the database.

This differs from a standard START TRANSACTION or BEGIN statement, which creates its read view lazily only when the first read operation is performed. Using WITH CONSISTENT SNAPSHOT is essential for transactions where the snapshot's timing must be precisely aligned with the start of the transaction itself, not a later read query.

Syntax

START TRANSACTION and its alias BEGIN can be modified with one or more characteristics.

START TRANSACTION [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic:
    WITH CONSISTENT SNAPSHOT
  | READ WRITE | READ ONLY
  | [NOT] CHAIN

For example:

BEGIN WITH CONSISTENT SNAPSHOT; START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;

The InnoDB Read View

MariaDB's InnoDB storage engine uses a mechanism called MVCC (Multi-Version Concurrency Control) to handle concurrent data access. A core component of MVCC is the read view.

A read view can be thought of as an instantaneous snapshot of the database. When a transaction uses a read view, it sees only the data that was committed at the moment the "snapshot" was taken. It ignores any changes made by transactions that had not yet committed, as well as any changes from transactions that started after the read view was created.

The key difference addressed by this command is the timing of this snapshot:

Transaction Type	Creation of Read View	Time of Read View Creation
START TRANSACTION	Created lazily	At the first read operation (e.g., a SELECT)
START TRANSACTION WITH CONSISTENT SNAPSHOT	Created immediately	At the moment the statement is executed, before other actions

Behavior with Transaction Isolation Levels

The behavior of WITH CONSISTENT SNAPSHOT is dependent on the transaction isolation level.

Isolation Level	Default Read View Behavior	Effect of WITH CONSISTENT SNAPSHOT
REPEATABLE READ	A single, stable read view is created and used for the entire transaction.	Guarantees the read view is established immediately at the start of the transaction.
SERIALIZABLE	Same as REPEATABLE READ	Provides a predictable snapshot for all subsequent reads. This is its most common use case.
READ COMMITTED	A new read view is created for each individual SELECT statement.	Affects the first read statement only, ensuring its snapshot is taken at the transaction's start time.
READ UNCOMMITTED	Does not use read views. Reads include uncommitted data ("dirty reads").	Not permitted and has no effect.

With Default Read View Behavior

Checking an Account Balance with a Transaction Delay

In this situation, there is a delay in the application logic after initiating a transaction to check an account balance.

Setup

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
) ENGINE=InnoDB;

INSERT INTO accounts VALUES (1, 1000.00);

Scenario: Two sessions are running concurrently.

Timeline	Session 1 (Application checking balance)	Session 2 (An external deposit)
T1	START TRANSACTION;
T2	-- Application logic causes a 2-second delay DO SLEEP(2);	START TRANSACTION;
T3		UPDATE accounts SET balance = 1500.00 WHERE id = 1;
T4		COMMIT;
T5	SELECT balance FROM accounts WHERE id = 1;
T6	COMMIT;

In the scenario above, the read view for Session 1 is created at T5. Since Session 2's COMMIT happened at T4, the SELECT in Session 1 will see the new balance: $1500.00. This might not be the desired behavior if the goal was to see the balance as it was at T1.

With CONSISTENT SNAPSHOT

Timeline	Session 1 (Application checking balance)	Session 2 (An external deposit)
T1	START TRANSACTION WITH CONSISTENT SNAPSHOT;
T2	-- Application logic causes a 2-second delay DO SLEEP(2);	START TRANSACTION;
T3		UPDATE accounts SET balance = 1500.00 WHERE id = 1;
T4		COMMIT;
T5	SELECT balance FROM accounts WHERE id = 1;
T6	COMMIT;

In this second scenario, the read view for Session 1 is created immediately at T1. Even though Session 2 commits a change at T4, the SELECT at T5 uses the original snapshot. It will see the old balance: $1000.00, reflecting the state of the database when the transaction began.

Related System Variables

innodb_snapshot_isolation

This system variable influences the behavior of locking reads (e.g., SELECT ... FOR UPDATE). When innodb_snapshot_isolation is enabled (ON), locking reads will reference the transaction's read view. If a transaction tries to lock a row modified by another transaction not visible in the current read view, MariaDB returns an ER_CHECKREAD error instead of waiting for a lock. This enforces stricter snapshot consistency, even for locking operations.