[MySQL] Transactions implemented by MVCC multi-version concurrency control

Multi-Version Concurrency Control Multi-version concurrency control, MVCC is a concurrency control method. It is generally used in database management systems to achieve concurrent access to the database; it implements transaction memory in programming languages.

1. There is no unified implementation standard, non-blocking read operations are realized, and write operations only lock necessary rows
2. Implemented by saving a snapshot of the data at a certain point in time
3. Typical ones include optimistic concurrency control and pessimistic concurrency control
4. Innodb's mvcc has an incrementing version number for each transaction. By adding two columns of hidden fields after each row of records, the two columns are respectively Create version number and delete version number, store the version number of the transaction that operates it
5. Add, delete, modify and query in the transaction is to operate the two-column version number field

insert saves the current value for each newly inserted row Transaction version number to the row creation version number field
update Insert a new row to save the current transaction creation version number, modify the deletion version number of the original row data to the version number of this transaction
delete Modify the row deletion version number field For the version number of this transaction
select Query to create data whose version number field is less than or equal to the current transaction version Make sure the record exists before this transaction or is newly inserted by this transaction
Query to delete the version number field that does not exist Or data larger than the current version ensures that the record has not been deleted before this transaction

6. Such a design does not require locking, and has good reading and operation performance, but requires additional storage space
7 .mvcc only works under the two isolations of REPEATABLE READ and READ COMMITED; READ UNCOMMITED always reads the latest data; SERIALIZABLE locks all rows read

[Recommended course: mysql video tutorial】

The above is the detailed content of [MySQL] Transactions implemented by MVCC multi-version concurrency control. For more information, please follow other related articles on the PHP Chinese website!