🍖 Transaction isolation mechanism

Time:2021-10-21

1、 Introduction to transaction isolation mechanism

Transaction hasAtomicity, consistency, isolation, persistenceFour characteristics

andIsolationAs the name suggests, transactions are isolated from each other. When multiple transactions process a data at the same time, they do not affect each other. If the isolation is not good enough, there may be problemsDirty reading, unrepeatable reading, phantom reading, etcphenomenon

2、 Four levels of isolation

1. Level (isolation degree) from low to high

  • Read uncommitted(uncommitted read)
  • Read committed(submit for reading)
  • Repeatable read(repeatable) (MySQL default)
  • Serializable(serializable)

2. Solve the reading phenomenon in turn

  • ✔ : May appear

  • ❌ : Will not appear

  Dirty reading Non repeatable reading Unreal reading
Read uncommitted
Read committed ×
Repeatable read (MySQL default) × ×
Serializable × × ×

It should be emphasized that: we can indeed improve the isolation level of transactions to solve the problems of dirty reading, non repeatable reading and phantom reading, but at the same time, the higher the isolation level of transactions, the lower the concurrency ability; Therefore, readers need to make trade-offs according to business needs

3、 Introduction to four levels

1. Read uncommitted

  • definition

Is the lowest isolation level. Under this transaction isolation level, one transaction can read uncommitted data from another transaction

  • Database locking (implementation principle)

The transaction does not lock the data when reading the data

When a transaction modifies data, it only adds row level shared locks to the data

  • Phenomenon explanation

When transaction 1 reads a row of records, transaction 2 can also read and update this row of records, and because transaction 1 does not add any locks to the data

When transaction 2 also updates the record, transaction 1 reads the record again and can read the modified version of the record by transaction 2 (because transaction 2 only adds a shared read lock, transaction 1 can add a shared read lock to read data). Even if the modification has not been committed, if transaction 2 rolls back at this time, the data read by transaction 1 will be dirty, which leads to dirty reading

When transaction 1 updates a row of records, transaction 2 cannot update the row until the end of transaction 1 (because transaction 1 adds a shared read lock to the data, transaction 2 cannot add an exclusive write lock to modify the data)

2. Read committed

  • definition

It can be understood that Chengdu has committed. In the process of modifying data in a transaction, if the transaction has not been committed, other transactions cannot read the data

  • Database locking

The transaction adds a row level shared lock to the currently read data (the lock is only added when the data is read). Once the row is read, the row level shared lock of the row is released immediately

At the moment when a transaction updates some data (at the moment of updating), it must first add a row level exclusive lock to it until the end of the transaction

  • Phenomenon explanation

During the whole process of reading a row record by transaction 1, transaction 2 can read the row record (because when transaction 1 adds a row level shared lock to the row record, transaction 2 can also add a shared lock to the data to read the data)

At the moment when transaction 1 reads a row, transaction 2 cannot modify the row data. However, as long as transaction 1 reads the changed row data, transaction 2 can modify the row data (transaction 1 will add a shared lock to the data at the moment of reading, and no other transaction can add an exclusive lock to the row of data; however, once the transaction reads the row of data, it will release the row level shared lock. Once the lock is released, transaction 2 can add an exclusive lock to the data and modify the data.)

When transaction 1 updates a row record, transaction 2 cannot update the row record until transaction 1 ends (when transaction 1 updates the data, it will add an exclusive lock to the row data, and the lock will not be released until the end of the transaction. Therefore, transaction 1 can read the data without adding a shared lock to the data before transaction 2 is committed; therefore, submitting reading can solve the phenomenon of dirty reading)

3. Repeatable reads

  • definition

Because the committed read isolation level will cause the phenomenon of non repeatable read, the isolation level higher than the committed read can solve the problem of non repeatable read. This isolation level is called repeatable read

  • Database lock

At the moment when a transaction reads some data (that is, the moment when it starts reading), it must first add a row level shared lock to it until the end of the transaction

When a transaction updates some data (that is, when the update occurs), it must first add a row level exclusive lock to it until the end of the transaction

  • Phenomenon explanation

