时间戳

首页 > 解决方案 > MySQL排他锁

mysql - MySQL排他锁

问题描述

我们有下表：

mysql> desc journeys ;
+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| journey_id    | char(36)   | NO   | PRI | NULL    |       |
| is_completed  | tinyint(1) | NO   |     | 0       |       |
| user_id       | char(36)   | NO   |     | NULL    |       |
| created_at    | datetime   | NO   |     | NULL    |       |
| updated_at    | datetime   | NO   |     | NULL    |       |
| pack_id       | char(36)   | YES  | MUL | NULL    |       |
| family_id     | char(36)   | YES  | MUL | NULL    |       |
+---------------+------------+------+-----+---------+-------+

mysql> desc packs ;
+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| pack_id       | char(36)   | NO   | PRI | NULL    |       |
| is_published  | tinyint(1) | NO   |     | 0       |       |
| order         | int(11)    | NO   |     | NULL    |       |
| created_at    | datetime   | NO   |     | NULL    |       |
| updated_at    | datetime   | NO   |     | NULL    |       |
| family_id     | char(36)   | NO   | MUL | NULL    |       |
+---------------+------------+------+-----+---------+-------+

隔离级别为 REPEATABLE_READ。

根据这里的词汇表： https ://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_exclusive_lock

排他锁是一种防止任何其他事务锁定同一行的锁。根据事务隔离级别，这种锁可能会阻止其他事务写入同一行，或者也可能阻止其他事务读取同一行。

我们的逻辑如下所示（user_id采用不同的值）：

START TRANSACTION;
SELECT * FROM journeys WHERE user_id = <user_id> FOR UPDATE ;
# COMMIT;

下面有一点测试。我打开一个终端窗口（终端 #1）并执行以下语句：

START TRANSACTION;
SELECT * FROM journeys WHERE user_id = user_id_1 FOR UPDATE ;

然后我打开第二个终端（终端#2）窗口并执行以下语句：

START TRANSACTION;
SELECT * FROM journeys WHERE user_id = user_id_2 FOR UPDATE ;

终端#2 现在停止，因为我们从未在终端#1 上提交事务。

我的假设是，因为第一个终端 #1 上的条件与终端 #2 中的语句不同，所以第二个终端不会等待第一个终端提交。我的假设基于排他锁的定义，它说排他锁阻止任何其他事务锁定同一行。这是一个错误的假设吗？如果是，如何实现锁定我们在第一个条件下拥有的行？

当我在条件中使用主键时，它似乎有所不同。在下面的情况下，终端#2 不等待终端#1 提交。

terminal #1
START TRANSACTION;
SELECT * FROM journeys WHERE journey_id = journey_id_1 FOR UPDATE ;

终端 #2 中的语句

terminal #2
START TRANSACTION;
SELECT * FROM journeys WHERE journey_id = journey_id_2 FOR UPDATE ;

当我们有不包括主键的条件时，排他锁究竟会发生什么？我们要锁定整张桌子吗？

标签： mysqltransactionslockinginnodbtransaction-isolation

解决方案

是的，当您在未索引列（如user_id.

锁适用于所有“已检查”的行。您的条件WHERE user_id = <user_id>必须检查表中的所有行，并一一测试它们是否与 的值匹配<user_id>。

两个查询都在检查整个行集，即使它们正在搜索 的不同特定值<user_id>，所以它们会发生冲突。

如果你在user_id列上有一个索引，那么 MySQL 将首先使用该索引来查找匹配的行，然后只有匹配的行才会成为检查的行，因此会被锁定。

这与事务隔离级别无关。这些类型的锁出现在所有事务隔离级别中。

推荐阅读