MySQL INNODB Transaction implementation process related content collation

Source: Internet
Author: User
Tags flush flushes rollback



The implementation of MySQL transaction involves redo and undo as well as Purge,redo to ensure the atomicity and persistence of transactions; Undo is the consistency of the transaction (consistent read and multiple versioning concurrency control); Purge cleanup undo table space
Background, for each row of rows in the InnoDB table, include:
6-byte transaction ID (db_trx_id) field: Used to identify the most recent modification to our records (insert| UPDATE), which is the last modification of the transaction (insert| UPDATE) The transaction ID of the bank record.
7-byte rollback pointer (db_roll_ptr) field: The Undo log record (revocation logging) written to the rollback segment (ROLLBACK segment).
If a row of records is updated, the UNDO LOG record contains the information that is necessary to rebuild the record before the row was updated.

1,mysql transaction execution,
for undo log
for the update or delete operation, each row has a transaction ID, modify the transaction ID for the current session of the transaction ID,
generates the version before the data row transaction, and the current The row's rollback pointer points to the previous version of the transaction.
for insert operations, the rollback pointer for the current row is null, because insert has no previous version of the transaction operation.
for redo log
as the Update\delete\insert operation executes, redo log redo logs are continuously written to the redo log cache (Redo_log_buffer),
for redo log buffer ( Write Redo Log File) with three strategies:
(1), transaction Commit,
(2), Redo_log_buffer (default 8MB) when using more than 50%,
(3), when checkpoint occurs that is, Redo log buffe is not necessarily the time to write the transaction commits, for large transactions, Redo log is likely to gradually drop the disk (2, 32 points of influence)

2, commit commit for transaction
Redo Log Buffer is determined by the variable innodb_flush_log_at_trx_commit, and there are three modes 0,1,2
If set to 0, the commit does not trigger the redo log buffer write disk, writes the redo log buffer record to the redo log file every n seconds, and brushes the redo log file into the hardware store 1 times, n by Innodb_flush_log_at_ Timeout control.
If set to 1, the transaction commits synchronously flushes the redo log buffe to the redo log file and flushes the redo log file to disk.
If set to 2, the transaction commits a synchronous flush of redo log Buffe to the redo log file, but the flush (swipe to disk) operation of redo log does not take place at the same time. Redo Log writes are controlled by the operating system and innodb_flush_log_at_timeout.
It is important to note that, in the case of Innodb_flush_log_at_trx_commit=1, although transaction submissions can guarantee the redo log synchronous write disk,
However, redo Log Buffer's write disk is not necessarily written only when the transaction is committed, it is possible that the execution (if the transaction is large) is gradually written on the disk.

3, after the transaction
Because of the existence of the redo log (after the write disk), the consistency and durability of the transaction is guaranteed, for dirty data in memory, through the checkpoint or memory mechanism to the disk, after the data is written to disk, the redo log space can be released
For undo log, the space occupied by the undo log is cleared asynchronously by the purge thread when there is no active session access

See a lot of people write blogs using xmind or other tools to organize relevant knowledge points,
Found similar to Xmind structure diagram for the knowledge structure is very clear, xmind the first job used, long time useless, installed the trial version of the discovery features limited.
So try similar to Xmind online tool, found that Baidu brain map can also, the key is online, anytime and anywhere can be opened to organize relevant knowledge points.

Reference:
1190000012650596
https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/
MySQL Technology insider InnoDB storage Engine

MySQL INNODB Transaction implementation process related content collation

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.