How to use mysql gap lock

Explanation

1. When we use range conditions to retrieve data and request shared or exclusive locks, InnoDB will lock the index items of existing data records that meet the conditions; Records whose key values ​​are within the condition range but do not exist are called gaps.

2. InnoDB will also lock this gap. This lock mechanism is the so-called gap lock.

Note

If the execution condition is that the range is too large, InnoDB will lock all index key values ​​in the entire range, which can easily affect performance.

Example

Transaction-A
mysql> update innodb_lock set k=66 where id >=6;
Query OK, 1 row affected (0.63 sec)
mysql> commit;
 
Transaction-B
mysql> insert into innodb_lock (id,k,v) values(7,'7','7000');
Query OK, 1 row affected (18.99 sec)

The above is the detailed content of How to use mysql gap lock. For more information, please follow other related articles on the PHP Chinese website!