Attention can view more fans exclusive blog~

An overview of the

MVCC refers to a technique that improves concurrency. In the earliest database systems, only reading and writing could be concurrent, and reading and writing were blocked. With the introduction of multiple versions, only writing blocks each other, and the other three operations can be done in parallel, greatly increasing InnoDB’s concurrency. In its internal implementation, unlike Postgres, which implements multiple versions on data rows, InnoDB is implemented in Undolog, where historical versions of data can be retrieved. The retrieved historical version of the data can be made available to the user to read (some read requests can only see older versions of the data, as defined by the isolation level), or the data on the data page can be overwritten when rolled back. Inside InnoDB, a global array of active read and write transactions is recorded, which is used to determine the visibility of transactions.

  1. Most of MySQL’s transactional storage engines do not implement simple row-level locking. In order to improve concurrency performance, they generally implement multi-version concurrency control (MVCC) simultaneously. Not only MySQL, but other database systems such as Oracle and PostgreSQL also implement MVCC, but their implementation mechanisms are different, because there is no unified implementation standard for MVCC.
  2. MVCC can be considered a variant of row-level locking, but it avoids locking in many cases and is therefore less expensive. Implementation mechanisms vary, but most implement non-blocking reads, and writes lock only the necessary rows.
  3. There are many approaches for implementing MVCC, typically including optimistic concurrency control and pessimistic concurrency control.
  4. MVCC only works at READ COMMITTED and REPEATABLE READ isolation levels. The other two isolation levels are incompatible with MVCC because READ UNCOMMITTED always reads the latest row, not the row that matches the current version of the transaction. SERIALIZABLE locks all rows that are read.

features

  1. MVCC is supported by InnoDB, the transactional storage engine in MySQL;
  2. For high concurrency transactions, MVCC is more efficient than simple locking.
  3. MVCC only works at READ COMMITTED and REPEATABLE READ isolation levels;
  4. The MVCC can be implemented using optimistic and pessimistic locks;
  5. MVCC implementations are not uniform across databases
  6. InnoDB’s MVCC is implemented by keeping two hidden columns after each row record

Transaction snapshot creation process:

  1. View all current uncommitted and active transactions, stored in an array
  2. Select the smallest XID of an uncommitted and active transaction and record it in the xmin of the snapshot
  3. Select the maximum XID of all committed transactions and record it in xmax after +1

When a snapshot is generated in a Read view, the RC and RR levels of visibility are different. RC cannot prevent non-repeatability, while RR can.

  1. In InnoDB (default RR level), a snapshot (read view) is created after the first SELECT statement read after begin/ Start transaction, and other active transactions in the system are recorded.
  2. InnoDB creates a snapshot (read View) for every SELECT statement in an RC-level transaction.

undo log

  1. Undo logs are generated when we make changes to records. Undo records are stored in the system tablespace by default. Since MySQL5.6, a separate Undo tablespace has been created.
  2. Undo records store old version data. When an earlier transaction wants to read data, it needs to search for data that meets its visibility along the Undo chain. When the Undo chain is very long, this process is time-consuming.
  3. INSERT/UPDATE/DELETE, where the INSERT operation is visible only to the current transaction before the transaction commit, and the generated undo log is directly deleted after the transaction commit. UPDATE/DELETE requires maintenance of multiple versions. Therefore, the undo logs generated by these two operations are grouped together as update_undo
  4. Insert undo log and update undo log:
    1. Insert undo log: The undo log generated by the transaction during the INSERT. It is only needed for rollback and can be deleted immediately after the transaction is committed.
    2. Update undo log: The undo log generated during the update/delete transaction, which is required for rollback/consistency reads and is purge by the Purge thread only if the snapshot used by the database is not involved.

