Shulou(Shulou.com)05/31 Report--

Today, the editor will share with you the relevant knowledge about what the row-level lock in the InnoDB of MySQL knowledge points is. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

A row lock, also known as a record lock, is, as its name implies, a lock added to a record. Note, however, that this record refers to by locking the index entry on the index. The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks.

Whether using a primary key index, a unique index, or a normal index, InnoDB uses row locks to lock the data.

Row locks can be used only if indexes are actually used in the execution plan: even if index fields are used in the condition, whether or not to use indexes to retrieve data is determined by MySQL by determining the cost of different execution plans. If MySQL thinks full table scans are more efficient, such as for very small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks.

At the same time, when we retrieve data with a range condition instead of an equal condition and request a lock, InnoDB locks the index entries of the existing data records that meet the criteria.

But even row locks are divided into various types in InnoDB. In other words, even if a row lock is added to the same record, the effect will be different if the type is different.

Here we still use the previous teacher table, add an index, and insert several records.

Mysql > desc teacher +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | number | int (11) | NO | PRI | NULL | name | varchar | YES | MUL | NULL | | domain | varchar | YES | | NULL | | +-+- -+ 3 rows in set (0.00 sec) mysql > select * from teacher +-+ | number | name | domain | +-+ | 1 | T | Java | 3 | M | Redis | | 9 | X | MQ | | 15 | O | Python | | 21 | A | Golang | +-+- +-+ 5 rows in set (0.00 sec)

Let's take a look at what common types of row locks are available.

Record Locks

Also known as record lock, is to lock only one record, the official type name is: LOCK_REC_NOT_GAP. For example, the schematic diagram of adding a record lock to the record with the number 9 is as follows:

Record locks can be divided into S-locks and X-locks. When a transaction acquires an S-type record lock of a record, other transactions can continue to acquire the S-type record lock of the record, but can not continue to acquire the X-type record lock; when a transaction acquires an X-type record lock of a record, other transactions can neither continue to acquire the S-type record lock of the record, nor continue to acquire the X-type record lock.

T1T2begin

Select * from teacher where number=9 for update

Select * from teacher where number=9 for update; # blocking Gap Locks

MySQL can partially solve the phantom reading problem under the REPEATABLE READ isolation level. there are two solutions, which can be solved by using MVCC or locking. However, there is a problem when using the locking scheme, that is, when the transaction performs a read operation for the first time, those phantom records do not yet exist, and we cannot add a record lock to these phantom records. InnoDB proposed a lock called Gap Locks, the official type name is: LOCK_GAP, we can also call it gap lock for short.

Gap lock is essentially a lock on the gap before and after the index, not on the index itself.

T1T2begin

Update teacher set domain='Redis' where name='M'

Insert into teacher value (23); # blocking

Insert into teacher value (23); # blocking

Transaction T1 puts a gap lock on ([A, 21], [M, 3]) and ([M, 3], [O, 15]), as shown in the following figure:

It means that no other transaction is allowed to insert a new record between before and after this record, so T2 cannot be inserted.

But when the SQL statement becomes:

Insert into teacher value (70 pencils, packs, dockers)

Can you plug it in? Of course I can, because this record is not in the locked range.

Thinking questions

Now there is a table, and the records are as follows:

List = ['su liang','hacker','ice'] list.insert (1Magnetique Kiko') print (list) # result: ['su liang',' kiko', 'hacker','ice']

Start a transaction:

Begin;SELECT * FROM test1 WHERE number = 3 FOR UPDATE

Start another transaction:

INSERT INTO test1 (id, number) VALUES (2,1); # blocking INSERT INTO test1 (id, number) VALUES (3,2); # blocking INSERT INTO test1 (id, number) VALUES (6,8); # blocking INSERT INTO test1 (id, number) VALUES (8,8); # normal execution of INSERT INTO test1 (id, number) VALUES (9,9); # normal execution of INSERT INTO test1 (id, number) VALUES (10,12) # normal execution UPDATE test1 SET number = 5 WHERE id = 11 AND number = 12; # blocking

Why can't it be executed (6jol 8), but can it be executed (8jol 8)? The scope of this gap lock should be [1line 8]. Why can't the last statement be executed?

Solution: draw a diagram of number index data storage, and then add the lock according to the lock mode of the gap lock, and you can quickly understand the answer.

When the inserted number is blocked in the interval (1m 8).

When the inserted number is equal to 1, 8, then the id will be blocked in the interval (1, 4) and [6, 7).

Next-Key Locks

Sometimes we want to lock a record and prevent other transactions from inserting a new record in the gap in front of the record, so InnoDB proposes a lock called Next-Key Locks, the official type name is: LOCK_ORDINARY, we can also call it next-key lock for short. The essence of next-key lock is

A combination of a record lock and a gap lock.

By default, InnoDB runs at the REPEATABLE READ isolation level. In this case, InnoDB uses Next-Key Locks locks for search and index scanning, which prevents phantom reads from happening.

Insert Intention Locks

We say that when a transaction inserts a record, it needs to determine whether the insertion position has been added by another transaction to the so-called gap lock (the next-key lock also contains the gap lock, which is not emphasized later), and if so, the insert operation needs to wait until the transaction that owns the gap lock commits.

But InnoDB stipulates that a transaction also needs to generate a lock structure in memory while waiting, indicating that a transaction wants to insert a new record in a gap, but is now in a waiting state. This type of lock is named Insert Intention Locks, and the official type name is LOCK_INSERT_INTENTION, which we can also call an insert intention lock.

It can be understood that inserting an intention lock is a kind of waiting queue of locks, so that the transactions waiting for the lock are queued in memory. When the transaction holding the lock is completed, the transaction in the waiting state can obtain the lock to continue the transaction.

Implicit lock

The maintenance of locks requires cost. In order to save resources, MySQL put forward a concept of implicit locks in the design. In general, INSERT operations are unlocked, of course, in the case of real concurrency conflicts, it will still cause problems.

So in MySQL, a transaction can lock the newly inserted record unexplicitly, but when other transactions add S lock or X lock to this record, they will check the trx_id hidden column in the index record, and then make various judgments, which will first help the current transaction generate a lock structure, and then regenerate itself into a lock structure and enter the waiting state. However, due to the existence of the transaction id, it is equivalent to adding an implicit lock.

In this way, implicit locks serve the purpose of delaying lock generation. This process, we can not intervene, is automatically handled by the engine, is completely transparent to us, we just need to know.

Memory structure of lock

The so-called lock is actually a structure in memory, which is not locked before the transaction is executed, that is to say, there is no lock structure associated with the record at the beginning. When a transaction wants to make changes to this record, it will first check whether there is a lock structure associated with this record in memory, and when it does not, it will generate a lock structure associated with it in memory. For example, if transaction T1 makes changes to the record, it needs to generate a lock structure associated with it.

There must be at least two more important attributes in the lock structure:

Trx message: represents which transaction generated the lock structure.

Is_waiting: indicates whether the current transaction is waiting.

When transaction T1 changes a record, a lock structure is generated associated with the record. Because no other transaction has locked the record before, the is_waiting attribute is false. We call this scenario successful lock acquisition, or lock successful, and then you can continue with the operation.

Before transaction T1 commits, another transaction T2 also wants to make changes to the record, so first check to see if there is a lock structure associated with the record, and then generate a lock structure associated with the record. However, the is_waiting attribute value of the lock structure is true, indicating that the current transaction needs to wait. We call this scenario lock acquisition failure or lock failure. Or failed to acquire the lock.

After transaction T1 commits, the lock structure generated by the transaction is released, and then see if there are any other transactions waiting to acquire the lock. It is found that transaction T2 is still waiting to acquire the lock, so set the is_waiting property of the lock structure corresponding to transaction T2 to false, and then wake up the corresponding thread of the transaction to continue execution, and transaction T2 acquires the lock. This implementation is very similar to the wait queue for AQS in concurrent programming.

The essence of locking a record is to create a lock structure associated with it in memory. So, when a transaction locks multiple records, do you want to create multiple lock structures? For example:

SELECT * FROM teacher LOCK IN SHARE MODE

Obviously, this statement needs to lock all records in the teacher table. So, do you need to generate a lock structure for each record? In fact, in theory, there is no problem with creating multiple lock structures, but it is easier to understand. But if a transaction wants to acquire 10000 records of locks, it is not cost-effective to generate 10000 such structures, both in terms of execution efficiency and space efficiency, so in fact, it is not a record-by-record lock structure.

Of course, the lock structure is actually very complex, so we have a rough idea of what elements are contained in it.

Transaction information of the lock: whether it is a table-level lock or a row-level lock, a lock belongs to a transaction, and the transaction information corresponding to the lock is recorded here.

Index information: for row-level locks, you need to record which index the locked record belongs to.

Table lock / row lock information: the contents of the table-level lock structure and the row-level lock structure are different in this location. Specifically, the table-level lock records which table is locked, as well as some other information, while the row-level lock records the table space where the record is located, the page number of the record, and the data structure that distinguishes which record is locked.

Lock mode: which of the IS,IX,S,X is the lock?

Lock type: table lock or row lock, the specific type of row lock.

Other: some data structures related to lock management, such as hash tables and linked lists.

Basically, in the same transaction, the same data page, the same lock type of lock will be saved together.

These are all the contents of the article "what is the row-level lock in the InnoDB of MySQL knowledge points". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.