preface

  • The transactional nature of a database
  • Consistency problems encountered in concurrent data reads and writes
  • Isolation level of mysql transactions
  • Realization principle of MVCC
  • Lock and isolation levels

Pay attention to the public account, communicate together, wechat search: sneak forward

1 Transaction features of the database

  • Atomicity: Operations within a transaction are indivisible, and the SQL in the transaction is either executed together or not, atomicity
  • Isolation: The database system provides some isolation mechanism to ensure that transactions are executed in a “standalone” environment that is not affected by external concurrent operations. This means that the intermediate state during a transaction is not visible to the outside world
  • Consistency: Data constraints must be in a consistent state at the start and completion of a transaction
  • Persistence: After a transaction completes, its changes to the data are permanent and can persist even in the event of a system crash

2 Consistency problems during concurrent data read and write

  • Dirty read (for uncommitted)
    • Transaction A modifies data D and transaction A is uncommitted, while transaction B can read uncommitted data D, which is called dirty read
  • Dirty write
    • When two transactions attempt to update A data record at the same time, when transaction A updates, transaction B overwrites the update data submitted by transaction A. This is A dirty write. Want to add lock to solve commonly
  • Unrepeatable reads (for committed Updates)
    • The value changed by the committed transaction is read by other transactions, and the value changed by other committed transactions is read by multiple queries within the transaction, which results in unrepeatable reads
  • Phantom read (for committed INSERTS)
    • The transaction reads inserted data after the transaction has started, for exampleselect * from table_user where id between 1 and 10, this SQL should have found the data 1~9, id=10 now does not exist, then other transactions insert another record id=10. The current transaction is then queried again to find 10 records. This is illusory reading
    • The difference between unrepeatable and unrepeatable reads is that the problem of unrepeatable reads is to read the latest changes, while phantom reads are to read the latest inserted data

3 Isolation level of mysql transactions

  • READ uncommitted (READ uncommitted (RU) : Corresponds to dirty reads, and you can READ the latest uncommitted changes
  • READ COMMITTED (RC) : A transaction can READ changes COMMITTED by another transaction. It avoids dirty reading, but it still has the problem of unrepeatable reading and phantom reading
  • REPEATABLE READ (RR) : Multiple reads of the same data in the same transaction return the same result. It avoids dirty read and unrepeatable read problems, but phantom read still exists
  • SERIALIZABLE Read: Transactions are executed serially. All of these problems are avoided

4 Implementation principle of MVCC

Concurrency Control MVCC is a multi-version Concurrency Control that allows Concurrency without locking

Undo log version chain of MVCC

  • Each row in InnoDB has a hidden column, which contains the transaction ID trx_ID of the row and the roll_pointer pointer to undo log

  • Undo log-based version chain: The hidden column of each row contains the undo log pointer roll_pointer, and each undo log also points to an earlier undo log, thus forming a version chain

readView

REPEATABLE READ (REPEATABLE READ) : REPEATABLE READ (REPEATABLE READ) : REPEATABLE READ (REPEATABLE READ) : REPEATABLE READ (REPEATABLE READ) : REPEATABLE READ (REPEATABLE READ) : REPEATABLE READ (REPEATABLE READ) You need to determine which version in the MVCC version chain is visible to the current transaction. ReadView innoDB’s solution readView contains four more important properties

  • m_ids: in the generatedReadViewIs a list of active read and write transaction ids in the current system
  • min_trx_id: indicates that the system is being generatedReadViewIs the smallest transaction ID among active read/write transactions in the current system, i.em_idsMinimum value of
  • max_trx_id: generatesReadViewIs the id value in the system that should be assigned to the next transaction
  • creator_trx_id: Generates this parameterReadViewThe transaction id
ReadView access steps
  • If the version is accessedtrx_idAttribute values andReadViewIn thecreator_trx_idIf the value is the same, the current transaction is accessing its own modified version of the record, which can be accessed by the current transaction.
  • If the version is accessedtrx_idAttribute value less thanReadViewIn themin_trx_idValue indicating that the transaction that generated this version was generated in the current transactionReadViewPreviously committed, so the version can be accessed by the current transaction.
  • If the version is accessedtrx_idAttribute values inReadViewthemin_trx_idandmax_trx_idBetween, that needs to judgetrx_idIs the property value inm_idsIn the list, if yes, the system is createdReadViewThe transaction that generated the version is still active, and the version cannot be accessed. If no, the system is createdReadViewThe transaction that generated the version has been committed and the version can be accessed
  • If the version is accessedtrx_idAttribute value is greater than or equal toReadViewIn themax_trx_idValue indicating that the transaction that generated this version was generated in the current transactionReadViewThis version cannot be accessed by the current transaction. Whereas visible
  • If one version of the data is not visible to the current transaction, then follow the version chain to find the next version of the data (undo log). If the last version is not visible, it means that the record is not visible to the transaction at all

Read committed and repeatable reads are implemented using ReadView

  • Snapshot read: Reads the snapshot version, that is, the historical version of the readView data. The common SELECT is snapshot read
  • UPDATE, DELETE, INSERT, SELECT… LOCK IN SHARE MODE, SELECT… FOR UPDATE is the current read and requires a lock
  • READ UNCOMMITTED: Just read the latest version of the record
  • READ COMMITTED: Generates a ReadView each time data is read
    • For current reads, the RC isolation level guarantees that records read are locked (record locks), with phantom reads
  • REPEATABLE READ: Generates a ReadView when reading data for the first time
    • For the current read, the RR isolation level ensures that the read record is locked (record lock) and the read range is locked. New records that meet the query conditions cannot be inserted (gap lock) and no phantom read exists
    • RR does not solve illusions strictly. If the transaction starts with an update to an invisible data (with no current read operation in front of it) and then queries, this record will be found more, which is also a phantom read

5 Lock and isolation levels

  • RC, RR, SERIALIZABLE level of isolation, the current read will require locks
  • MVCC can avoid illusions in most cases, but not completely
  • The RR isolation level must be selected. For UPDATE Locks the current read operation to prevent phantom reads
  • forSERIALIZABLEFor isolation level transactions,InnoDBSpecifies the use of locking to access records

Corrections are welcome

Refer to the article

  • Mysql lock procedure
  • Mysql > alter database lock
  • Learn in depth how MySQL transactions: ACID feature works
  • MySQL transaction isolation level implementation principle