主页 / dba / 问题 / 315321
Accepted
Jignesh M. Khatri
Asked: 2022-08-08 03:27:00 +0800 CST

了解 MySQL 锁和死锁

  • 772

我只是在查看有关 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

1 个回答

  1. Best Answer

    将 aVARCHAR与数字文字进行比较是我有时在SELECT ... WHEREs. 你似乎正在经历同样的事情UPDATE...WHEREs。

    varchar 在测试之前被转换为数字。这往往会导致全表（或索引）扫描。在 的情况下UPDATEs，这往往会导致锁定表中的所有行。

    引用文字也可以解决问题。

    WHERE vc = 2   -- bad
WHERE int = 2   -- good
WHERE vc = "2"   -- good
WHERE int = "2"   -- good (the literal is interpreted as a number)

    修复数据类型（如您所述）以解决问题。还是还有案例？

    功能和可搜索性

    • WHERE column = function(constant) 也许可以使用索引column
    • WHERE function(column) = anything不能在 上使用索引column。见sargable
    • WHERE column + INTERVAL 7 DAY > NOW() 不能使用索引；在+这种情况下，本质上是一个“功能”
    • WHERE FROM_UNIXTIME(column) > CURDATE() - INTERVAL 3 MONTH 由于 [builtin] 函数，无法使用索引FROM_UNIXTIME。右边是一个“常量表达式”，在执行查询之前被求值；因此不是问题。
    • 1

相关问题