Mysql transaction isolation level, mysql transaction isolation

Mysql transaction isolation level, mysql transaction isolation
Transaction Isolation Levels

InnoDB supports four isolation levels in the SQL1992 standard: read uncommitted, read committed, repeatable read, and SERIALIZABLE. The default isolation level is repeatable read.

The set transaction command can be used to change the default isolation level. To make this change take effect for all connections of all users, you need to add the -- transaction-isolation option.

InnoDB implements different isolation levels through different locking policies

REPEATABLE READ

This is the default isolation level of InnoDB. In a transaction, consistent read reads the snapshot read for the first time in the transaction. This means that the results returned by executing a normal SELECT statement multiple times in the same transaction are consistent.

For lock read (SELECT... for update or SELECT... lock in share mode), update, and delete statements. The LOCK depends on whether the statement uses a unique index or range-Class search condition.

• If you use a unique index as the search condition, InnoDB only locks the detected index record without locking the gap above it.
• For other search conditions, InnoDB locks the scan index range and uses the gap lock or next-key lock to block other sessions from inserting data into the gap within the range.

 

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

 

Features of transactions

• Atomicity: the operations in the transaction are either all successful or all failed
• Consistency: transaction operations must take the database from one consistent state to another consistent state. The so-called consistency means that, for example, the transfer, A and B both have 100 yuan, A total of 200 yuan before the transfer, and 200 yuan after the transfer
• Isolation: transactions do not affect each other
• Persistence: changes made to the database after the transaction is committed will be permanently saved.

Problems caused by transaction concurrency

• Dirty read: one transaction reads uncommitted data from another transaction.
• Phantom read: A transaction reads the committed data of another transaction. However, the committed data involves the entire table, such as insertion and deletion. It looks like an illusion.
• Unrepeatable read: The results returned by reading the same data multiple times in a transaction are different.

Transaction isolation level

• Read not committed: allows one transaction to see data not committed by another transaction
• Read committed: allows one transaction to view the committed data of another transaction.
• Repeatable read: The data read from the same transaction multiple times is always consistent.
• Serialization: Concurrent transaction serial execution