It’s getting late, there’s color in my eyes as I look at you

sequence

In MySQL Transaction Learning Notes (1), we explained how to start a transaction, commit and rollback. But there is one small tail that is missing, which is how to set the isolation level of a transaction. This article describes how to set the isolation level in MySQL and how to implement transaction ACID in ySQL and the isolation level. When I was writing this article, I was also thinking about how to organize the content, whether to reorganize the content of the materials I read, or to list the knowledge points in the form of notes. Frankly speaking, I do not like the form of listing knowledge points very much. I feel that there is no line organized. I personally prefer the knowledge organization structure like a tree, with a trunk. So this paper introduces MySQL is to achieve transaction implementation, will first introduce its organization from the macro, part of the knowledge point is not too detailed, this way we can first grasp its backbone, will not get lost in the details.

Sets the transaction isolation level

select @@tx_isolation;
Copy the code

My MySQL default isolation level is repeatable reads, and SQL transaction isolation level is:

  • Uncommitted read
  • Has been submitted to read
  • Repeatable read
  • Serializable.

MySQL supports setting isolation levels at run time and startup time:

  • Set isolation level at startup:

    The configuration file for Windows is my.ini

    The configuration file for Linux is my.cnf

    Add: transaction-Isolation = Isolation level in the configuration file

    Isolation level candidate values: READ COMMITTED, REPEATABLE READ, READ UNCOMMITTED, SERIALIZABLE

  • Set the isolation level at run time

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

Candidate values of LEVEL are READ-COMMITTED, REPEATABLE READ, READ UNCOMMITTED, SERIALIZABLE

The GLOBAL keyword is globally affected after executing the following statement:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Copy the code

The isolation level for all subsequent sessions becomes serializable;

The SESSION keyword, on the other hand, applies only to the scope of the SESSION and only to subsequent transactions if the transaction has not been committed.

If neither GLOBAL nor SESSION exists, it is valid only for the next transaction to be started in the current SESSION. After the next transaction executes, the sequenced transaction reverts to its previous isolation level. The statement cannot be executed in an already opened transaction and an error is reported.

Let’s demonstrate the problems that can occur with transactions at different isolation levels:

  • The isolation level of the transaction is uncommitted read:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Copy the code

​

Then open another window:

​

A dirty read occurred

  • The isolation level of the transaction is committed read
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Copy the code

Start another conversation:

​

Unrepeatable reads appear:

  • The isolation level is repeatable read
SET GLOBAL TRANSACTION ISOLATION LEVEL  REPEATABLE READ;
Copy the code

MySQL can disable phantom reading at this level.

This is a typo. It’s 5 and 6.

MySQL implements isolation level (ACID).

Redo atomic persistence

In “MySQL Optimization Learning Notes (1)”, we talked about how MySQL uses pages as the basic unit of interaction between disk and memory. In fact, adding, deleting, modifying and querying are accessing pages (reading, writing, creating new pages). Although we are accessing pages, we are not accessing pages of disk, but pages of cache pool. MySQL updates the cache pool to disk periodically by updating the cache pool to disk periodically by updating the cache pool to disk periodically. Persistence means that after a committed transaction is committed, changes made by the transaction to the database cannot be lost even if the system crashes.

The simple and no-brainer approach would be to refresh a buffer pool page to disk as soon as it is updated, but refreshing a full page is too wasteful. Sometimes only one field of a row on a page is changed, which is a bit expensive to flush to disk. Second, suppose the transaction is only a single statement, but changes many pages of data. Unfortunately, these pages are not adjacent, which is slow.

MySQL’s approach is to modify the data storage of information, such as Student’s id = 1 this column name changed to zhang SAN, MySQL will store the data in the data page of the xyz rows of columns to zhang SAN, incremental, record the changes. So after we transaction commit, we will change the flushed to disk, even before the worker thread could refresh the buffer pool pages to disk, system crash, restart again according to the records we change the data again, recording of data in MySQL change was known as the redo log, is the redo log. The benefits of flushing redo logs directly to disk during transaction execution are as follows, as opposed to flushing all modified in-memory pages to disk at transaction commit time:

  • Redo logs take up very little space
  • Redo logs are written to disk in sequence