InnoDB row data hides fields

  1. 6 bytes DB_TRX_ID (transaction ID) : specifies the transaction ID used to record the last INSERT/UPDATE operation on the row. (DELETE is an UPDATE operation, InnoDB does not actually DELETE data)
  2. 7 bytes DB_ROW_PTR (rollback pointer) : Points to the undo log record and stores the contents of the row before rebuilding the row record. I was wondering how the undo log record can record the previous content in the multi-threaded concurrent scenario, multiple copies? Later remember InnoDB updates are locked exclusively, only one thread can do it. Stupid -_ -!!!!!!
  3. 6-byte ROW_DB_ID: Contains a row ID that monotonically increases as new rows are inserted. This field is used when InnoDB automatically generates the cluster index, but this field is not generated if the table has a unique primary key, because the unique primary key can already be used to generate the cluster index.

SQL example

// Test InnoDB RR level test, modify the same data concurrently, modify firstrollback, modified latercommit// transaction ABEGIN;
START TRANSACTION;
select * from `user` where id = 1;
UPDATE `user` SET Field3 = 3 where id = 1;
select * from `user` where id = 1;
SELECT SLEEP(5); // Sleep for 5 seconds for transaction B to start executingROLLBACK; // Transaction A information [SQL]BEGIN; Affected row: 0 time: 0.001s [SQL]START TRANSACTION; Affected row: 0 time: 0.001s [SQL]select * from `user` where id = 1; Affected row: 0 time: 0.001s [SQL]UPDATE `user` SET Field3 = 3 where id = 1; Affected rows: 1 time: 0.001s [SQL]select * from `user` where id = 1; Affected row: 0 time: 0.001s [SQL]SELECT SLEEP(5); Affected row: 0 time: 5.000s [SQL]ROLLBACK; Affected row: 0 time: 0.002s // transaction BBEGIN;
START TRANSACTION;
select * from `user` where id = 1;
UPDATE `user` SET Field2 = 2 where id = 1;
select * from `user` where id = 1;
COMMIT; // Transaction B information [SQL]BEGIN; Affected row: 0 time: 0.000s [SQL]START TRANSACTION; Affected row: 0 time: 0.000s [SQL]select * from `user` where id = 1; Affected row: 0 Time: 0.000s // At this time transaction A executesUPDATEThe transaction is notcommit/rollbackSo exclusive lock blocking [SQL]
UPDATE `user` SET Field2 = 2 where id = 1; Affected rows: 0 time: 3.016s [SQL]select * from `user` where id = 1; Affected row: 0 time: 0.001s [SQL]COMMIT; Affected row: 0 Time: 0.002sCopy the code

In the above process, the undo log status is as shown in the figure when transaction A is executed:

Current read/Snapshot read

MySQL InnoDB’s default RR isolation level is implemented by “row exclusive lock + MVCC”, which not only ensures repeatable reads, but also prevents some phantom reads.

  1. During the execution of transaction A, transaction B inserts A piece of data and commits it, and transaction A queries it again. Although the old records in undo log are obtained through snapshot read (to prevent unreal read), update/ DELETE in transaction A can be successfully executed without any real prevention.

  2. Because operations in InnoDB are divided into current read/snapshot read.

  3. The current reading:

    1. select … lock in share mode;
    2. select … for update;
    3. insert
    4. update
    5. delete

  4. Snapshot read: ordinary select, excluding the current read select.

At the RR level, snapshot reads are implemented using MVCC (Concurrent multi-version Control) and Undo log. The current read is implemented through record lock and gap lock. Therefore, there is no real protection against phantom read in snapshot read scenarios. The current read scenario supports both repeatability and phantom read protection.

Snapshot read SQL example

