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

The following mainly brings you how the MySQL transaction mechanism is implemented, and I hope that how the MySQL transaction mechanism is implemented can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

MySQL provides two transactional storage engines: InnoDB and NDB Cluster. There are also third-party storage engines that support transactions, including XtraDB and PBXT. The following is illustrated by InnoDB.

MySQL will maximize the use of caching mechanism to improve database access efficiency, but what if there is a power outage in the database and the cached data is not written to disk, resulting in the loss of cached data in memory, resulting in data inconsistency?

InnoDB mainly implements the ACID feature through the transaction log, which 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. download

The transaction log is appended, so the operation of writing the log is the sequential I _ peg O of a small area of the disk, unlike the random I _ An O, which needs to move the head in multiple places on the disk. So the way of transaction logging is much 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 call pre-write logging, that is, modifying data requires writing to disk twice.

The transaction log includes the redo log redo and the rollback log undo,Redo, which records all completed transactions, that is, transactions that have executed commit, and the record file is ib_logfile0 ib_logfile1. Undo records outstanding transactions that have been partially completed and written to the hard disk, and the rollback log is recorded in the tablespace (shared or exclusive tablespace) by default.

In general, after the mysql crashes, the service is restarted. Innodb rollback all the outstanding transactions that have been completed and written to disk by rolling back the log undo, and then all the transactions in redo are reexecuted again to recover the data. However, with the increase in the amount of redo, it takes a long time to recover from the first item of redo, so the checkpoint mechanism is introduced.

During the general business operation, when the business needs to modify a row of data in a table, innodb will first read the data from the disk to the cache, and then modify the data in the cache, so that the data in the cache is inconsistent with the data on the disk. At this time, the data in the cache is called dirty page, and only when the dirty pages are flushed to disk will it be clean page. download

Checkpoint: if at a certain point in time, the data of the dirty page is flushed to disk, the system records the refresh point in time to the end of the redo log. When the data is recovered, the data before the checkpoint point in time does not need to be recovered, and the time can be shortened.

Innodb_log_buffer_size redo log cache size

The more the Innodb_log_file_size redo log file size, the longer it takes for big data to recover.

The default number of Innodb_log_file_group redo log files is 2 ib_logfile0 ib_logfile1

The mechanism of transaction logging actually satisfies the atomicity and persistence of transactions, that is, they either succeed or fail. When it comes to the consistency and isolation of transactions, we should talk about "locks".

InnoDB uses a two-phase locking protocol. Locks can be executed at any time during transaction execution, locks are released only when COMMIT or ROLLBACK are executed, and all locks are released at the same time. InnoDB automatically locks when needed according to the isolation level.

Most of MySQL's transactional storage engines do not implement simple row-level locks. In order to improve the concurrency performance, they generally implement multi-version concurrency control (MVCC) at the same time. MVCC implements non-blocking read operations and write operations to lock only the necessary rows. MVCC is implemented by saving a snapshot of the data at a certain point in time. For InnoDB, this is achieved by keeping two hidden columns after each row of records. These two columns, one saves the creation time of the row, and the other saves the expiration time (or deletion time) of the row. Of course, what is stored is not the actual time value, but the system version number. Each time you start a new transaction, the system version number is automatically incremented. The system version number at the beginning of the transaction is used as the version number of the transaction, which is used to compare with the version number of each row of records queried. Let's take a look at how MVCC works under MySQL's default isolation level, REPEATABLE READ. download

SELECT

InnoDB checks each row of records against the following two criteria:

A. InnoDB only looks for rows whose version is earlier than the current transaction version (that is, the system version number of the row

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.