Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the process of MySQL transaction submission". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The common way for storage engines to implement transactions is based on redo log and undo log.

To put it simply, redo log records the modified data after the transaction, and undo log records the original data before the transaction.

So the actual process that occurs when a transaction is executed is simplified as follows:

Record the undo/redo log first and make sure that the log is brushed to disk for persistent storage.

Update data records, cache operations and refresh disk asynchronously.

Commit the transaction and write the commit record in redo log.

If the transaction in MySQL is interrupted due to failure, you can redo the transaction through redo log or roll back and forth through undo log to ensure the consistency of data.

This is done by the transactional storage engine, but the binlog is not within the scope of the transactional storage engine, but is recorded by the MySQL Server.

Then the consistency between binlog data and redo log must be ensured, so the actual transaction execution is one more step after binlog is enabled, as follows:

Record the undo/redo log first and make sure that the log is brushed to disk for persistent storage.

Update data records, cache operations and refresh disk asynchronously.

Persist the transaction log to binlog.

Commit the transaction and write the commit record in redo log.

In this way, the entire transaction needs to be rolled back as long as the binlog is not written successfully, and even if the binlog is successfully written, the transaction can be resumed and the commit can be completed even after the MySQL Crash.

MySQL ensures the consistency and persistence of database transactions through WAL, that is, C (consistent) and D (durability) in ACID features.

WAL (Write-Ahead Logging) is a standard way to implement transaction logging, specifically:

1. Be sure to write a log before modifying the record

2. In the process of transaction commit, you must ensure that the log is closed before the transaction commit is completed.

Through the WAL method, the performance of the database can be improved while ensuring the transaction characteristics.

As can be seen from the above process, there are four main things done in the submission process.

1. Clean up the information of undo segment. For the update operation of innodb storage engine, purge is required for undo segment. The main function of purge here is to delete physical records. When performing a delete or update operation, the actual old record is not really deleted, but is marked on the record, but is actually deleted by the purge thread after the transaction is committed, freeing up physical page space. Therefore, the undo information is added to the purge list during the submission process for processing by the purge thread.

2. Release lock resources. Mysql ensures that different transactions do not operate a single record at the same time through the lock mutual exclusion mechanism. Only after the transaction is executed will all lock resources be truly released and other transactions waiting for their lock resources will be awakened.

3. Browse the redo log. As we mentioned earlier, mysql implements the mechanism of transaction consistency and persistence. Through the redo log operation, it ensures that even if the modified data page is not updated to disk, as long as the log is completed, the integrity and consistency of the database can be ensured.

4. Clean up the SavePoint list. Each statement will actually have a savepoint (SavePoint). The SavePoint function is to roll back to the state before the execution of any statement of the transaction. Since the transaction has been committed, the SavePoint list can be cleaned up.

This is the end of the content of "MySQL transaction submission process". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.