// Test InnoDB RR level test, whether can prevent unreal read in the snapshot read scenario, whether transaction A can modify the new data of transaction B in the snapshot read scenario // The database currently has 6 data // Transaction A snapshot readBEGIN;
START TRANSACTION;
select * from `user`;
SELECT SLEEP(5);
select * from `user`;
UPDATE `user` set sex = 2;
select * from `user`;
COMMIT; // Transaction A information [SQL]BEGIN; Affected row: 0 time: 0.000s [SQL]START TRANSACTION; Affected row: 0 time: 0.001s [SQL]select * from `user`; Affected row: 0 time: 0.001s [SQL]SELECT SLEEP(5); Affected row: 0 time: 5.001s [SQL]select * from `user`; Affected row: 0 time: 0.001s [SQL]UPDATE `user` set sex = 2; Affected row: 7 // Here transaction B has committed after 5s sleep, but transaction AupdateAffects the number of rows when7Line that does not really prevent illusory reading time:0.000s

[SQL]
select * from `user`; Affected row: 0 time: 0.000s [SQL]COMMIT; Affected row: 0 time: 0.002s // transaction BBEGIN;
START TRANSACTION;
select * from `user`;
INSERT INTO `chat_room`.`user` (`id`.`user_name`.`password`.`sex`) VALUES ('1006'.'name'.'000'.'0');
select * from `user`;
COMMIT; // Transaction B information [SQL]BEGIN; Affected row: 0 time: 0.001s [SQL]START TRANSACTION; Affected row: 0 time: 0.000s [SQL]select * from `user`; Affected row: 0 time: 0.000s [SQL]INSERT INTO `chat_room`.`user` (`id`.`user_name`.`password`.`sex`) VALUES ('1006'.'name'.'000'.'0'); Transaction A executes selelct and then sleeps before executing itupdate, so transaction B executesinsertLock can be obtained directly when [SQL]
select * from `user`; Affected row: 0 time: 0.001s [SQL]COMMIT; Affected rows: 0 Time: 0.001sCopy the code

Currently read the SQL sample

// The current reading scenario is to addselect.lock in share mode; select for updateAn exclusive lock is created where transaction B executes before transaction A commits the transactioninsertThe operation requires waiting for transaction A to readBEGIN;
START TRANSACTION;
select * from `user` for UPDATE;
SELECT SLEEP(5);
select * from `user`;
UPDATE `user` set sex = 2;
select * from `user`;
COMMIT; // Transaction A information [SQL]BEGIN; Affected row: 0 time: 0.000s [SQL]START TRANSACTION; Affected row: 0 time: 0.001s [SQL]select * from `user` for UPDATE; Affected row: 0 time: 0.001s [SQL]SELECT SLEEP(5); Affected row: 0 time: 5.001s [SQL]select * from `user`; Affected row: 0 time: 0.000s [SQL]UPDATE `user` set sex = 2; // Only 6 rows will be affected in the current read scenario, support repeatable read and also prevent phantom read time: 0.000s [SQL]select * from `user`; Affected row: 0 time: 0.000s [SQL]COMMIT; Affected row: 0 time: 0.002s // transaction BBEGIN;
START TRANSACTION;
select * from `user`;
INSERT INTO `chat_room`.`user` (`id`.`user_name`.`password`.`sex`) VALUES ('1006'.'name'.'000'.'0');
select * from `user`;
COMMIT;

[SQL]BEGIN; Affected row: 0 time: 0.001s [SQL]START TRANSACTION; Affected row: 0 time: 0.000s [SQL]select * from `user`; Affected row: 0 time: 0.000s [SQL]INSERT INTO `chat_room`.`user` (`id`.`user_name`.`password`.`sex`) VALUES ('1006'.'name'.'000'.'0'); Transaction B because we have a table lock at the time of the current readinsertNeed to wait for lock release if willselect * from `user` for UPDATE; Modified toselect * from `user` where id = 1000 for UPDATE; So you can't prevent phantom reads, transaction B doesn't block, transaction A still doesupdate 7Article, which involves shared locks, exclusive locks, and gap locks behind can be dedicated to write a blog details [SQL]
select * from `user`; Affected row: 0 time: 0.001s [SQL]COMMIT; Affected row: 0 Time: 0.002sCopy the code

References:

  1. High Performance MySQ
  2. Mysql-innodb-mvcc Multiversion concurrency control