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

This article shares with you about how to analyze mysql transactions and the underlying principle of isolation level. Xiaobian thinks it is quite practical, so share it with you to learn. I hope you can gain something after reading this article. Let's not say much. Let's take a look at it together with Xiaobian.

preface

A transaction is the smallest unit of work of a database operation, a series of operations performed as a single logical unit of work; these operations are submitted to the system as a whole, either all or none of them are performed; a transaction is a set of operations that cannot be divided any more (logical unit of work).

Characteristics of transactions:

Atomicity: Atomicity means that all operations involved in a transaction either succeed or fail. Consistency: The result of a transaction execution must be to change the database from one consistent state to another. Isolation: The execution of one transaction cannot be interfered with by other transactions. That is, the internal operations and data used in a transaction are isolated from other concurrent transactions, and the transactions executed concurrently cannot interfere with each other. Durability: Once a transaction is committed, its changes to the data in the database should be permanent. Subsequent actions or failures should not have any effect on the results of their execution.

I. Analysis of atomicity in the underlying principles of transactions:

Principle: undo log

The undo log, also known as the rollback log, is the basis for atomicity and isolation of transactions. When a transaction modifies the database, InnoDB generates an undo log; if the transaction fails or rollback is called, the transaction needs to be rolled back, and the information in the undo log can be used to roll back the data to the state before the modification.

The undo log is a logical log that records information about sql execution. When a rollback occurs, InnoDB does the opposite of what it did before, based on the contents of the undo log: for each insert, the rollback performs a delete; for each delete, the rollback performs an insert; and for each update, the rollback performs a reverse update, changing the data back.

The undo is stored in a special segment inside the database called the undo segment. The undo segment is located in a shared table space. undo is a logical log, so it simply restores the database logically to its original state. undo log generates redo log, which means redo log generation is accompanied by redo log generation, because undo log also needs persistent protection.

The undo log is executed before each write or modification of data.

How undo log works: Each row of data in the database table will have two more columns DATA_TRX_ID and DATA_ROLL_PTR (there may be a column DB_ROW_ID, which will be added automatically when there is no default primary key). DATA_TRX_ID indicates the transaction version of the current data, DATA_ROLL_PTR points to the just copied to

The old version record in the undo log chain. undo log is a linked list. If multiple transactions are modified many times, undo log will continue to be generated and the pointing relationship will be established through DATA_ROLL_PTR. Illustrate with pictures:

In this way, once the transaction is rolled back, mysql can restore the data with undo log, thus ensuring the atomicity of the uncommitted transaction.

persistent

Principle: redo log

As InnoDB is MySQL's storage engine, data is stored on disk. In order to reduce disk IO and improve read performance, InnoDB provides a buffer Pool. Buffer Pool contains the mapping of some data pages in the disk, which is used as a buffer for accessing the database: when reading data from the database, it will be read from Buffer Pool first. If there is no data in Buffer Pool, it will be read from disk and put into Buffer Pool; when writing data to the database, it will be written into Buffer Pool first, and the modified data in Buffer Pool will be refreshed to disk regularly (this process is called flushing).

However, this also brings a new problem. If MySQL goes down, and the modified data in Buffer Pool has not been refreshed to disk at this time, it will lead to data loss and transaction persistence cannot be guaranteed.

To solve this problem, redo logs are introduced, also known as redo logs. When the data is modified, in addition to modifying the data in the Buffer Pool, this operation will be recorded in the redo log; when the transaction is submitted, the fsync interface will be called to flush the redo log. If MySQL goes down, you can read the data in the redo log when restarting and restore the database. redo log uses WAL (Write-ahead logging), all changes are written to the log before committing, ensuring that data will not be lost due to MySQL downtime, thus meeting the persistence requirements. redo log is for physical pages, executed concurrently, and the last commit overwrites uncommitted data. Local redo log:

redo log is also buffered--redo log buffer, which flushes all modified information to disk after the transaction is committed. The user can also modify the refresh policy (default 1) by controlling the value of the variable innodb_flush_log_at_trx_commit, such as setting the value to 2 and controlling it to refresh every second, so that transactions are advanced.

It will be faster, but there is a risk of log loss.

redo log is logged after the SQL statement is executed.

Since redo log also needs storage, it also involves disk IO. Why use it?

(1) The redo log is stored sequentially, while cache synchronization is a random operation.

(2) Cache synchronization is in units of data pages, and the size of data transferred each time is greater than redo log.

