Transaction isolation level

Transaction isolation is one of the foundations of database processing. Isolation is the I in ACID; The isolation level is a setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions make changes and perform queries simultaneously.

InnoDB provides all four transaction isolation levels described by the SQL:1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level of InnoDB is REPEATABLE READ.

Users can use the SET TRANSACTION statement to change the TRANSACTION isolation level for a single session or for all subsequent connections. To set the server’s default isolation level for all connections, use the — Transaction Isolation option on the command line or in the configuration file options. More information about isolation levels and level setting syntax is covered in the set transaction statement below.

InnoDB supports each of the transaction isolation levels described here using a different locking strategy. For operations on critical data, a high degree of consistency with the default REPEATABLE READ level can be enforced in cases where ACID compliance is important. Alternatively, consistency rules can be relaxed using READ COMMITTED or even READ UNCOMMITTED, such as batch reporting, in which precise consistency and repeatable results are less important than minimizing locking overhead. SERIALIZABLE executes stricter rules than REPEATABLE READ and is mainly used in special cases such as XA transactions together and to resolve concurrency and deadlock issues.

The following list describes how MySQL supports different transaction levels. The list ranges from the most frequently used level to the least frequently used level.

    • REPEATABLE READ
      • This is InnoDB’s default isolation level. REPEATABLE READ (MVCC multiversion, REPEATABLE READ level, a snapshot is created when a transaction is started) This means that if multiple ordinary (non-locked, snapshot-read) SELECT statements are issued in the same transaction, the SELECT statements are also consistent with each other, that is, the same data is read.
      • FOR lock reads (current reads, SELECT with FOR UPDATE or FOR SHARE), UPDATE and delete statements, locking depends on whether the statement uses a unique index with a unique search condition or uses a scope-type search condition.
        • For unique indexes with unique search criteria, InnoDB locks only the index records found, not the gaps in front of them.
        • For other search criteria, InnoDB locks the index range of the scan and uses gap locks or next-key locks to prevent other sessions from being inserted into the gap in the range coverage, solving the phantom read problem. Check out my other post on gap locks or next-key locks at juejin.cn/post/684490…

    • READ COMMITTED 
      • Even within the same transaction, each snapshot read operation sets up and reads its own new snapshot.
      • FOR SELECT with FOR UPDATE or FOR SHARE, UPDATE and DELETE statements, InnoDB locks only index records and does not lock the gaps before them, thus allowing new records to be inserted freely next to locked records. Gap locking is only used for foreign key constraint checking and duplicate key checking.
      • Because gap locking is disabled, phantom reading problems can occur because other sessions can insert new rows into the gap. Subsequent interpretations of illusory readings will also be translated.
      • READ COMMITTED Isolation Level Only row-base binary logs are supported. Binlog logs in Row mode are supported in Statement, Row, and Mixed modes. If READ COMMITTED and BINlog_format =MIXED are used, the server will automatically use row-base logging.
      • Using READ COMMITTED has other effects:
        • For UPDATE or DELETE statements, InnoDB holds locks only on rows that it updates or deletes. After MySQL (SERVER layer) evaluates the WHERE condition, mismatched record locks will be released, violating MySQL’s two-phase locks (lock and unlock after transaction commit). This greatly reduces the likelihood of deadlocks, but deadlocks can still occur.
        • For UPDATE statements, if a row has been locked, InnoDB performs a “semi-consistent” read to return the latest committed version to MySQL (SERVER layer) so that MySQL can determine if the row matches the updated WHERE condition. If the row matches (it must be updated), MySQL reads the row again, and This time InnoDB either locks it or waits for it to be locked.
      • Consider the following example, starting with the following table:

        • CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1.2), (2.3), (3.2), (4.3), (5.2);
COMMIT;    
Copy the code

        • In this case, the table has no index, so searches and index scans use hidden clustered indexes (primary key indexes) for record locking instead of indexed columns
        • Suppose a session performs an update with the following statement:

          • # Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3; Copy the code
        • Also assume that the second session executes these statements after the first session to perform the update:

          • # Session B
UPDATE t SET b = 4 WHERE b = 2;Copy the code

        • When InnoDB performs each update, it first acquires an exclusive lock for each row and then decides whether to modify it. If InnoDB does not modify rows, it releases the lock. Otherwise, InnoDB keeps the lock until the transaction ends. This affects the following transactions.
        • When using the default REPEATABLE READ isolation level, updates in SessionA get an X-lock on every line it reads without releasing any of them:

          • x-lock(1.2); retain x-lock
x-lock(2.3); update(2.3) to (2.5); retain x-lock
x-lock(3.2); retain x-lock
x-lock(4.3); update(4.3) to (4.5); retain x-lock
x-lock(5.2); retain x-lockCopy the code

        • Updates in SessionB block immediately when attempting to acquire any locks (because updates in SessionA retain locks on all rows) and do not continue until updates in SessionA commit or roll back:

          • x-lock(1.2); block and wait for first UPDATE to commit or roll backCopy the code

        • If READ COMMITTED is used instead, the first update acquires an X lock for each row it reads and releases the X lock for its unmodified rows:

          • x-lock(1.2); unlock(1.2)
x-lock(2.3); update(2.3) to (2.5); retain x-lock
x-lock(3.2); unlock(3.2)
x-lock(4.3); update(4.3) to (4.5); retain x-lock
x-lock(5.2); unlock(5.2)
Copy the code
        • For the second update, InnoDB performs a “semi-consistent” read, and the engine returns the most recent committed version of each row read so that MySQL can determine if the row matches the updated WHERE condition:

          • x-lock(1.2); update(1.2) to (1.4); retain x-lock