During the whole process of reading a row record by transaction 1, transaction 2 can read the row record (because when transaction 1 adds a row level shared lock to the row record, transaction 2 can also add a shared lock to the data to read the data)

During the whole process of reading a row record by transaction 1, transaction 2 cannot modify the row data (transaction 1 will add a shared lock to the data during the whole process of reading, and the lock will not be released until the transaction is committed, so no other transaction can add an exclusive lock to the row data during the whole process; therefore, repeatable reading can solve the reading phenomenon of non repeatable reading)

When transaction 1 updates a row record, transaction 2 cannot update the row record until transaction 1 ends (when transaction 1 updates the data, it will add an exclusive lock to the row data, and the lock will not be released until the end of the transaction. Therefore, transaction 1 can read the data without adding a shared lock to the data before transaction 2 is committed; therefore, submitting reading can solve the phenomenon of dirty reading)

4. Serializable

  • definition

It is the highest isolation level. Unreal reads that cannot be solved by the first three isolation levels can be solved in the serializable isolation level

  • Database lock

When a transaction reads data, it must first add a table level shared lock to it until the end of the transaction

When a transaction updates data, it must first add a table level exclusive lock to it until the end of the transaction

  • Phenomenon explanation

When transaction 1 is reading the records in table a, transaction 2 can also read table a, but cannot update, add or delete table a until transaction 1 ends (because a pair of tables in transaction add table level shared locks, other transactions can only add shared locks to read data, and cannot do any other operations)

When transaction 1 is updating the records in table a, transaction 2 cannot read any records in table a, let alone update, add or delete table a until transaction 1 ends (table level exclusive locks are added to one pair of tables in transaction, and shared locks or exclusive locks cannot be added to other transactions, so no operation can be performed)

  • Effect of serializing transactions
  • Unable to read records submitted by companies that have been modified by other transactions
  • Before the current transaction is completed, other transactions cannot modify the records read by the current transaction
  • Before the completion of the current transaction, the index key value of the new record inserted by other transactions cannot be within the index key range read by any statement of the current transaction

5. Selection of isolation level

The four transaction isolation levels are getting higher and higher in isolation degree, but lower and lower in concurrency at the same time; The reason why there are several isolation levels is to facilitate developers to select the most appropriate isolation level according to business needs in the development process

4、 Modify transaction isolation level

1. View the current transaction isolation level

  • Variable names can be fuzzy matched
show variables like "%tx_isolation";

image-20210302190403075

  • View variable TX directly_ isolation
select @@tx_isolation;

image-20210302190600192

  • You can also view it through the following statement
select @@global.tx_ isolation;   #  View global transaction isolation levels
select @@session.tx_ isolation;  #  View the current session transaction isolation level

image-20210302190857307

ps: in MySQL 8.0.3,tx_isolationVariable istransaction_isolationVariable replaced; To query the transaction isolation level in MySQL version 8.0.3, just put thetx_isolationReplace variable withtransaction_isolationVariable is enough

2. Modify transaction isolation level

MySQL providesset transactionStatement to change the transaction isolation level of a single session or a global session

set [session|global] transaction isolation level
    [read ununcommitted|read committed|repeatable read|serializable]
  • Session: indicates that the modified transaction isolation level will be applied to all transactions in the current session (current CMD window)
  • Global: indicates that the modified transaction isolation level will be applied to all transactions in all sessions (global), and the existing sessions will not be affected
  • If session and global are omitted, the modified transaction isolation level will be applied to the next transaction that has not started in the current session

3. User permissions

Any user can change the transaction isolation level of the session, but only the user with super permission can change the global transaction isolation level

  • Verify that the global transaction isolation level is modified, and the current session is not affected
select @@global.tx_ isolation;   #  View global transaction isolation levels
select @@session.tx_ isolation;  #  View the current session transaction isolation level
set global transaction isolation level read committde; 
#Modify global transaction isolation level

image-20210302195744397

  • You can modify TX directly_ The isolation variable is used to modify the transaction isolation level of the current session
set tx_isolation="read-committed";
select @@session.tx_isolation;

image-20210302204911467

—end—