MySQL Transaction Isolation Level

15.5.2.1 Transaction Isolation Levels

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

---

isolation level

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_isolation_level

One of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.

From highest amount of consistency and protection to the least, the isolation levels supported by InnoDB are: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.

With InnoDB tables, many users can keep the default isolation level (REPEATABLE READ) for all operations. Expert users might choose the READ COMMITTED level as they push the boundaries of scalability with OLTP processing, or during data warehousing operations where minor inconsistencies do not affect the aggregate results of large amounts of data. The levels on the edges (SERIALIZABLE and READ UNCOMMITTED) change the processing behavior to such an extent that they are rarely used.

See Also ACID, OLTP, READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE, transaction.

---

READ UNCOMMITTED

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_read_uncommitted

The isolation level that provides the least amount of protection between transactions. Queries employ a locking strategy that allows them to proceed in situations where they would normally wait for another transaction. However, this extra performance comes at the cost of less reliable results, including data that has been changed by other transactions and not committed yet (known as dirty read). Use this isolation level with great caution, and be aware that the results might not be consistent or reproducible, depending on what other transactions are doing at the same time. Typically, transactions with this isolation level only do queries, not insert, update, or delete operations.

See Also ACID, dirty read, isolation level, locking, transaction.

---

READ COMMITTED

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_read_committed

An isolation level that uses a locking strategy that relaxes some of the protection between transactions, in the interest of performance. Transactions cannot see uncommitted data from other transactions, but they can see data that is committed by another transaction after the current transaction started. Thus, a transaction never sees any bad data, but the data that it does see may depend to some extent on the timing of other transactions (witch may result problems known as read–write conflict, also known as unrepeatable reads).

When a transaction with this isolation level performs UPDATE … WHERE or DELETE … WHERE operations, other transactions might have to wait. The transaction can perform SELECT … FOR UPDATE, and LOCK IN SHARE MODE operations without making other transactions wait.

SELECT … FOR SHARE replaces SELECT … LOCK IN SHARE MODE in MySQL 8.0.1, but LOCK IN SHARE MODE remains available for backward compatibility.

See Also ACID, isolation level, locking, REPEATABLE READ, SERIALIZABLE, transaction.

---

REPEATABLE READ

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_repeatable_read

The default isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads. It uses a moderately strict locking strategy so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.

When a transaction with this isolation level performs UPDATE … WHERE, DELETE … WHERE, SELECT … FOR UPDATE, and LOCK IN SHARE MODE operations, other transactions might have to wait.

SELECT … FOR SHARE replaces SELECT … LOCK IN SHARE MODE in MySQL 8.0.1, but LOCK IN SHARE MODE remains available for backward compatibility.

See Also ACID, consistent read, isolation level, locking, phantom, transaction.

---

SERIALIZABLE

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_serializable

The isolation level that uses the most conservative locking strategy, to prevent any other transactions from inserting or changing data that was read by this transaction, until it is finished. This way, the same query can be run over and over within a transaction, and be certain to retrieve the same set of results each time. Any attempt to change data that was committed by another transaction since the start of the current transaction, cause the current transaction to wait.

This is the default isolation level specified by the SQL standard. In practice, this degree of strictness is rarely needed, so the default isolation level for InnoDB is the next most strict, REPEATABLE READ.

See Also ACID, consistent read, isolation level, locking, REPEATABLE READ, transaction.