MySQL排他锁

Question

我们有下表:

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 ;

当我们的条件不包括主键时，排他锁会发生什么？我们是否锁定了整个表？

Answer 1

是的，当您在未编入索引的列(如 user_id)上有条件时，您将锁定表中的所有行。 .

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

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

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

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