MySQL Transaction Study Notes (3) Very Huan

When I first started working, I heard my seniors talk about this concept, but I still don't understand it. This concept is also lying in my study plan, and I put it on hold until today to prepare to learn this concept thoroughly.

I will make a study outline with concepts I am interested in, and whenever I open Typora, I want to write this and I want to write this again. I think of Cang Yue, a martial arts novelist who I liked in high school. According to her statement, she also has some unfinished novels stored in her computer, and every time she turns on the computer to create, these novels seem to say to her: Write me, write me. This article mainly describes the related concepts of locks in InnoDB. The main content is also referred to from the Nuggets booklet "How MySQL Works: Understanding MySQL from the Root". I mainly selected the content I wanted to know and combined it. one time.

It is recommended that when reading the articles in this series, you should first read:

• MySQL Optimization Learning Notes (1)
• MySQL optimization study notes handbook (2)
• MySQL Optimization Learning Notes (3)
• MySQL transaction study notes (1) first encounter
• MySQL transaction study notes (2) acquaintance

situation that will go wrong

If you just read, there will be no concurrency problem, then we will ignore this situation first, then the hardest hit area of ​​the problem is:

• write - write

That is, concurrent transactions make changes to a record one after another, in which case dirty writes may occur. MySQL does not allow dirty writes to occur at any isolation level. Therefore, when multiple concurrent transactions make changes to the same record, MySQL will make them queue for execution. This queue execution is actually implemented through locks, similar to the current interview (without considering the situation of the group), the general interview After the interviewer has finished interviewing the person, HR will be called to let the next person in. In MySQL, when a transaction wants to make changes to a transaction, it will first check whether there is a lock structure associated with this record in memory, and if not, a lock structure will be generated in memory to be associated with it.

• read-write or write-read

In this case, dirty reads, non-repeatable reads, and phantom reads may occur. Note here that the phantom read problem occurs because a transaction reads a range of records, and then other transactions insert new records in the range. When the transaction reads the records in the range again, it can read the newly inserted records. record of. So phantom reads are not caused by reading and writing the same record.

We have already talked about the problems that may occur under different isolation levels. I will briefly mention them here, and I will not repeat them too much. Different database vendors may support different SQL standards. MySQL is in the The REPEATABLE READ isolation level has actually solved the phantom read problem.

The solution in MySQL is two:

• MVCC: Reads use multi-version concurrency control (MVCC), and writes are locked.
• Both read and write are locked

In some business scenarios, we are not allowed to read the old version of the record, but must read the latest version of the record every time. For example, in the bank's deposit transaction, we need to read the account balance first, then add it to the amount of this deposit, and finally write it into the database. After the account balance is read, it is not necessary for other transactions to access the balance. No other transactions can access the account balance until this deposit transaction is completed. In this way, when reading the record, it needs to be locked.

Why, let's analyze it. We are still discussing at the level of repeatable reading. Suppose we take the transfer business as an example, what will happen if the old account balance is allowed to be read.

If it is like the following statement to execute:

  UPDATE Student Set money = money + 50 where id = '1';

Two commits are not a problem because the two transactions are queued for execution. My original idea was that MySQL also read the UPDATE, so when I read the record before the transaction was committed, I would eventually transfer the account twice and only add fifty dollars. Then if it is to perform operations, for example, we perform operations in the code, and then the final update statement will be problematic as follows:

  UPDATE Student Set money = 60 where id = '1';

The transaction submitted later will overwrite the transaction submitted earlier. This is not a dirty write, but two independent update operations.

If the MVCC method is used, the read-write operation does not conflict, and the performance is better. If the locking method is used, it needs to be executed in a queue, which affects the performance. In general, we prefer to use MVCC to solve the problems caused by concurrent transaction execution, but in some cases, the business cannot accept MVCC, and it seems that there is only one option left in MySQL.

The read operations performed by a transaction using MVCC are called consistent reads, or consistent lock-free reads and snapshot reads. All SELECT statements are considered consistent reads at the READ COMMITTED and REPEATABLE READ isolation levels.

Analysis of the concept of lock

Row shared locks and exclusive locks

Locks in MySQL can be roughly divided into two categories:

• The English name of the shared lock is Shared Locks, abbreviated as S lock.

If a transaction has a shared lock on a record, if another transaction also wants to acquire the lock, it is also possible, which means that two transactions can hold the lock on the record at the same time.

• Exclusive locks are also often called exclusive locks, English name: Exclusive Locks, referred to as S locks,

If transaction T1 has acquired the S lock of the record, and transaction T2 wants to acquire the X lock of another record, then this operation will be blocked until T1 releases the S lock.

