# START TRANSACTION ... WITH CONSISTENT SNAPSHOT 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. ```sql 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 SNAPSH`OT; ## **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 ```sql 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 (for instance, `SELECT ... FOR UPDATE`). When [innodb\_snapshot\_isolation](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables#innodb_snapshot_isolation) 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.