redo log is a persistence feature used to recover data to secure committed transactions.

Isolation:

Principle:

(1). The impact of write operations on write operations: locking mechanisms ensure isolation

(2). The impact of write operations on read operations: MVCC guarantees isolation

Consistency:

Consistency is special. Atomicity, persistence and isolation are all for consistency. In addition, consistency also depends on the guarantees provided by the database itself, such as SQL syntax verification, column type insertion data type verification, and also depends on the application layer guarantees, such as transfer operation, which requires developers to deduct the balance of the transferor and increase the balance of the recipient. If there is a problem at the application level, consistency cannot be guaranteed.

II. Analysis of the bottom principle of isolation level

In the analysis of the underlying principles of transactions, the principle of isolation is not too deep, so we will briefly introduce it here.

First of all, let's introduce MVCC(MultiVersion Concurrency Control) of MySQL, which is called Multiversion Concurrency Control. It relies on undo log and read view implementations. undo log We've already talked about it above, so I won't repeat it. Read view is different from database view, which is used to judge the visibility of the current version of data.

Readview has four main attributes:

(1). m_ids represents the ID of all currently active transactions when ReadView is generated. Active means that the transaction has been opened and has not been submitted;

(2). min_trx_id denotes the smallest transaction ID among the currently active mIds;

(3). max_trx_id indicates the largest transaction ID when generating ReadView, which is not necessarily the largest transaction ID in mIds;

(4).creator_trx_id indicates the transaction ID that created the ReadView.

Note: Each time a transaction is opened, the transaction ID is incremented. The transaction ID can be regarded as a global auto-incrementing variable. The first transaction opened doesn't necessarily commit before the next transaction opened, such as a long connection, so don't assume max_trx_id is the maximum value in mIds.

Read view How do you determine which version of data a transaction should read based on the above four attributes?

If the data_trx_id of the accessed version is less than the minimum value in m_ids, the transaction that generated the version was committed before ReadView was generated, and the version can be accessed by the current transaction.

If the data_trx_id attribute value of the accessed version is the same as the creator_trx_id value in ReadView, it means that the current transaction is accessing its own modified record, so the version can be accessed by the current transaction.

If the data_trx_id attribute value of the accessed version is greater than the max_trx_id value in ReadView, it indicates that the transaction that generated this version was opened after the current transaction generated ReadView, so this version cannot be accessed by the current transaction.

If the data_trx_id attribute value of the accessed version is between min_trx_id and max_trx_id of ReadView, it is necessary to determine whether the trx_id attribute value is in the m_ids list. If yes, it means that the transaction generated when ReadView is created is still active, and the version cannot be accessed; if not, it means that the transaction generated when ReadView is created has been committed, and the version can be accessed.

When a transaction wants to read a row of data, first use the above rules to determine the latest version of the data, that is, the row of records. If it is found that it can be accessed, it will be read directly. If it is found that it cannot be accessed, it will find undo log through DATA_ROLL_PTR pointer. Recursively go down to find each version until it reads the version that it can read. If it cannot read, it will return null.

Therefore, when accessing data, a view will be created in the database, and the logical result of the view will prevail when accessing:

READ UNCOMMITTED: Returns the most recent value on the record directly at this isolation level, with no view concept. Because reads don't add any locks, writes modify data during reads, resulting in dirty reads. The advantage is that concurrent processing performance can be improved, and reading and writing can be done in parallel.

READ COMMITTED: At this isolation level, this view is created at the beginning of each SQL statement execution. InnoDB uses exclusive locking in READ COMMITTED, reading data without locking but using MVCC mechanism. Or in other words, he uses a separate mechanism for reading and writing.

REPEATABLE READ: At this isolation level, this view is created at transaction initiation and is used throughout the lifetime of the transaction.

SERIALIZABLE: This isolation level uses locking directly to avoid parallel access.

At this point, you may have found that MySQL uses MVCC to eliminate phantom reads at the repeatable read isolation level.

III. SUMMARY

Atomicity: Use undo log (rollback log) to achieve rollback, thus ensuring the atomicity of uncommitted transactions;

Persistence: Use redo logs to restore data, thus ensuring the persistence of committed transactions;

Isolation: use locks and MVCC ideas to achieve read and write separation, read and write parallel;

Consistency: Consistency through rollback, recovery, and isolation in a concurrent environment.

The above is how to analyze the underlying principles of mysql transactions and isolation levels. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.