MySQL transaction study notes (2) acquaintance

It's getting late, the eyes are colorful in my eyes "Entertainment Sky"

sequence

In "MySQL Transaction Study Notes (1) First Encounter", we have explained how to open a transaction, commit, and roll back. But there is another small tail that is missed, which is how to set the isolation level of the transaction. In this article, we will introduce how to set the isolation level in MySQL and how to implement ACID and isolation level of transactions in ySQL. When writing this article, I was also thinking about how to organize the content, whether to reorganize the content of the materials I read, or take notes and list the knowledge points. Frankly speaking, I don't really like the form of listing knowledge points. I feel that there is no line to organize it. Personally, I prefer a tree-like knowledge organization structure with a trunk. Therefore, in this article, when introducing MySQL to realize transaction implementation, it will first introduce its organization from a macro perspective, and some knowledge points will not be too detailed. This way allows us to grasp its backbone first and not get lost in the details.

Set transaction isolation level

select @@tx_isolation;

My MySQL default isolation level is repeatable read, and the isolation level of SQL transactions:

• uncommitted read
• Submitted for read
• repeatable read
• Serializable.

MySQL supports setting isolation levels at runtime and startup:

• Set the isolation level at startup:

  The configuration file under windows is my.ini

  The configuration file under Linux is my.cnf

  In the config file add: transaction-isolation = isolation level

  Candidate values ​​for isolation level: READ COMMITTED, REPEATABLE READ, READ UNCOMMITTED, SERIALIZABLE

• Set isolation level at runtime

​ SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

​ LEVEL candidates: READ-COMMITTED, REPEATABLE READ, READ UNCOMMITTED, SERIALIZABLE

The GLOBAL keyword affects the global scope, after executing the following statement:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

The isolation level of all subsequent sessions will become serializable;

The SESSION keyword is only affected within the scope of the session. If the transaction has not been committed, it is only valid for the subsequent transaction.

​ If neither GLOBAL nor SESSION is available, it is only valid for the next transaction to be opened in the current session. After the execution of the next transaction, the subsequent transaction will be restored to the previous isolation level. This statement cannot be executed in an already opened transaction and an error will be reported.

​ Let's demonstrate the problems that occur in transactions at different isolation levels:

• The isolation level of the transaction is read uncommitted:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

​ Then open another window:

Dirty read occurred

• The isolation level of the transaction is read committed

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Open another session:

​

An unrepeatable read occurred:

• Isolation level is repeatable read

SET GLOBAL TRANSACTION ISOLATION LEVEL  REPEATABLE READ;

We mentioned above that MySQL can prohibit phantom reading at this level. Let's test it here:

​ This picture is wrong, it is 5 and 6.

Let's talk about how MySQL implements isolation level and ACID.

redo atomic persistence

In "MySQL Optimization Learning Notes (1)", we mentioned that MySQL uses pages as the basic unit of interaction between disk and memory. In fact, adding, deleting, modifying and querying is actually accessing pages (reading, writing, creating new pages), although We are accessing the page, but we are not accessing the page of the disk, but the page of the cache pool. The updated page of the cache pool is periodically refreshed to the disk by the worker thread, then the problem is, the data of a certain page is changed, and the I didn't have time to refresh this page to disk and encountered some failures. How does MySQL ensure durability? The so-called persistence means that for a committed transaction, after the transaction is committed, even if the system crashes, the changes made by the transaction to the database cannot be lost.

The simple and no-brainer approach is to refresh the data page of the buffer pool to disk immediately after updating the data page, but it is too wasteful to refresh a complete data page. Sometimes we may only change a certain page in a certain page. A field of row data, which is a bit expensive to flush to disk. Secondly, suppose that although this transaction has only one statement, it modifies the data of many pages. Unfortunately, these pages are not adjacent, which is very slow.

MySQL's approach is to store the meta information of the modified data. For example, if the name of the column of Student's id = 1 is changed to Zhang San, MySQL will store this data in a certain row of the data page and change it to Zhang. Third, take the increment and record the change. In this way, after our transaction is committed, we will flush the changes to the disk, even if the worker thread has not had time to flush the pages of the cache pool to the disk, the system crashes, and when we restart, we will restore the data according to the changes in these records. Yes, recording changed data is called redo log in MySQL, especially redo log. Compared with flushing all modified in-memory pages to disk when the transaction is committed, the benefits of directly flushing the redo log generated during transaction execution to disk are as follows:

• The redo log takes up very little space
• redo logs are written to disk sequentially

What about atomicity? In fact, redo logs are used. When performing these operations to ensure atomicity, redo logs must be recorded in the form of groups. When data recovery is performed, the logs of a certain group in the system are either all recovered, or All are not restored. The redo log also has its own buffer area, and it is not flushed directly to disk.

undo log rollback

What if the transaction is half executed and the system is powered off, or if a rollback is performed manually, how do we rollback? The answer is to record the changes, what changed into what (the changes here refer to UPDATE INSERT, UPDATE ), MySQL refers to the data changed by these records as undo log, and different types of update log are different. If a transaction performs operations such as adding, deleting, and modifying a table, the InnoDB engine assigns a unique transaction id to the transaction. As we have talked about above, MySQL uses pages as the basic unit of interaction between disk and memory. The pages are row records, and each row will have multiple hidden columns:

• trx_id: Every time a transaction changes a clustered index record, the transaction id obtained by the transaction is assigned to the trx_id hidden column.
• roll_pointer: Every time a clustered index record is changed, the old version will be written to the undo log, and then this hidden column is equivalent to a pointer, which can be used to record the information before the modification.

Then some students may ask, what should I do if multiple transactions update one record, MySQL will make them queue for execution, which can be understood as a lock, let's try it, what if two transactions update a record at the same time?

After a while, Lock wait timeout exceeded; try restarting transaction will appear.

Every time a record is changed, an undo log will be recorded, and each undo log will also have a roll_pointer attribute. These logs can be strung together to form a linked list. The head node of the version chain records the latest value of the current record, and each version also contains a transaction ID. For a transaction whose isolation level is READ UNCOMMITED, since the data modified by the uncommitted transaction can be read, it is good to read the latest version directly. For transactions with READ COMMITED and REPEATABLE READ isolation levels, it is necessary to ensure that the committed transaction is read, that is to say, if the current transaction is not committed, the latest version record cannot be read, then the problem now is to read Which record in the linked list is taken, which leads us to the concept of READ VIEW.

READ VIEW generation timing MVCC

READ VIEW has four more important contents:

• m_ids: Represents the list of transaction IDs of read and write transactions that are active in the current system when ReadView is generated
• min_trx_id: Indicates the minimum transaction ID in the read and write transactions that were active in the system when the ReadView was generated, that is, the minimum value of m_ids.
• max_trx_id: Indicates the ID that the system should assign to the next transaction when generating the ReadView.

• creator_trx_id: Indicates the transaction ID of the transaction that generated the ReadView.

  If you access the trx_id of the version and the creator_trx_id table name in the READ VIEW, the current transaction will access its own modified records, and you can directly access the latest head node of the linked list.

  If the trx_id of the accessed version is less than the min_trx_id value in Read View, it indicates that the transaction that generated this version has been committed before the current transaction generates ReadView, so this version can be accessed by the current transaction.

If the trx_id of the accessed version is greater than or equal to or the max_trx_id in the Read View, it indicates that the transaction of the generated version is opened after the current transaction generates the Read View, so the version cannot be accessed by the current transaction.

If the value of the trx_id attribute of the accessed version is between min_trx_id and max_trx_id of ReadView, it is necessary to judge whether the attribute value of trx_id is in m_ids. Access, if not, indicates that the transaction that generated the version when the ReadView was created has been committed.

The way to access data now is to traverse the undo list corresponding to the data, and judge the visibility according to the steps. If the traversal is not visible until the end, it is really invisible.

In MySQL, a very big difference between READ COMMITED and REPEATABLE READ isolation level is the timing of generating ReadView. During the execution of the transaction, only when the record is actually modified for the first time (INSERT DELETE UPDATE), a separate transaction id will be assigned, and the transaction id is incremented.

Below we give some examples to illustrate the process of querying under different isolation levels. At the beginning of the story we still prepare a table:

CREATE TABLE `student`  (
  `id` int(11) NOT NULL COMMENT '唯一标识',
  `name` varchar(255) COMMENT '姓名',
  `number` varchar(255) COMMENT '学号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB 

READ COMMITTED generates a Read View for each query

There are now two transaction IDs 200 and 300 executing, like this:

# 事务ID 为 200， id = 1 的name 在这个事务开始之前为 王哈哈
BEGIN;
update  student set name =  '李四' where id = 1;
update  student set name =  '王五' where id = 1;

# 事务ID 为 300
BEGIN;
# 做更新其他表的操作

At this time, the version chain recorded in the row with id 1 is shown in the following figure:

Now another transaction starts to query the record with id=1, and executing the SELECT statement will generate a Read View. The value of the Read View is [200,300], the min_trx_id is 200, the max_trx_id is 301, and the creator_trx_id is 200. Then start traversing the undo list, the latest version is Wang Wu, trx_id = 200, it does not meet the visibility principle in min_ids, access the next record, the trx_id of the next record is 200, and jump to the next record. Wang Haha's trx_id is less than min_trx_id, and then returns this row of records to the user.

REPEATABLE READ generates a Read View when it is read for the first time

Or the update statement above:

# 事务ID 为 200， id = 1 的name 在这个事务开始之前为 王哈哈
BEGIN;
update  student set name =  '李四' where id = 1;
update  student set name =  '王五' where id = 1;

# 事务ID 为 300
BEGIN;
# 做更新其他表的操作

Then use the REPEATABLE READ isolation level to query:

begin;
SELECT * FROM Student Where id = '1';

The above SELECT query will generate a Read View: m_ids[200,300], min_trx_id=200, max_trx_id=301, creator_trx_id=0.

The latest version of trx=id is in min_ids, and this version is not visible. To the next record, Li Si's trx_id is also 200, and it is also in min_id, which is also invisible. The version id of Wang Haha is smaller than the min_trx_id in the read view, indicating that this record was generated before the Reada View, and the record is returned. Then submit the operation with transaction ID=200.

BEGIN;
update  student set name =  '李四' where id = 1;
update  student set name =  '王五' where id = 1;
COMMIT;

Then the transaction ID is 300 and the id = 1 is also modified.

begin;
update  student set name =  '徐四' where id = 1;
update  student set name =  '赵一' where id = 1;

The current version chain is as follows:

Query records with id = 1:

begin;
SELECT * FROM Student Where id = '1';

The read view has been generated before, reuse the above read view, then the transaction id of the current record is in min_ids[200,300], the record is not visible, skip to the next record, the trx_id of the next record is 300, and it is also in min_ids , it is not visible, and then jumps to the next record. The trx_id of the next record is also in min_ids and is not visible. Until "Wang Haha", which is what repeatable reading means. Even if the transaction with transaction ID 300 is committed, other transactions will read it as "Wang Haha". After all transactions for this record are committed, querying the record again will generate a Read View again. This is also the MVCC (Multi-Version Concurrency Control multi-version concurrent access control), the strategy adopted to avoid dirty reads and non-repeatable reads at the READ COMMITTED and REPEATABLE READ isolation levels. READ COMMITE generates a Read View for each query. The REPEATABLE READ is to generate a Read View when the related record query is performed for the first time, and then the Read View is reused by the query, and the Read View reused by the query operation in these transactions is submitted after submission. When the corresponding record is queried again, it will be regenerated.

in conclusion

Under MySQL, undo and redo are used to achieve atomicity, durability, committed read, and repeatable read. redo records what has changed, and undo is used to roll back. In order to support MVCC, after the corresponding record is deleted, it will not be deleted immediately but will be marked. It seems that I came into contact with MVCC when I was just graduating. At that time, I thought it was very high-end and complicated. When I wrote this article today, I still started from the macro level, and did not introduce the details about the format of the undo and redo logs that I planned to use before. , in my experience, it can be dizzying to introduce these formats and get lost in the details, in fact, the original intention is just to understand MySQL's implementation of ACID and transactions. Therefore, this article only introduces the necessary content, and will introduce it again when the detailed introduction of these logs must be cited in the following articles.

References

• Four isolation levels of MySQL transactions https://blog.51cto.com/moerjinrong/2314867
• How MySQL Works: Understanding MySQL from the https://juejin.cn/book/6844733769996304392/section/6844733770071801870
• [Mysql]——Understanding the 4 isolation levels of transactions through examples https://www.cnblogs.com/snsdzjlz320/p/5761387.html