On the type of read-write lock and lock mode of InnoDB

(in order to facilitate, the English keywords are used in lowercase, the relevant knowledge points will be introduced briefly, strive to be able to read independently)

At the beginning of the article I will first introduce the following knowledge points required in this article:

• Knowledge of InnoDB clustered index (clustered index) and non-clustered index (two-level index, nonclustered index)
• Isolation level for INNODB (isolation levels)
• Simple SQL Knowledge (can read SQL statements)
• MVCC (multi-version Concurrent Control) multi-version concurrency controls
• Dirty reading of data, Phantom reading (if there is time to detail the dirty read if there is no time, the internet to tell a lot of this place)

Question 1: There are several modes of reading, locking there are several ways we first look at a MySQL table and a few statements table name: My_table search engine: INNODB table structure:

1. Select * from my_table where id = 1;

2. Select * from my_table WHERE id = 1 lock in share mode;

3. Select * from my_table where id = 1 for update;

4. Update my_table Set address = ' Tianjin ' where id = 1;

First of all, the isolation level, the MySQL isolation level is divided into four kinds: READ UNCOMMITTED, Read Committed, repeat read (repeatable read), Serialization (serializable) where MySQL defaults to the isolation level for re-repetition (repeatable read), hereinafter referred to as RR, this article also only describes the mode of reading this pattern into two types:

• Snapshot read (snapshot read)
• Current Read

Let's start by knowing that MVCC:MVCC is a protocol designed to implement concurrency control for a database. The relative thing LBCC is the lock-based concurrency control (lock-based Concurrent). The simplest way to implement concurrency access control for a database is to locking access, which is not written at read time (this is read as the current read and is described later.) Allows multiple threads to lock the content they want to read at the same time, that is, a shared lock or a s lock, which cannot be read at the time of writing (only one thread can write to the same content, that is, exclusive lock, X Lock). Such locking access, in fact, is not really a concurrency, or it can only achieve concurrent reading, both read and write serialization, which greatly reduces the database read and write performance. LBCC is the highest level of serialize isolation in four isolation levels. MVCC contrast LBCC Its greatest advantage is that read without lock, read and write do not conflict. In MVCC, the read operation can be divided into two categories, snapshot read (Snapshot read) and current read. Snapshot reads, reads the visible version of the record (possibly a historical version, that the latest data may be being modified concurrently by the currently executing transaction), does not lock the returned record, such as SQL statement 1 above, and the current read, reads the latest version of the record, and locks the returned record. Ensure that other transactions do not modify this record concurrently. such as the SQL statement above 2,3,4. The difference is that the 2 plus is the S lock, 3, 4 plus the x lock, insert Plus is the X lock. Note: MVCC only works under RC and RR two isolation levels, and the other two isolation levels are incompatible with MVCC: (no intent locks are involved) look at an SQL statement first

Update my_table Set name = ' Zhang ' where id = 1;

Suppose the ID is the primary key: This SQL execution will add X locks to this row of data, as

MySQL's InnoDB default isolation mode is RR mode, both repeatable read, InnoDB RR isolation level to ensure that the read to the record locking (record lock), while ensuring that the read range is locked, new records satisfying the query condition can not be inserted (GAP Lock), so there is no phantom read phenomenon. However, a standard RR can only guarantee that the result of reading the same record more than once in the same transaction is consistent, and cannot resolve the problem of Phantom reads (which are not guaranteed to occur in the transaction). InnoDB's Magic reading solution is based on the MVCC implementation mechanism. Other models have time to add to this and not explain the other modes. Here, because the ID is the primary key, there is a clustered index in InnoDB, and the other indexes are level two indexes, here is a brief introduction to clustered index: in the InnoDB storage engine, the existence of the primary key is very important, in time you do not set the primary key for the table, the storage engine will also implicitly define a primary key, Just transparent to the user. It's important because the storage of clustered indexes is stored with the data, and the clustered index data is the order in which the data is stored. If the data you need to find is contiguous, the data location found by the clustered index is also contiguous, just read sequentially. For a clustered index, the leaf node stores the actual data rows, no more separate data pages (there is a difference from the two-level index in the back, the two-index leaf nodes point to the data page data rows of the logical pointer, you need to follow the pointer to retrieve the data). You can create up to one clustered index on a table, because the physical order of real data can only be one. Secondary indexes: The table data storage order is independent of the index order. For nonclustered indexes, the leaf node contains the indexed field values and logical pointers to data rows of the page, with the same number of rows as the data table row data. Look at the SQL statement above, or look at the previous SQL, this statement will be executed with an X lock, this time if the other transactions in the statement is also in the operation of the lock (update, insert or delete, And the S-lock of the current read operation of the 2 statement) will cause lock contention (InnoDB when lock contention is handled in the event that the rollback timeout acquires a transaction that is not locked). Let's start with the situation 1:id the primary key in the case of the interaction of two of the above four statements.