x-lock(2.3); unlock(2.3)
x-lock(3.2); update(3.2) to (3.4); retain x-lock
x-lock(4.3); unlock(4.3)
x-lock(5.2); update(5.2) to (5.4); retain x-lockCopy the code

        • However, if the WHERE condition contains index columns and InnoDB uses indexes, only index columns are considered when acquiring and retaining record locks. In the example below, the first update acquires and retains an X lock on each row of b=2. The second update blocks while trying to acquire the X lock on the same record because it also uses the index defined on column B.

          • CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;Copy the code

        • The isolation level of READ COMMITTED can be set at startup or changed at run time. At run time, this can be set globally for all sessions or individually for each session.
    • READ UNCOMMITTED 
      • SELECT statements are executed unlocked, but earlier versions of rows may be used. Therefore, such reads are inconsistent with this isolation level. This is also called dirty reading. Otherwise, this isolation level works like READ COMMITTED.
    • SERIALIZABLE  

      • This level is similar to REPEATABLE READ, but InnoDB implicitly converts all pure SELECT statements to SELECT… FOR SHARE If autocommit(autocommit=0) is disabled, it is used FOR sharing. If automatic commit is enabled, the selection is its own transaction. Therefore, it is read-only and can be serialized if executed as a consistent (non-locked) read and does not need to block for other transactions. (To forcibly prevent pure selection when other transactions have modified the selected row, disable “AutoCOMMIT” (autoCOMMIT =0).)

Set transaction statement

  • grammar

    • SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

access_mode: {
     READ WRITE
   | READ ONLY
}Copy the code

    • This statement specifies the transaction characteristics. It accepts a comma-separated list of one or more eigenvalues. Each eigenvalue sets the transaction isolation level or access mode. Isolation levels are used for operations on InnoDB tables. Access mode specifies whether the transaction is run in read/write or read-only mode.
    • In addition, SET TRANSACTION can contain an optional global or session keyword to indicate the scope of the statement.
      • Transaction isolation level
        • To set the transaction ISOLATION LEVEL, use the ISOLATION LEVEL LEVEL clause. It is not allowed to specify more than one isolation level clause in the same SET TRANSACTION statement.
        • The default isolation level is REPEATABLE READ. Other allowed values include READ COMMITTED, READ UNCOMMITTED, and SERIALIZABLE. For information about these isolation levels, see the introduction above.
      • Transaction access pattern
        • To set the transaction access mode, use the READ WRITE or READ ONLY statement. It is not allowed to specify more than one access mode clause in the same SET TRANSACTION statement.
        • By default, transactions are conducted in read/write mode, allowing read and write operations to the tables used in the transaction. This mode can be explicitly specified using SET TRANSACTION with READ WRITE access mode.
        • If transaction access mode is set to read-only, changes to the table are prohibited. This may enable the storage engine to make performance improvements when writes are not allowed.
        • In read-only mode, you can still use DML statements to change tables created using the TEMPORARY keyword. As with permanent tables, changes are not allowed using DDL statements.
        • You can also specify READ WRITE and READ ONLY for a single transaction using the START Transaction statement.
      • Transaction eigenvalue range
        • You can set transaction characteristics globally for the current session or just for the next transaction:
          • Use the GLOBAL keyword:
            • This statement applies to all subsequent sessions.
            • Existing sessions are not affected.
          • Use the SESSION keyword:
            • This statement applies to all subsequent transactions executed in the current session.
            • This statement is allowed in a transaction, but does not affect the transaction currently in progress.
            • If executed between transactions, this statement overrides any previous statement that sets the next transaction value of the named characteristic.
          • There is no SESSION or GLOBAL keyword:
            • This statement applies only to the next transaction executed in the session.
            • Subsequent transactions revert to using the session’s eigenvalues.
            • This statement is not allowed in the following transactions:

              • mysql> START TRANSACTION; Query OK, 0 rows affected (0.02sec) mysql> SET TRANSACTION LEVEL SERIALIZABLE; ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progressCopy the code
          • Changing the global transaction feature requires the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege, deprecated in 8.0). Any session is free to change its session characteristics (even in the middle of a transaction) or the characteristics of the next transaction (before the transaction begins).
          • To set the global isolation level at server startup, use the — Transaction Isolation =level option on the command line or in the options file. The level value of this option uses dashes instead of Spaces, so the allowed values are read-uncommitted, read-committed, REPEATABLE-READ, or SERIALIZABLE.
          • Similarly, to set the global transaction access mode at server startup, use the — Transaction read-only option. The default value is OFF (read/write mode), but it can be set to ON for read only mode.
          • For example, to set the isolation level to repeatable reads and the access mode to read and write, use the following line in the [mysqld] section of the options file:

            • [mysqld]
transaction-isolation = REPEATABLE-READ
transaction-read-only = OFFCopy the code

               

          • As mentioned earlier, at run time, the set Transaction statement can be used to indirectly set global, session, and next transaction scope level features. They can also be set directly using the set statement to assign values to transaction isolation and transaction read-only system variables:
            • SET TRANSACTION allows optional GLOBAL and SESSION keywords to be SET at different scope levels.
            • The SET statement used to assign values to transaction_ISOLATION and transaction_read_only system variables has syntax for setting these variables at different scope levels.
            • The following table shows the level of feature scope set by each collection transaction and variable assignment syntax.
            • The following table sets the transaction syntax for transaction characteristics
              •   
            • The following setup syntax for the table transaction feature



            • Global and session values for the transaction feature can be checked at run time:

              • SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;
Copy the code