Shulou(Shulou.com)06/01 Report--

MySQL InnoDB supports three row locking methods:

Record Lock: the lock is added directly to the index record.

Gap Lock: the lock is applied to free space that does not exist, either between two index records, or before the first index record or after the last index.

Next-Key Lock: the combination of row lock and gap lock is called Next-Key Lock.

By default, InnoDB works at the repeatable read isolation level and locks data rows in a Next-Key Lock manner, which effectively prevents phantom reading. Next-Key Lock is a combination of row lock and gap lock, so that when InnoDB scans an index record, it will first add a row lock (Record Lock) to the selected index record, and then add a gap lock (Gap Lock) on both sides of the index record (scan to the left to the first value smaller than the given parameter, to the right to the first value larger than the given parameter, and then use this as a boundary to build an interval). If a gap is locked by transaction T1, other transactions cannot insert records in that gap.

For example:

Table task_queue

Id taskId

1 2

3 9

10 20

40 41

Start a session: session 1

Sql > set autocommit=0

# #

Cancel automatic submission

Sql > delete from task_queue where taskId = 20

Sql > insert into task_queue values (20,20)

Opening a session: session 2

Sql > set autocommit=0

# #

Cancel automatic submission

Sql > delete from task_queue where taskId = 25

Sql > insert into task_queue values (30,25)

In the case of no concurrency or very little concurrency, it is possible to execute normally. In Mysql, transactions are ultimately executed through rows, but in the case of high concurrency, the order of execution is very likely to change to look like this:

Sql > delete from task_queue where taskId = 20

Sql > delete from task_queue where taskId = 25

Sql > insert into task_queue values (20,20)

Sql > insert into task_queue values (30,25)

At this point, the last statement: insert into task_queue values (30,25); a deadlock error will occur during execution. Because 20-41 is locked when the record taskId = 20 is deleted, and they all acquire the shared lock of this data segment, a deadlock occurs when acquiring the exclusive lock of this data segment.

The only function of gap lock in InnoDB is to prevent the insertion of other transactions, so as to prevent the occurrence of phantom reading, so gap lock makes no difference between shared lock and exclusive lock. In addition, in the above example, we chose a normal (non-unique) index field to test, which is not random, because if InnoDB scans a primary key or a unique index, then InnoDB will only use row locks to add locks, not Next-Key Lock, that is, it will not lock the gaps between indexes, which is not difficult to understand. You can also test it yourself.

To disable gap locks, you can lower the isolation level to read submitted, or turn on the parameter innodb_locks_unsafe_for_binlog.

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.