Transaction is one of the most important mechanisms of a database. Anyone who has used a database knows the transaction mechanism of a database and knows the four basic properties of ACID as well. However, when talking about transactions or the underlying implementation of ACID, it is often unclear. Therefore, today we will analyze and discuss the transaction mechanism of InnoDB, hoping to establish a specific understanding of the underlying implementation principle of transactions.

Database transactions have four properties of ACID. ACID is the abbreviation of these four words:

  • Atomicity: Transaction minimum unit of work, all success or all failure.
  • Consistency: The integrity of the database is not compromised after a transaction starts and ends.
  • Isolation: Different transactions do not affect each other. The four isolation levels are RU (read uncommitted), RC (read committed), RR (repeatable read), and SERIALIZABLE.
  • Durability: After transactions are submitted, modifications to data are permanent and not lost even if systems fail.

Let’s take a concrete example to introduce the principles of database transactions and how InnoDB implements ACID.

The sample is introduced

Let’s start with a concrete example. You can personally experiment, so that understanding and memory will be more profound.

First, use the following SQL statement to create two tables, goods and Trade, representing goods and trade. Insert a record into the Goods table where the quantity of goods with ID 1 is 10.

CREATE TABLE goods (id INT, num INT, PRIMARY KEY(id));
CREATE TABLE trade (id INT, goods_id INT, user_id INT, PRIMARY KEY(id));
INSERT INTO goods VALUES(1, 10);
Copy the code

Then open the terminal, connect to the database, start session 1, start a transaction with BEGIN display. Session 1 subtracts the goods table with id 1 by one, then adds a record of a transaction to the Trade table, and finally displays the COMMIT transaction using COMMIT.

Add a transaction to the trade table, update the goods table with id 1, and ROLLBACK the transaction with ROLLBACK. The specific statements and order of execution in the two sessions are shown below.

This example illustrates many of the features of database transactions, which we will cover one by one. First, operation 2 of session 1 updates the number of goods with ID 1, but operation 5 of session 2 still reads the number of goods with ID 1. This reflects the isolation of transactions and is implemented using InnoDB’s multi-version control mechanism.

InnoDB has locked the row lock for the record, so operation 7 will block until session 1 COMMIT. But session 1’s operation 4 and session 2’s operation 7 both insert records into the TRADE table, and the latter does not block because they insert different rows of records. Lock mechanism is a common concurrency control mechanism, which together with multi-version control mechanism realizes the isolation of InnoDB transactions. For details about InnoDB lock, please refer to InnoDB lock type and state query and InnoDB row lock algorithm.

The atomicity of the transaction is demonstrated by the fact that session 1 commits the transaction with COMMIT and session 2 rolls back the entire transaction with ROLLBACK. That is, a series of transactions are either committed or ROLLBACK. There is no partial execution. InnoDB uses a transaction logging system to achieve atomicity of transactions. Some of you might ask what happens if the connection breaks or the Server crashes. What can I do? I just roll it back.

Once a session commits a transaction using the COMMIT operation, the data must be written to the database and stored persistently, which demonstrates the persistence of the transaction. InnoDB uses the redo log mechanism for transaction persistence.

The consistency of transactions is difficult to understand. Simply speaking, at the beginning of a transaction, the database has a state at this time, which is the same state of all MySQL objects, for example, the database integrity constraint is correct, the log state is consistent, etc. When the transaction is committed, the database has a new state, different data, different indexes, different logs, etc. However, constraints, data, indexes, logs, and other MySQL states must be consistent. So the database goes from one consistent state to another consistent state. After the transaction executes, the database integrity constraints are not broken.

Let’s take a closer look at the implementation of the ACID nature of the transactions covered by the above example. In summary, transaction isolation is achieved through multiple versioning mechanisms and locks, while atomicity, consistency, and persistence are achieved through InnoDB’s Redo log, undo log, and Force log at Commit mechanisms.

Atomicity, persistence and consistency

Atomicity, persistence, and consistency are achieved through redo log, undo log, and Force log at Commit mechanisms. The redo log is used to recover data in the event of a crash, the undo log is used to undo the impact of a transaction, and can be used for multiple versioning. The Force Log at Commit mechanism ensures that redo Log logs are persisted after a transaction is committed.