1. Select * from my_table where id = 1;

2. Select * from my_table WHERE id = 1 lock in share mode;

As we said above, statement 1 is a snapshot read and has no effect on other reads or writes. So when these two statements are parallel, 1 reads the snapshot, and 2 is the statement plus S lock.

SELECT * from my_table WHERE id = 1 lock in share mode;

Case 2:id PRIMARY Key

2. Select * from my_table WHERE id = 1 lock in share mode;

3. Select * from my_table where id = 1 for update;

Where the statement 2 plus s lock, 3 plus x lock (when the data is added S lock, the other to this record want to read this record also need to add S lock, this is why S lock is a shared lock. At this point, it is not allowed to add X lock to this record) two kinds of locks can not exist in one record at the same time. Therefore, two statements cannot be executed concurrently. Case 3:id PRIMARY Key

3. Select * from my_table where id = 1 for update;

4. Update my_table Set address = ' Tianjin ' where id = 1;

In this case, all two statements need to be X-locked for the data, so it is obviously not possible to execute concurrently. Let's talk about the ID is not the primary key case ID if it is not a primary key, you cannot use the clustered index, and there are several situations in InnoDB

• Second-level unique index
• Secondary non-unique index
• No index

As long as it is not a snapshot read is bound to lock, we have learned the form of locks, it is not difficult to understand whether the first plus x lock or S lock which, must not add another lock, so we only analyze the way to lock the case 4: The Assumption ID is a level two unique index (unique)

4. Update my_table Set address = ' Tianjin ' where id = 1;

There is a clear need to add an X lock, but here the locking and ID primary key (indexed as clustered index) lock is not exactly the same, it will be slightly more complicated. This time we need to have a certain understanding of the index knowledge, it said that the leaf node in the level two index stored in addition to the index information and the actual data of the logical pointer, that is, we need to now find a level two unique index in the logical pointer to this record, And then through the pointer to find the actual data storage location and to lock the data. Note that the lock here should be added on the index and on the data itself (or clustered index), because the two are stored in a structure, whereas the logical pointer in the leaf of Level two index in InnoDB is not the physical address of the data store, but the primary key value, not just the two-level unique index. If you want to use good InnoDB, his index structure is necessary to learn, if later time will be described in detail. Case 5:age is a level two non-unique index, ID is the primary key

5. Update my_table Set address = ' Tianjin ' where age = 25;

This situation is more special than the previous one, because cases 3 and 4 can only find one record, and only the record needs to be locked, so the result set will not be changed. But if age is a two-level non-unique index, we see two records in the table below age=25 if we have a user who inserts an age of 25 data in the update process, then there is a phenomenon that you have updated all the age=25 data, But there is an illusion that the data is not updated, which is the Phantom reading (you can find the data yourself to avoid this article too long). This time obviously only to find out the data lock is not solve the problem. So there's the gap lock (the gap lock is literally a better understanding) here's a drawing to understand: there are two in age 25, with IDs 1 and 3, respectively. When we modify the execution of the above statement, if there is no gap lock, a situation may occur: Another transaction executes the following statement

Update my_table set age=25 where id=2;

The Phantom read occurs. Gap locks prevent the insertion of a record that satisfies a condition during the execution of a statement or transaction to create a phantom read. So in this case, in addition to the conditions of the level two index and data (or clustered index) plus x lock also to the relevant gap lock. It can be understood that this gap lock prevents new entries from being added to the two-level index because the level two index itself is also ordered.

Condition 6:age No index, ID primary key

5. Update my_table Set address = ' Tianjin ' where age = 25;

In this case, all records are added with an X lock, and the gap (GAP) between each record is also added to the gap lock.  In reality, SQL may be more complex, and if other column indexes are used, MySQL will filter the results by index order, and may not be full-table plus X-Lock and Gap lock. Note: Turn from your own blog

On the type of read-write lock and lock mode of InnoDB