主页 / user-229628

Jignesh M. Khatri's questions

Martin Hope
Jignesh M. Khatri
Asked: 2022-08-08 03:27:00 +0800 CST
  • 1

我只是在查看有关 MySQL 表锁定系统的文章。我想了解如何在 UPDATE/DELETE 操作时避免 MySQL 中的死锁。

到目前为止我做了什么：

设置

CREATE TABLE `new_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx` (`a`)
);

START TRANSACTION;
truncate new_table;
insert into new_table(a) values(2),(3),(4),(5),(10),(11),(15),(19),(20),(25),(27),(35);
COMMIT;
  • 试验一：

步骤1：

* SESSION 1:
    START TRANSACTION;
    delete from new_table where a in (2, 3, 4);
    insert into new_table (a) values (55);
    // Both the queries will be executed. (NOT COMMITTED YET)

* SESSION 2:
    START TRANSACTION;
    delete from new_table where a in (5, 10);insert into new_table (a) values(105);
    // waiting for lock

* SESSION 3:
    START TRANSACTION;
    insert into new_table (a) values(7); delete from new_table where a in (11, 15);
    // waiting for lock

第2步：

* SESSION 1:
    COMMIT;

* SESSION 2:
    ** DEADLOCK HERE **

* SESSION 3:
    // Both the queries will be executed. (NOT COMMITTED YET)

您会看到SESSION 2中存在死锁。我的想法是，这是由于 MySQL 中的 GAP 锁定所致（我可能错了，如果我在这里错了，请纠正我）。

为了避免 Gap 锁定，我将 GLOBAL Transaction 隔离更改为 READ COMMITTED（默认为 REPEATABLE READ）。但仍然是同样的问题。

  1. 试验二：

步骤1：

* SESSION 1:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    START TRANSACTION;
    delete from new_table where a in (2, 3, 4);
    insert into new_table (a) values (55);
    // Both the queries will be executed. (NOT COMMITTED YET)

* SESSION 2:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    START TRANSACTION;
    delete from new_table where a in (5, 10);insert into new_table (a) values(105);

* SESSION 3:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    START TRANSACTION;
    insert into new_table (a) values(7); delete from new_table where a in (11, 15);

第2步：

* SESSION 1:
    COMMIT;

* SESSION 2:
    // Both the queries will be executed. (NOT COMMITTED YET)

* SESSION 3:
    ** DEADLOCK HERE **

注意变化，死锁转移到SESSION 3。如您所见，我没有尝试删除重叠的行。但无论如何，仍然存在僵局。

当我尝试使用PRIMARY KEYinWHERE子句执行上述删除查询时，一切正常。

谁能解释一下这里发生了什么？我们如何处理这种情况并避免 MySQL 中的死锁？

编辑：添加结果EXPLAIN ANALYZE

  • 询问：EXPLAIN ANALYZE SELECT * from new_table where a in (2, 3, 4);

** 带索引列

-> Filter: (new_table.a in (2,3,4))  (cost=1.45 rows=4) (actual time=0.059..0.069 rows=3 loops=1)
    -> Covering index scan on new_table using idx  (cost=1.45 rows=12) (actual time=0.048..0.060 rows=12 loops=1)

** 从列中删除索引后

-> Filter: (new_table.a in (2,3,4))  (cost=1.45 rows=4) (actual time=0.034..0.043 rows=3 loops=1)
    -> Table scan on new_table  (cost=1.45 rows=12) (actual time=0.031..0.037 rows=12 loops=1)

** 带PK

  • 询问：EXPLAIN ANALYZE SELECT * from new_table where id in (2, 3, 4);
-> Filter: (new_table.id in (2,3,4))  (cost=1.36 rows=3) (actual time=0.040..0.050 rows=3 loops=1)
    -> Index range scan on new_table using PRIMARY over (id = 2) OR (id = 3) OR (id = 4)  (cost=1.36 rows=3) (actual time=0.038..0.047 rows=3 loops=1)
mysql innodb