For atomicity, the redo log is also used. For these atomicity operations, redo logs must be recorded as a group. During data recovery, all or none of the logs of a group in the system are either restored. Redo logs have their own cache and are not flushed directly to disk.

Undo Log rollback

What if the system fails in the middle of the transaction, or if a manual rollback is performed, how do we roll back? The answer is to record the changes, namely what changes to what (UPDATE INSERT, UPDATE). MySQL calls these changes the undo log. Different types of Update logs differ. If a transaction adds, deletes, or modifies a table, InnoDB assigns a unique transaction ID to the transaction. MySQL uses pages as the basic unit of interaction between disk and memory. Pages contain rows with multiple hidden columns per row:

  • Trx_id: Each time a transaction changes a cluster index record, the transaction ID is assigned to the trx_ID hidden column.
  • Roll_pointer: Every time a change is made to a clustered index record, the old version is written to the undo log. The hidden column is then used as a pointer to the previous record.

MySQL will queue all transactions to update one record. So let’s see, what if two transactions update one record at the same time?

Lock Wait timeout exceeded; try restarting transaction.

An undo log is logged every time a change is made to the record, and each undo log has a roll_pointer attribute, which can be strung together into a linked list. The header of the version chain records the latest value of the current record, and each version also contains a transaction ID. For transactions with an isolation level of READ UNCOMMITED, it is good to simply READ the latest version because data that has not been modified by committed transactions can be READ. For READ COMMITED and REPEATABLE READ transactions, it is necessary to READ the committed transaction. If the current transaction is not committed, the latest version record cannot be READ. Then the problem is which record in the linked list should be READ. This leads us to the concept of READ VIEW.

READ VIEW generation time MVCC

READ VIEW has four important things:

  • M_ids: represents a list of transaction ids of the read and write transactions currently active in the system at the time the ReadView was generated
  • Min_trx_id: indicates the minimum transaction ID (the minimum value of m_IDS) of the read/write transactions active in the system at the time the ReadView was generated.
  • Max_trx_id: indicates the ID that the system should assign to the next transaction when the ReadView is generated.
  • Creator_trx_id: indicates the transaction Id of the transaction that generated this ReadView.

If the current transaction accesses trx_id and creator_trx_id in READ VIEW and then accesses its own modified record, it simply accesses the latest header of the linked list.

If the accessed version’s trx_id is less than the min_trx_id value in ReadView, it indicates that the transaction that generated the version was committed before the current transaction generated the ReadView, so the version is accessible by the current transaction.

If the accessed version’s trx_id is greater than or equal to or max_trx_id in the Read View, the generated version of the transaction is not started until the current transaction generates the Read View, so the version is not accessible by the current transaction.

If the value of the trx_id attribute is between the min_trx_id and max_trx_id of the ReadView, then you need to check whether the value of trx_id is in m_IDS. If yes, the transaction that generated the ReadView was active when the ReadView was created, and the version is not accessible. If not, the transaction that generated that version of the ReadView was committed when it was created.

Now the way to access data is to traverse the corresponding undo linked list of data and judge the visibility according to the steps. If the visibility is not visible at the end of the traverse, it is really not visible.

One very big difference between READ COMMITED and REPEATABLE READ isolation levels in MySQL is when a ReadView is generated. During the execution of a transaction, a single transaction ID is assigned only on the first actual record modification (INSERT DELETE UPDATE), and this transaction ID is incremented.

Here are some examples to illustrate the process of querying at different isolation levels. At the beginning of the story we still prepare a table:

CREATE TABLE `student`  (
  `id` int(11) NOT NULL COMMENT 'Unique identifier'.`name` varchar(255) COMMENT 'name'.`number` varchar(255) COMMENT 'student id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB 
Copy the code

READ COMMITTED Generates a READ View for each query

There are now two transactions with ids 200 and 300 in progress, as follows:

# transaction ID = 200, ID = 1, name = king before this transaction starts
BEGIN;
update  student set name =  'bill' where id = 1;
update  student set name =  'Cathy' where id = 1;
Copy the code
Transaction ID = 300
BEGIN;
Alter table table_name update table table_name
Copy the code

At this time, the version chain of the record with ID 1 is shown below:

Now another transaction starts to query the record with id=1. Execute the SELECT statement to generate a Read View with the value [200,300],min_trx_id 200, max_trx_id 301, and creator_trx_id 200. Trx_id = 200, does not comply with the visibility rule in min_IDS, accesses the next record, the next record trx_id is 200, skip to the next record. Trx_id is less than min_trx_id and is returned to the user.

REPEATABLE READ Generates a READ View for the first READ

The same update statement as above:

# transaction ID = 200, ID = 1, name = king before this transaction starts
BEGIN;
update  student set name =  'bill' where id = 1;
update  student set name =  'Cathy' where id = 1;
Copy the code
Transaction ID = 300
BEGIN;
Alter table table_name update table table_name

Copy the code

Then use the isolation level of REPEATABLE READ to query:

begin;
SELECT * FROM Student Where id = '1';
Copy the code

The SELECT query above generates 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 not visible in min_ids, and the trx_id of Li Si is also 200, also not visible in min_id. The version ID is less than min_trx_id in the read view, indicating that the record was created before the Reada view. Then commit the transaction ID=200.

BEGIN;
update  student set name =  'bill' where id = 1;
update  student set name =  'Cathy' where id = 1;
COMMIT;
Copy the code

Then the transaction ID 300 is also changed to ID = 1.

begin;
update  student set name =  'xu four' where id = 1;
update  student set name =  'a zhao' where id = 1;
Copy the code

The current version chain looks like this:

Select * from user where id = 1;

begin;
SELECT * FROM Student Where id = '1';
Copy the code

The transaction id of the current record is min_ids[200,300]. The transaction id of the current record is not visible. The transaction id of the next record is min_ids (trx_id 300). Then jump to the next record, whose TRx_ID is also in min_IDS and not visible. Until “Wang ha ha,” which is what repeatable means. Even if the transaction with transaction ID 300 commits, other transactions will read it and say “king ha ha”. After all transactions for this record have been committed, querying the record again reproduces the Read View. This is the multi-version Concurrency Control (MVCC) approach to avoid dirty and unrepeatable reads at READ COMMITTED and REPEATABLE READ isolation levels. READ COMMITE generates a READ View for each query. REPEATABLE READ generates the READ View in the first related record query, and then the READ View is reused by the query operation in the transaction, after the commit. When the corresponding record is queried, it is generated again.

To summarize

MySQL under the use of undo, redo atomic, persistent, committed read, can repeat read. Redo records what has changed, and undo is used for rollback. To support MVCC, deleted records are marked instead of being deleted immediately after deletion. Seems to be in contact when I was just graduated in MVCC, think this is very sophisticated and complex, at that time today at the time of writing this article, first, from the perspective of the macro, not presented before going to the format of the undo and redo log details, based on my experience, introduce the format will be very let a person feel dizzy, lost in the detail, The original intention was to understand MySQL’s implementation of ACID and transactions. So this article covers only what is necessary and will cover it again in future articles when it is necessary to refer to the details of these logs.

The resources

  • The four MySQL transaction isolation level blog.51cto.com/moerjinrong…
  • MySQL juejin.cn/book/684473…
  • / Mysql – understand affairs by examples of four kinds of isolation level www.cnblogs.com/snsdzjlz320…