Mariadb Set Transaction

SET TRANSACTION in MariaDB

Last updated: 2026-07-20

This skill covers the delta between standard SQL SET TRANSACTION and MariaDB's. It assumes the agent already knows the standard isolation-level concepts (dirty read, non-repeatable read, phantom read). Everything below documents MariaDB's scope rules, its default isolation level and access mode, the variable-name history, and innodb_snapshot_isolation — a behavior with no standard-SQL analogue that changes how UPDATE/DELETE conflicts are detected under REPEATABLE READ.

Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.

What LLMs Often Miss

Syntax

SET [GLOBAL | SESSION] TRANSACTION
    transaction_property [, transaction_property] ...

-- transaction_property:
--   ISOLATION LEVEL {REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE}
--   READ WRITE
--   READ ONLY

READ WRITE and READ ONLY cannot both appear in the same statement. READ WRITE/READ ONLY can also be given directly on START TRANSACTION, where they apply to that one transaction only.

Scope Rules — the Part LLMs Get Wrong Most

A bare SET TRANSACTION ... can be issued at any point between transactions but not once a transaction is already in progress — attempting that raises ERROR 1568 (25001). SET SESSION TRANSACTION ... and SET GLOBAL TRANSACTION ..., by contrast, may be changed even mid-transaction (they simply won't affect the transaction already running).

START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ERROR 1568 (25001): Transaction characteristics can't be changed
-- while a transaction is in progress

Isolation Levels and the Default

The default is REPEATABLE READ — set it at startup with --transaction-isolation=REPEATABLE-READ (dashes, not spaces, for the option/config-file form) or check it live via SELECT @@transaction_isolation;.

• READ UNCOMMITTED — non-locking reads may see an uncommitted ("dirty") earlier or concurrent version of a row.
• READ COMMITTED — each consistent read (even within the same transaction) takes a fresh snapshot; locking reads (FOR UPDATE/LOCK IN SHARE MODE) skip gap locks except for foreign-key/unique-key checks, so requires row-based binary logging.
• REPEATABLE READ (default) — all consistent reads in a transaction reuse the snapshot from the transaction's first read. Minimum isolation level required for non-distributed XA transactions.
• SERIALIZABLE — like REPEATABLE READ, but plain SELECT is implicitly converted to SELECT ... LOCK IN SHARE MODE when autocommit is disabled. Required for distributed XA transactions.

The session variable is transaction_isolation (introduced 11.1.1, enum, default REPEATABLE-READ). Its predecessor tx_isolation is deprecated (since 11.1) but still functions identically — new code should use transaction_isolation. Setting either mid-session with a bare SET @@tx_isolation = ... follows the same "next transaction only" scope rule as SET TRANSACTION.

innodb_snapshot_isolation — Write-Write Conflict Detection

This is the single most consequential MariaDB-specific behavior layered on top of REPEATABLE READ, and it has no equivalent in standard SQL semantics or in most other engines' REPEATABLE READ.

• Default: ON (confirmed in storage/innobase/handler/ha_innodb.cc, MYSQL_THDVAR_BOOL(snapshot_isolation, ..., /* default */ TRUE)). Scope: Global and Session; dynamic.
• What it does: under REPEATABLE READ, if an UPDATE or DELETE targets a row that a concurrent transaction has changed since your snapshot was taken, the operation is rejected rather than silently switching to a current-read of that row.
• Error and recovery: raises ER_CHECKREAD (1020). This is handled like a deadlock, not an ordinary statement error — the entire transaction is rolled back, not just the offending statement. Application retry logic should catch 1020 alongside 1213 (deadlock) and re-run the whole transaction.
• Why it exists: without it, a locking read/write on a stale-snapshot row would fall back to reading the latest committed version for just that row — a genuine non-repeatable-read anomaly (and a lost-update hazard) inside a nominally REPEATABLE READ transaction.
• Disabling it (SET GLOBAL innodb_snapshot_isolation = OFF) restores pre-11.6.2 "traditional" InnoDB locking, where locking reads/UPDATE/DELETE transparently read the latest committed row version — weaker consistency, kept only for backward compatibility.

-- Typical retry pattern that must also catch snapshot-isolation conflicts:
-- on error 1213 (deadlock) OR 1020 (ER_CHECKREAD) -> ROLLBACK and retry

Access Mode — READ WRITE / READ ONLY

Default is READ WRITE (transaction_read_only defaults to OFF; confirmed sql/sys_vars.cc, DEFAULT(0)). READ ONLY lets the storage engine apply optimizations unavailable to writing transactions, and also blocks DDL on temporary tables. It follows the same GLOBAL/SESSION/next-transaction scope rules as isolation level.

See Also

• mariadb-transactions — START TRANSACTION/COMMIT/ROLLBACK/SAVEPOINT, autocommit, and implicit commit
• mariadb-lock-tables — explicit table locks and named user-level locks
• Canonical references on mariadb.com/docs (consult only for edge cases not covered here):
  • https://mariadb.com/docs/server/reference/sql-statements/transactions/set-transaction
  • https://mariadb.com/docs/server/reference/sql-statements/transactions/transactions-repeatable-read
  • https://mariadb.com/docs/server/reference/sql-statements/transactions/transactions-read-committed
  • https://mariadb.com/docs/server/reference/sql-statements/transactions/transactions-serializable
  • https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables

This page is: Copyright © 2026 MariaDB. All rights reserved.