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

This article introduces how to open and close the gap lock in MySQL, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

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.

When we retrieve data using range conditions instead of equality conditions, and request shared or exclusive locks, InnoDB locks the index entries of existing data records that meet the criteria; for records whose key values are within the range of conditions but do not exist

It is called "GAP", and InnoDB will also lock this "gap". This locking mechanism is called gap lock.

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.

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

Check to see if the gap lock is turned on:

Mysql > show variables like 'innodb_locks_unsafe_for_binlog'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_locks_unsafe_for_binlog | OFF |

+-+ +

Innodb_locks_unsafe_for_binlog: the default value is 0, which means gap lock is enabled.

The main function of this parameter is to control whether innodb locks gap.

However, this setting change does not affect the need for foreign keys and unique indexes (including primary keys) to lock gap.

The REPEATABLE-READ transaction isolation level that turns on innodb_locks_unsafe_for_binlog has been transformed into READ-COMMITTED to a large extent.

Turn off the gap lock (gap lock) method:

Add [mysqld] in my.cnf

[mysqld]

Innodb_locks_unsafe_for_binlog = 1

It takes effect after restarting MySQL.

On how to open and close the gap lock in MySQL is shared here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.