After starting a transaction, users can COMMIT it using COMMIT or ROLLBACK. After the COMMIT or ROLLBACK is successfully executed, all data must be saved or rolled back to the original state, which also reflects the atomicity of transactions. However, there are also many exceptions, such as connection disconnection during transaction execution, error during COMMIT or ROLLBACK, Server Crash, etc. At this time, the database will be automatically rolled back or recovered after restart.

The redo log is a physical log that records physical changes to a database page caused by a database SQL operation. If data is lost, the database can recover data based on the redo log.

InnoDB implements transaction persistence through Force Log at Commit. When a transaction is committed, all logs of the transaction must be written to the redo Log file for persistence.

When various SQL operations are performed on a transaction, data is modified in the buffer and the corresponding redo log is written to the cache to which it belongs. When a COMMIT is performed, the redo log buffer associated with the transaction must be flushed to disk before the COMMIT is considered successful.

When redo logs are written to disk, an operating system fsync operation must be performed to prevent redo logs from only being written to the operating system disk cache. The innodb_flush_log_at_trx_commit parameter controls the flush policy for redo log logs to disk. See InnoDB’s disk files and drop disk mechanism for details

After the redo log is written to disk, the database is flushed to disk at a CheckPoint and the redo log is deleted, even if the COMMIT succeeds. However, if the database crashes during this process, the redo log file is used to restore data in memory that has not been updated to disk.

To improve database performance, data pages are not flushed to disk every time they are modified in memory. InnoDB uses a checkpoint mechanism to checkpoint data pages to ensure that the previous data page has been dropped, so that the associated redo log is no longer used. Data pages after checkpoint may or may not fall, so post-checkpoint redo log files need to be used during crash recovery. InnoDB periodically pushes checkpoint based on the flush of dirty pages to reduce the recovery time of database crashes. The checkpoint information is at the head of the first log file.

After the database restarts after a crash, you need to recover the dirty page data from the redo log and write it to the disk again to prevent data loss. Of course, in crash recovery you also need to roll back uncommitted transactions. Undo logs are required for rollback operations, and redo logs are required to ensure the integrity and reliability of undo logs. Therefore, redo logs are used to restore data before Undo rollback.

In addition to redo logs, a certain amount of undo logs are logged during transaction execution. The undo log records the status of data before each operation. If a transaction needs to be rolled back during execution, the undo log can be used to roll back data.

Undo logs are stored differently from redo logs. They are stored in a special segment within the database called a rollback segment. The rollback segment is in the shared table space. The undo page is the smaller organization unit in the undo section. Undo Page is similar to a page that stores database data and indexes. Because redo logs are physical logs, they record physical changes to database pages. Undo logs also generate redo logs. Undo logs also generate redo logs because they require persistence protection. As shown in the figure above, there are rollback segments and leaf segments and non-leaf segments in the table space, and all three have corresponding page structures.

Let’s summarize the entire flow of a database transaction again, as shown in the figure below.

Each time an SQL statement is executed, the undo log and redo log are recorded and updated to form dirty pages. The redo log is logged based on time or space. The undo log and dirty pages are logged based on checkpoint. The redo log is now deleted. If the transaction is not committed, the checkpoint record is checked and the redo log is used to restore data and the undo log. Then, the undo log is used to rollback the transaction. When a COMMIT is performed, all redo logs related to the transaction are dropped. A COMMIT is successful only when all redo logs are dropped. Then the dirty pages in memory continue to checkpoint. If a crash occurs, only the redo log is used to restore the data.

Isolation,

InnoDB transaction isolation is mainly achieved through multi-version control mechanism and lock mechanism. For details, please refer to multi-version control, InnoDB lock type and state query and InnoDB row lock algorithm three articles.

Afterword.

Originally wanted to write an article to explain the transaction mechanism of MySQL, after reading it again, or found that the content is somewhat obscure, complex knowledge is difficult to explain, fu Yi close, then many visitors; Danger is far, then to less, I hope readers to this article as a guide to the article, and then go to a more in-depth place to explore. However, it is also a great ability to explain complex knowledge in a simple and popular way. Text and explanation ability still need to be prompted.

  • Mysql > select * from b-tree

  • Explore the internal storage structure of database

  • SQL statement execution process in detail

  • InnoDB memory structure and features

  • InnoDB disk files and drop disk mechanism

  • InnoDB lock type and state query

  • InnoDB read InnoDB read InnoDB read InnoDB read InnoDB read InnoDB read InnoDB read InnoDB

reference

  • MySQL · Engine features · InnoDB Transaction system
  • MySQL · Engine features · InnoDB crash recovery process