MariaDB - Transaction Isolation

Revenge · Mar 20, 2016

I've noticed that Centminmod uses the default transaction isolation, Repeatable Read.

Why not use Read Commited, that have more performance in busy sites and is completely safe?

Isolation Levels
The following sections describe how MariaDB supports the different transaction levels.

READ UNCOMMITTED
SELECT 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 like READ COMMITTED.

READ COMMITTED
A 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 MySQL :: MySQL 5.7 Reference Manual :: 14.3.4 Consistent Nonlocking Reads.

For 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 DELETEstatements, 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 MySQL replication and recovery to work.

Note: Since MariaDB 5.1, if 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 duplicate-key checking. Also, record locks for non-matching rows are released after MariaDB has evaluated the WHERE condition. As of MariaDB/MySQL 5.1, if you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging.

REPEATABLE READ
This 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 snapshot 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 also with respect to each other. SeeMySQL :: MySQL 5.7 Reference Manual :: 14.3.4 Consistent Nonlocking Reads.

For 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. For 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.

This is the only transaction level that can be used with the row based binary logging.

This is the minimum isolation level for non-distributed XA transactions.

SERIALIZABLE
This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements toSELECT ... 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.)

Distributed XA transactions should always use this isolation level.
Click to expand...

SET TRANSACTION - MariaDB Knowledge Base

The founder of Percona, Peter Zaitsev also recommends using Read Commited as default.
MySQL performance implications of InnoDB isolation modes

eva2000 · Mar 20, 2016

Ultimately, it comes down to your specific web app's mysql usage requirements. As such I wouldn't have any idea what the end user uses, so defaults are most optimal

Also if end user uses mysql replication etc, they may also have a unique set of requirements regarding the best isolation method for the mysql replication configuration.

Log in / Register

Forums

Join the community today

MariaDB - Transaction Isolation

Revenge Active Member

eva2000 Administrator Staff Member

.

Log in / Register

Useful Searches

Join the community today

MariaDB - Transaction Isolation

Revenge Active Member

eva2000 Administrator Staff Member

.