MySQL provides two syntaxes to allow us to obtain the X lock and S lock of the record when reading the record:

 SELECT ... LOCK IN SHARE MODE; 
在读取记录的时候获取该记录的共享锁
SELECT ... FOR UPDATE;
在该事务中获取该记录的X锁

If the current transaction executes a statement that acquires a shared lock (SELECT ... LOCK IN SHARE MODE), it will add S locks to the records it reads, allowing other transactions to continue to acquire S locks for these records (if other transactions also use SELECT ... LOCK IN SHARE MODE; to read these records), but cannot acquire X locks on these records (say use SELECT ... FOR UPDATE to read these records, or modify these records) if other Transactions want to acquire the X locks of these records, then they will block until the S locks of these records are released after the current transaction commits.

If SELECT ... FOR UPDATE is executed in the transaction, it will add X lock to the read record, and other transactions cannot obtain the S lock and X lock of this record. If the transaction wants to obtain the S lock of these records and X locks, they will block until the X locks on these records are released after the current transaction commits.

Table shared locks and exclusive locks

What we mentioned above can be considered as row locks, that is, locking for several rows. The granularity of this lock is relatively fine. In fact, a transaction can also be locked at the table level, which is naturally called a table-level lock or a table lock. The table lock affects the records in the entire table, and the locks added to the table can also be divided into shared locks (S locks) and exclusive locks (X locks):

If a transaction adds an S lock to a table:

• Other transactions can continue to acquire the S lock on the table
• Other transactions can continue to acquire S locks on records in this table
• No other transaction can acquire the X lock on the table
• If there is an S lock on the row record, other transactions cannot obtain the X lock on the row record

If a transaction adds X lock to the table (exclusively this table):

• Other transactions cannot obtain the S lock on the table
• If there is an X lock on a record in that table, other transactions cannot acquire an S lock on that record.
• If there is an S lock on a record in that table, other transactions cannot acquire an X lock on that record
• Other transactions cannot continue to acquire X locks on some records in this table.

But there are actually two problems. If MySQl wants to put S lock on the table as a whole, it first needs to ensure that the records in the table cannot have X locks, and traverse the records of the table? The developers of MySQL designed intent locks:

• Intention Shared Lock Intention Shared Lock, referred to as IS lock. When a transaction is going to add an S lock to a record, it needs to add an IS lock to the table first.
• Intention Exclusive Lock, Intention Exclusive Lock, referred to as IX lock. When a transaction is going to add an X lock on a record, it needs to add an IX lock at the table level first.

This avoids a full table scan.

Overview of table locks

When executing some DDL statements in ALTER TABLE and DROP TABLE on a table, other transactions will block the concurrent execution of additions, deletions, and changes to the table. Similarly, a transaction executes SELECT, INSERT, DELETE, and UPDATE on a table. statement, the execution of DDL statements in other sessions will also block. This process is actually implemented by using a metadata lock (Metadata Locks, MDL for short) at the Server layer. Generally, the table-level S lock and X lock provided by the InnoDB storage engine are not used. Therefore, the watch-level S and X locks are quite tasteless. We focus on row locks. The intent locks we talked about above are also table locks. Then there is the self-increment attribute of MySQL. There are two ways to achieve self-increment in MySQL:

• Use AUTO-INC lock, that is, add an AUTO-INC lock to the table when executing the insert statement, and then assign each self-increment attribute. A transaction holds an AUTO-INC lock, and other insert transactions are blocked. Released after the insert statement is executed.

​ An AUTO-INC lock can be taken if the number of records inserted is uncertain.

• The lightweight lock is released after the value of the auto-increment attribute column is generated for the insert statement, and the lock does not need to be released until the execution of the entire insert statement is completed.

  If the number of records to be inserted can be determined before the statement is executed, a lightweight lock is generally used to assign values ​​to auto-increment columns.

So how to choose the auto-increment mode? There is a system variable innodb_autoinc_lock_mode in InnoDB to control which two ways to assign values ​​to the auto-increment column. When it is 0, the AUTO-INC lock is always used. When it is 2, the lightweight lock is always used Locks (may cause the auto-increment columns of insert statements in different transactions to be crossed, which is unsafe in the scenario of master-slave replication). When it is 1, when the number of inserted records is determined, a lightweight lock is adopted, and an AUTO-INC lock is adopted when it is uncertain.

Summarize

The concept of MySQL locks is more complicated than I originally thought, and there is no limit to knowledge, and there is no limit to life. I originally planned to go through the locks in MySQL in this section, but there is still no main line to connect these contents together. . In the end, only the basic concepts of row locks and table locks are introduced.

References

• "How MySQL Works: Understanding MySQL from the Root" Nuggets Booklet