What is a MVCC

Concurrency Control, for multi-version Concurrency Control, is designed to improve the Concurrency performance of a database. The following articles focus on the InnoDB engine because myIsam does not support transactions.

When read/write requests occur on the same row, the lock is blocked. But MVCC has a better way of handling read-write requests without locking them in the event of read-write conflicts.

This read refers to snapshot read, not current read. Current read is a pessimistic lock operation.

So how does it do read-write without locking, snapshot read and current read is what the heck, follow your dear brother, continue to read.

What are current reads and snapshot reads

What are current and snapshot reads under MySQL InnoDB?

The current reading

It reads database records that are of the latest version and locks the currently read data to prevent other transactions from modifying the data. Is an operation of pessimistic locking.

The following operations are current reads:

  • Select lock in share mode

  • Select for update (exclusive lock)

  • Update (exclusive lock)

  • Insert (exclusive lock)

  • Delete (exclusive lock)

  • Serialize the transaction isolation level

Read the snapshot

Snapshot read is implemented based on multi-version concurrency control, that is, MVCC. If multiple versions are used, the data to be read may not be the latest data, but may be the data of previous versions.

The following operations are snapshot reads:

  • Select operations without locking (note: transaction level is not serialization)

Relationship between snapshot reads and MVCC

MVCCC is an abstract concept for “maintaining multiple versions of a data without conflicting read and write operations.”

This concept requires specific functionality to be implemented, and this specific implementation is snapshot reading. (Specific implementation is described below)

Listen to the sweet elder brother’s explanation, is not the instant toilet burst open.

Database concurrency scenario

  • Read-read: There are no problems and no concurrency control is required

  • Read-write: Has thread safety issues, may cause transaction isolation issues, may encounter dirty reads, unreal reads, unrepeatable reads

  • Write – write: There are thread safety issues and there may be update loss issues, such as type 1 update loss, type 2 update loss

What concurrency problems does MVCC solve?

The lock-free concurrency control used by MVCC to resolve read-write conflicts is to assign unidirectional time-stamps to transactions. Save a version for each data modification, and the version is associated with the transaction timestamp.

The read operation only reads the database snapshot before the transaction starts.

The solution is as follows:

  • Concurrent read-write: The read operation does not block the write operation, and the write operation does not block the read operation.

  • Solve the transaction isolation problems such as dirty read, phantom read, and unrepeatable read, but cannot solve the above write-write update loss problem.

Hence the following to improve concurrency performancecombination:

  • MVCC + Pessimistic lock: MVCC resolves read/write conflicts, while pessimistic lock resolves write conflicts

  • MVCC + Optimistic lock: MVCC resolves read/write conflicts, while optimistic lock resolves write conflicts

Realization principle of MVCC

Its realization principle is mainly version chain, undo log, Read View to achieve

Here, by the way, I send you a classic learning materials, I used in university and work of the classic e-book library (including data structure, operating system, C++/C, network classics, front-end programming classics, Java related, programmer cognition, career development), interview and job summary are packed here.

Click here to get directly:

Computer Classics required reading list (including download methods)

Java to master the interview with the most complete information package (including download methods)

Version of the chain

For every row of data in our database, in addition to what we see with our eyes, there are several hidden fields that we have to see with our eyes. The values are db_trx_id, db_roll_pointer, and db_row_id.

  • db_trx_id

    6byte, last modified (modified/inserted) transaction ID: Records the ID of the transaction in which the record was created or last modified.

  • Db_roll_pointer (version chain key)

    7byte, a rollback pointer to the previous version of this record (stored in the rollback segment)

  • db_row_id

    6byte, implied increment ID (hidden primary key). If the table does not have a primary key, InnoDB automatically generates a cluster index with db_row_ID.

  • There is actually a deletion flag hidden field. When a record is updated or deleted, it does not mean that it has been deleted, but that the deletion flag has changed

As shown above, db_Row_id is the only implicit primary key that the database generates by default for this row of records, db_trx_id is the transaction ID that operates on this record, and DB_roll_pointer is a rollback pointer that is used with the undo log to point to the previous version.

An undo log is logged every time a change is made to the database record, and each undo log has a roll_pointer attribute (the undo log for INSERT does not have this attribute because the record does not have an older version). You can concatenate these undo logs into a linked list. So now it looks like this:

After each update to the record, the old value will be put into an undo log. As the number of updates increases, all versions of the record will be connected into a linked list by the roll_pointer attribute. This list is called the version chain, and the first node of the version chain is the latest value of the current record. In addition, each version also contains the transaction ID for which the version was generated, which is important information to use when judging version visibility from ReadView.

The undo log

The Undo log is used to record logs before data modification. Data is copied to the Undo log before table information modification.

