Learn how to start a consistent transaction for backups or replication setup. This command ensures a consistent view of the database without locking tables unnecessarily.
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.
START TRANSACTION and its alias BEGIN can be modified with one or more characteristics.
For example:
BEGIN WITH CONSISTENT SNAPSHOT;
START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;
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:
The behavior of WITH CONSISTENT SNAPSHOT is dependent on the transaction isolation level.
In this situation, there is a delay in the application logic after initiating a transaction to check an account balance.
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.
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.
innodb_snapshot_isolationThis system variable influences the behavior of locking reads (for instance, SELECT ... FOR UPDATE). When is enabled (ON), locking reads 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.
T5
SELECT balance FROM accounts WHERE id = 1;
T6
COMMIT;
T5
SELECT balance FROM accounts WHERE id = 1;
T6
COMMIT;
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
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").
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
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
Not permitted and has no effect.
COMMIT;
COMMIT;
START TRANSACTION [transaction_characteristic [, transaction_characteristic] ...]
transaction_characteristic:
WITH CONSISTENT SNAPSHOT
| READ WRITE | READ ONLY
| [NOT] CHAINCREATE TABLE accounts (
id INT PRIMARY KEY,
balance DECIMAL(10, 2)
) ENGINE=InnoDB;
INSERT INTO accounts VALUES (1, 1000.00);