删除查询MySQL中的锁定问题5.7.18

Question

在MySQL 5.7.18中的删除查询中面临锁定问题。 MySQL版本-5.7.18 隔离级别 - 读取

Table structure:
mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` bigint(19) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 10 | a    |
+----+------+
1 row in set (0.00 sec)

问题：

事务1：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> explain delete from test where id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | DELETE      | test  | NULL       | range | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> delete from test where id=10;
Query OK, 1 row affected (0.00 sec)

事务2：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> explain delete from test where id>1 and id<9;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | DELETE      | test  | NULL       | range | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> delete from test where id>1 and id<9;

查询正在等待锁定

更多详细信息：

mysql> select * from INFORMATION_SCHEMA.INNODB_TRX;
+---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id  | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                            | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 6643991 | LOCK WAIT | 2022-05-09 19:00:57 | 6643991:2140:3:2      | 2022-05-09 19:01:51 |          2 |                   3 | delete from test where id>1 and id<9 | starting index read |                 1 |                 1 |                2 |                  1136 |               2 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 6643990 | RUNNING   | 2022-05-09 19:00:20 | NULL                  | NULL                |          3 |                   4 | NULL                                 | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 1 |                       0 | READ COMMITTED      |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+---------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.01 sec)

mysql> select * from INFORMATION_SCHEMA.INNODB_LOCKS;
+------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table         | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| 6643991:2140:3:2 | 6643991     | X         | RECORD    | `testSchem`.`test` | PRIMARY    |       2140 |         3 |        2 | 10        |
| 6643990:2140:3:2 | 6643990     | X         | RECORD    | `testSchem`.`test` | PRIMARY    |       2140 |         3 |        2 | 10        |
+------------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 6643991           | 6643991:2140:3:2  | 6643990         | 6643990:2140:3:2 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.processlist;
+----+------+-----------+-----------+---------+------+-----------+----------------------------------------------+
| ID | USER | HOST      | DB        | COMMAND | TIME | STATE     | INFO                                         |
+----+------+-----------+-----------+---------+------+-----------+----------------------------------------------+
|  3 | root | localhost | testSchem | Query   |    2 | updating  | delete from test where id>1 and id<9         |
|  4 | root | localhost | testSchem | Sleep   |   93 |           | NULL                                         |
|  6 | root | localhost | NULL      | Query   |    0 | executing | select * from INFORMATION_SCHEMA.processlist |
+----+------+-----------+-----------+---------+------+-----------+----------------------------------------------+
3 rows in set (0.01 sec)

mysql> show engine innodb status;
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      | 
=====================================
2022-05-09 19:01:53 0x30af81000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 36 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 10 srv_active, 0 srv_shutdown, 100253 srv_idle
srv_master_thread log flush and writes: 100261
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 32
OS WAIT ARRAY INFO: signal count 29
RW-shared spins 0, rounds 28, OS waits 12
RW-excl spins 0, rounds 51, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 28.00 RW-shared, 51.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 6643992
Purge done for trx's n:o < 6643990 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421793707003456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6643991, ACTIVE 56 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 13068374016, query id 51 localhost root updating
delete from test where id>1 and id<9
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2140 page no 3 n bits 72 index PRIMARY of table `testSchem`.`test` trx id 6643991 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 6; hex 000000656116; asc    ea ;;
 2: len 7; hex 30000003ef0e09; asc 0      ;;
 3: len 1; hex 61; asc a;;

------------------
---TRANSACTION 6643990, ACTIVE 93 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 13068652544, query id 47 localhost root
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
494 OS file reads, 184 OS file writes, 81 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 9, seg size 11, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
0.00 hash searches/s, 0.08 non-hash searches/s
---
LOG
---
Log sequence number 6401339089
Log flushed up to   6401339089
Pages flushed up to 6401339089
Last checkpoint at  6401339080
0 pending log flushes, 0 pending chkp writes
54 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 303269
Buffer pool size   8192
Free buffers       7650
Database pages     534
Old database pages 213
Modified db pages  3
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 468, created 66, written 113
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 534, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=363, Main thread ID=13062987776, state: sleeping
Number of rows inserted 2392, updated 0, deleted 3, read 2402
0.08 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.08 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

事务3：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> explain delete from test where id>10 and id<19;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | DELETE      | test  | NULL       | range | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> delete from test where id>11 and id<19;
Query OK, 0 rows affected (0.00 sec)

查询成功执行。

谁能解释为什么交易2在这里等待锁？

值得注意的观点： 交易1锁一行。删除值少于锁定行的查询仅在等待锁定。

Answer 1

这是间隙锁定。

一开始只插入ID 10，这很奇怪。
删除时，间隙锁定基本上是锁定头部和尾部。
它必须与您正在使用的ID 10冲突。由于没有第10列的列，因此MySQL只是锁定了本地位置的差距。
（这就是为什么您的第三笔交易工作的原因。）

尝试将ID 10更改为1；
并使用：

First Transaction T1:

BEGIN;

DELETE FROM test WHERE id=1;


Second Transaction T2:

BEGIN;
DELETE FROM test WHERE id > 1 AND id<10;

我敢打赌它有效。让我知道你的想法。

* 编辑

在我在答案中经过评论时，我在答案中回答。

在我的用例和试验中，我发现这与删除查询和插入查询相矛盾。

请考虑以下两项交易：

First Transaction T1:

BEGIN;

SELECT * FROM `test` t WHERE t.`id`>1 AND t.`id`<10 FOR UPDATE;

Second Transaction T2:

BEGIN;

INSERT INTO test VALUES(5,'asgar');

首先尝试使用交易隔离级别：READ_COMMENT;

交易不会留在gaap lock

中

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

第二笔 第二笔交易停留在GAP锁定中。

这些是在插入语句的情况下。但是，对于删除语句，隔离级别似乎不会影响。