When a transaction is rolled back, data can be restored using the log in undo log.

Usage of Undo log

  • Ensure atomicity and consistency during transaction rollback. When the transaction is rolled back, undo log data can be used to restore the transaction.

  • It is used for MVCC snapshot data reading. In MVCC multi-version control, by reading historical version data of undo log, different transaction versions can have their own independent snapshot data versions.

Undo logs are classified into two types:

  • insert undo log

    The undo log, which represents the transaction generated when a new record is inserted, is only needed when a transaction is rolled back and can be discarded immediately after a transaction is committed

  • Update undo log (main)

    The undo log generated when the transaction is update or DELETE; Not only when a transaction is rolled back, but also when a snapshot is read;

    The purge thread will purge the log only if the log is not involved in a quick read or transaction rollback

Read View

A Read View produced when a transaction performs a snapshot Read. A snapshot of the current database system is generated at the moment the snapshot Read is performed.

Records and maintains a list of other transaction ids in the system that are currently active and should not be seen by the current transaction (there is no COMMIT; each transaction starts with an ID that is incrementally increased, so the newer the transaction, the greater the ID value).

The main purpose of the Read View is to make a visibility judgment, that is, when a transaction performs a snapshot Read, create a Read View of the record. This is compared to a condition to determine which version of the data can be seen by the current transaction. It could also be a version of the undo log recorded in the row.

Read View several properties

  • Trx_ids: set of active (uncommitted) transaction version numbers for the current system.

  • Low_limit_id: Maximum transaction version number +1 when the current read view is created.

  • Up_limit_id: “system was in active transaction minimum version” when the current Read view was created

  • Creator_trx_id: transaction version number for creating the current Read View;

Read View Visibility criteria

  • Db_trx_id < up_limit_id | | db_trx_id = = creator_trx_id (display)

    If the data transaction ID is less than the minimum active transaction ID in the Read View, you can be sure that the data existed before the current transaction started and therefore can be displayed.

    Creator_trx_id = creator_trx_id = creator_trx_id = creator_trx_id = creator_trx_id

  • Db_trx_id >= low_limit_id (not displayed)

    If the data transaction ID is greater than the maximum transaction ID of the current system in the Read View, the data was created after the current Read View was created, so the data is not displayed. If less than, it goes to the next judgment

  • Db_trx_id Specifies whether the value is in active transactions (trx_IDS)

    • None: the transaction is committed when the Read View is created, in which case the data can be displayed.

    • Existing: represents the time when my Read View was generated, and your transaction is still active and has not yet been committed. The data you modified is also invisible to my current transaction.

MVCC and transaction isolation levels

Read View is used to support the implementation of RC (Read Committed) and RR (Repeatable Read) isolation levels.

When RR and RC are generated

  • At the RC isolation level, each snapshot Read generates and retrieves the latest Read View.

  • In RR isolation, a Read View is created only for the first snapshot Read in the same transaction. Subsequent snapshot reads obtain the same Read View, and subsequent queries are not generated repeatedly. Therefore, the query result of a transaction is the same every time.

Solve illusory problems

  • Snapshot read: controlled by MVCC without locking. Add, delete, change, check and other operations according to the “grammar” specified in MVCC to avoid unreal reading.

  • Current read: Next key lock (row lock +gap lock) to solve the problem.

InnoDB snapshot read differences in RC and RR levels

  • Under the RR level of a transaction on a record of the first snapshot Read will create a snapshot and Read the View, the current system of active other transaction record, then the call snapshot to Read, or use the same Read View, so as long as the current transaction before other transaction commit updated snapshot used to Read, Subsequent snapshot reads use the same Read View, so subsequent changes are invisible;

  • That is, when a RR snapshot Read generates a Read View, the Read View records snapshots of all other active transactions at that time. The changes made to these transactions are not visible to the current transaction. Changes made by transactions created before the Read View are visible

  • In RC level transactions, each snapshot Read creates a new snapshot and Read View, which is why we see updates committed by other transactions in RC level transactions

conclusion

I myself liver six copies of PDF < about Java entry to god >, the whole network spread more than 10W +, search “code farmers attack” after paying attention to the public number, in the background reply PDF, get all PDF

Six PDF links

As can be seen from the above description, MVCC refers to the process of accessing the version chain of records when ordinary SEELCT operations are performed by transactions with READ COMMITTD and REPEATABLE READ isolation levels. In this way, read-write and read-read operations of different transactions can be executed concurrently. This improves system performance.

Welcome to pay attention to my public number “code farmers attack”. Share Python, Java, big data, machine learning, artificial intelligence and other technologies, pay attention to code farming technology improvement, career breakthrough, thinking transition, 100,000 + code farming growth charge first stop, accompany you have a dream to grow together.