Reasons and apologies for deleting the article yesterday

Last night about 6 o ‘clock sent this article, brother found that there was a problem with the typesetting of the article. Because it was written and copied to the public account, I copied one more copy, brother did not find it in time and also published it. I am deeply sorry here.

Because you came to study in a burst of enthusiasm, but only saw “the content has been deleted by the author of the release” a few big words, brother once again to apologize, I will be more rigorous, responsible for you, reduce these stupid mistakes.

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 -: There are no problems and no concurrency control is required
  • Read write: has thread safety issues, may cause transaction isolation issues, may encounter dirty read, unreal read, unrepeatable read
  • Write -: There is a thread safety problem, and there may be update loss, 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.
  • To solveDirty read,Phantom read,Unrepeatable readTransaction isolation, but does not solve the aboveWrite - Write updates lostThe problem.

Hence the following to improve concurrency performancecombination:

  • MVCC + Pessimistic lock: MVCC resolves read/write conflicts, pessimistic locking resolves write conflicts
  • MVCC + Optimistic lock: MVCC resolves read/write conflicts, optimistic locking resolves write conflicts

Realization principle of MVCC

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

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

  • ensureThe transactionforrollbackAt the time of theAtomicity and consistency, when the transaction is going onThe rollbackYou can use the undo log datarestore.
  • Used for MVCCRead the snapshotData, in MVCC multi-version control, by readingundo logtheHistorical version DataCan be implementedDifferent transaction version numbersAll have their ownIndependent snapshot data version.

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: The system is active (Have not been submitted) transaction version number collection.
  • low_limit_id: Current system when the current read View is createdMaximum transaction version number+ 1 “.
  • up_limit_idThe system was in an active transaction when the current Read View was createdMinimum version number
  • creator_trx_id: creates the transaction version number of 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)

    • There is no: indicates a transaction when the read view is createdHave a commitIn this case, the data doesAccording to.
    • existingThe transaction is still active and has not yet been committed. The data you modify is invisible to the 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

  • RCAt the isolation level, eachRead the snapshotwillBuild and get the latesttheRead View;
  • And in theRRAt the isolation level, it isIn the same transactiontheFirst snapshot readWill createRead View.After theAll snapshot reads areSame Read View, and then the queryIt won't repeatSo one transaction query results at a timeIt's all the same.

Solve illusory problems

  • Read the snapshot: control through MVCC, without lock. Add, delete, change, check and other operations according to the “grammar” specified in MVCC to avoid unreal reading.
  • The current reading: 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

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.

Follow wechat public account: IT elder brother

Java actual combat project video tutorial: you can get 200G, 27 sets of actual combat project video tutorial

Reply: Java learning route, you can get the latest and most complete a learning roadmap

Re: Java ebooks, get 13 must-read books for top programmers

Java foundation, Java Web, JavaEE all tutorials, including Spring Boot, etc

Reply: Resume template, you can get 100 beautiful resumes