Syntax\n------\n\nSET [GLOBAL | SESSION] TRANSACTION\n    transaction_property [, transaction_property] ...\n\ntransaction_property:\n    ISOLATION LEVEL level\n  | READ WRITE\n  | READ ONLY\n\nlevel:\n     REPEATABLE READ\n   | READ COMMITTED\n   | READ UNCOMMITTED\n   | SERIALIZABLE\n\nDescription\n-----------\n\nThis statement sets the transaction isolation level or the transaction access mode globally, for the current session, or for the next transaction:\n\n With the GLOBAL keyword, the statement sets the default transaction level globally for all subsequent sessions. Existing sessions are unaffected.\n With the SESSION keyword, the statement sets the default transaction level for all subsequent transactions performed within the current session.\n Without any SESSION or GLOBAL keyword, the statement sets the isolation level for only the next (not started) transaction performed within the current session. After that it reverts to using the session value.\n\nA change to the global default isolation level requires the SUPER privilege. Any session is free to change its session isolation level (even in the middle of a transaction), or the isolation level for its next transaction.\n\nIsolation Level\n\nTo set the global default isolation level at server startup, use the --transaction-isolation=level option on the command line or in an option file. Values of level for this option use dashes rather than spaces, so the allowable values are READ_UNCOMMITTED,READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For example, to set the default isolation level to REPEATABLE READ, use these lines in the [mariadb] section of an option file:\n\n``ini\n[mariadb]\ntransaction-isolation = REPEATABLE-READ\n`\n\nTo determine the global and session transaction isolation levels at runtime, check the value of the transaction_isolation variable.\n\nTo determine the global and session transaction isolation levels at runtime, check the value of the tx_isolation system variable.\n\n`sql\nSELECT @@GLOBAL.transaction_isolation, @@tx_isolation;\n`\n\nInnoDB supports each of the translation isolation levels described here using different locking strategies. The default level isREPEATABLE READ. For additional information about InnoDB record-level locks and how it uses them to execute various types of statements, see InnoDB Lock Modes, and innodb-locks-set.html.\n\nIsolation Levels\n\nThe following sections describe how MariaDB supports the different transaction levels.\n\nREAD UNCOMMITTED\n\nSELECT statements are performed in a non-locking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a "dirty read". Otherwise, this isolation level works likeREAD COMMITTED.\n\nREAD COMMITTED\n\nA somewhat Oracle-like isolation level with respect to consistent (non-locking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See innodb-consistent-read.html.\n\nFor locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), InnoDB locks only index records, not the gaps before them, and thus allows the free insertion of new records next to locked records. For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100). For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For range-type searches, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is necessary because "phantom rows" must be blocked for MariaDB replication and recovery to work.\n\nIf the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except for foreign-key constraint checking and\\\nduplicate-key checking. Also, record locks for non-matching rows are released after MariaDB has evaluated the WHERE condition. If you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging.\n\nRows that don't match are not being locked in a so called semiconsistent read. This means you might see only a partially consistent read when the transaction isolation level is READ COMMITTED or READ UNCOMMITTED.\n\n(A semiconsistent read applies to UPDATE and DELETE statements. Those statements skip locked rows, provided the version in the current read does not match the WHERE condition. Also, if the latest version of a record was successfully locked, but found not to match the condition, the lock is released.)\n\nREPEATABLE READ\n\nThis is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the\\\nsnapshot established by the first read. This convention means that if you issue several plain (non-locking) SELECT statements within the same transaction, these SELECT statements are consistent\\\nalso with respect to each other. See innodb-consistent-read.html.\n\nFor locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. MariaDB does not relax the gap locking for unique indexes.\n\nFor locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.\n\nFor other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.\n\nThis is the minimum isolation level for non-distributed XA transactions.\n\nSnapshot Isolation and DML Operations\n\nThis behavior is available from MariaDB 11.6.2 and 12.3.\n\nThe innodb_snapshot_isolation system variable is enabled by default. This introduces a stricter enforcement of REPEATABLE READ for UPDATE and DELETE statements:<br>\n\n Conflict Detection: If an UPDATE or DELETE attempts to modify a row that has been changed by a concurrent transaction since your snapshot was established, the operation is rejected.\n ER_CHECKREAD (1020): This rejection triggers error ER_CHECKREAD. The revised error message suggests that the user should try restarting the transaction.\n Automatic Rollback: Unlike a simple statement error, ER_CHECKREAD is treated similarly to a deadlock: the entire transaction is rolled back.\n* Purpose: This prevents the transaction from switching to "current-read" mode for that row, which would otherwise allow the transaction to observe concurrent changes it did not make, violating the pure repeatable read invariant.\n\nTraditional Locking Behavior\n\nIf innodb_snapshot_isolation is disabled (set to OFF), InnoDB follows traditional behavior where locking reads (SELECT ... FOR UPDATE), UPDATE, and DELETE statements read the latest committed version of rows. In this mode, subsequent non-locking SELECT statements for those same rows also return the current version rather than the snapshot version, which can lead to non-repeatable read anomalies.\n\nSERIALIZABLE\n\nThis level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (non-locking) read and need not block for other transactions. (This means that to force a plain SELECT to block if other transactions have modified the selected rows, you should disable autocommit.)\n\nDistributed XA transactions should always use this isolation level.\n\ninnodb_snapshop_isolation\n\nIf the innodb_snapshot_isolation system variable is not set to ON, strictly-speaking anything other than READ UNCOMMITTED is not clearly defined. innodb_snapshot_isolation defaults to OFF for backwards compatibility. Setting to ON will result in attempts to acquire a lock on a record that does not exist in the current read view raising an error, and the transaction being rolled back.\n\nIf the innodb_snapshot_isolation system variable is not set to ON, strictly-speaking anything other than READ UNCOMMITTED is not clearly defined.\n\nAccess Mode\n\nThe access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the tx_read_only system variable). READ ONLY mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that, unlike the global read_only mode, the READ_ONLY ADMIN privilege doesn't allow writes, and DDL statements on temporary tables are not allowed either.\n\nThe access mode specifies whether the transaction is allowed to write data or not. By default, transactions are in READ WRITE mode (see the tx_read_only system variable). READ ONLY mode allows the storage engine to apply optimizations that cannot be used for transactions which write data. Note that, unlike the global read_only mode, the SUPER privilege doesn't allow writes, and DDL statements on temporary tables are not allowed either.\n\nIt is not permitted to specify both READ WRITE and READ ONLY in the same statement.\n\nREAD WRITE and READ ONLY` can also be specified in the START TRANSACTION statement, in which case the specified mode is only valid for one transaction.\n\nExamples\n--------\n\nSET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/transactions/set-transaction