Shulou(Shulou.com)06/02 Report--

This article mainly introduces "the introduction of MySQL multi-version controller MVCC". In daily operation, I believe many people have doubts about the introduction of MySQL multi-version controller MVCC. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MySQL multi-version controller MVCC introduction". Next, please follow the editor to study!

Most transactional storage engines in MySQl do not implement simple row-level locks. Based on high performance considerations, they generally think of multiple versions of concurrent controllers (MVCC) at the same time. Not only MySQL, but also other database systems such as Oracle and PostgreSQL also implement MVCC, but their implementation mechanisms are different, because MVCC does not have a unified implementation standard. MVCC can be said to be a variant of row-level locks, but it avoids locking operations in most cases, so it is less expensive. Although the implementation mechanisms are different, most of them implement non-blocking read operations, and write operations lock only the necessary rows.

MVCC exists only under the isolation level of Read Commit and Read Repeatable of MySQL.

Transaction log

Transaction logs can help improve the efficiency of transactions. Using the transaction log, the storage engine only needs to modify the memory copy of the table when it modifies the data, and then records the modification in the transaction log on the hard disk, instead of persisting the modified data to disk every time. The transaction log is appended, so the operation of writing the log is the sequential I _ hand O in a small area of the disk, unlike the random I _ big O, which needs to move the head in multiple places on the secondary disk, so using the transaction log method is relatively faster. After the transaction log is persistent, the modified data in memory can be slowly brushed back to disk in the background. At present, most storage engines are implemented in this way, which we usually call pre-written logs (Write-Ahead Logging). To modify data, you need to write to disk twice.

Redo log is the log of the InnoDB storage engine layer, which is mainly used to record the log information of the transaction. When a transaction is started, a log sequence number is recorded, when the transaction is executed, the transaction log is inserted into the log buffer (redo buffer), and the log information in the redo buffer is recorded to disk before the transaction is committed. If the database is powered off, the InnoDB storage engine will use redo log to restore to the time before the power outage to ensure the integrity of the data.

Undo log is the log of the InnoDB storage engine layer, which is mainly used to record the log before the data is modified. Before the table information is modified, the data is copied to the undo log. When the transaction is rolled back, the data can be restored through the log in undo log.

Undo log is used for MVCC snapshot data. In MVCC multi-version control, by reading the historical version data of undo log, different transaction version numbers can have their own independent snapshot data version. Sometimes when it comes to row versioning, it is also achieved through undo log: when a read row is locked by another transaction, it can analyze the previous data of the row record from the undo log, thus providing the row version information and allowing the user to achieve non-locking consistent reading.

InnoDB is a multi-version storage engine: it stores information about older versions that have changed rows to support transactional features such as concurrency and rollback. This information is stored in a data structure called a rollback segment in the tablespace (after a similar data structure in Oracle). InnoDB uses the information in the rollback segment to perform the undo operations required in the transaction rollback. It also uses this information to build an earlier version of the row for consistent reading.

Internally, InnoDB adds three fields for each row stored in the database. The 6-byte DB_TRX_ID field indicates the transaction identifier of the last transaction that inserted or updated the row. In addition, deletion is considered an update internally, where the special bit in the row is set to mark it as deleted. Each line also contains a 7-byte field called a scroll pointer by DB_ROLL_PTR. The roll pointer points to the undo log record written to the rollback segment. If the row is updated, the undo log record contains the information needed to rebuild the contents of the row before updating the row. A 6-byte DB_ROW_ID field contains a row ID, and when a new row is inserted, the ID of the row increases monotonously. If InnoDB automatically generates a clustered index, the index contains row ID values. Otherwise, the DB_ROW_ID column will not appear in any index.

The undo log in the undo segment is divided into insert and update undo log. The undo log needs to be inserted only in a transaction rollback and can be discarded immediately after the transaction is committed. Updating undo log is also used for consistent reads, but they can only be discarded if no transactional InnoDB has assigned snapshots, and in consistent reads the information in undo log may need to be updated to build earlier version rows of the database.

In the InnoDB multi-version control scheme, when you delete a row using an SQL statement, the row is not physically deleted from the database immediately. InnoDB physically deletes the corresponding rows and their index records only when the updated undo log records written for deletion are discarded. This delete operation is called purge and is very fast, usually in the same time order as the deleted SQL statement.

DB_TRX_ID: records the transaction ID that operates the data transaction

DB_ROLL_PTR: pointer to the location of the previous version of data in undo log

DB_ROW_ID: hide ID, which will be used to create a clustered index when the created table does not have a suitable index as a clustered index

Read view

A read view is actually a list list that holds the transaction ID. What is recorded is what other transactions are being executed in MySQL when this transaction is executed.

Read Repeatable corresponds to the creation of a read view when each transaction starts.

Read Commit corresponds to the creation of a read view each time the SQL statement is executed.

Read View structure

Struct read_view_t {/ / because it is in reverse order, low/up reverses / / can see the high water level logo of the current line version, > low_limit_id can not see the trx_id_t low_limit_id; / / can see the low water level logo of the current line version, and < up_limit_id can see trx_id_t up_limit_id / / the number of current active transactions (that is, uncommitted transactions) ulint up_limit_id trxtransactions; / / an array of currently fetched active transactions id in reverse order / / its up_limit_id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.