MySQL独占锁 [英] MySQL exclusive lock
问题描述
我们有下表:
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.
The isolation level is REPEATABLE_READ.
根据此处的词汇表: https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_exclusive_lock
According to the glossary here: https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_exclusive_lock
排他锁是一种锁，可以防止任何其他事务锁定同一行.根据事务隔离级别，这种锁定可能会阻止其他事务写入同一行，或者也可能阻止其他事务读取同一行.
An exclusive lock is kind of lock that prevents any other transaction from locking the same row. Depending on the transaction isolation level, this kind of lock might block other transactions from writing to the same row, or might also block other transactions from reading the same row.
我们的逻辑看起来像下面的逻辑(user_id具有不同的值):
The logic that we have looks like the one below (the user_id takes different values):
START TRANSACTION;
SELECT * FROM journeys WHERE user_id = <user_id> FOR UPDATE ;
# COMMIT;
下面有一些测试.我打开一个终端窗口(终端#1)，然后执行以下语句:
Below there is a bit of testing. I open one terminal window (terminal #1) and I execute the statements below:
START TRANSACTION;
SELECT * FROM journeys WHERE user_id = user_id_1 FOR UPDATE ;
然后我打开第二个终端(终端#2)窗口，并执行以下语句:
Then I open a second terminal (terminal #2) window and I execute the statements below:
START TRANSACTION;
SELECT * FROM journeys WHERE user_id = user_id_2 FOR UPDATE ;
2号航站楼现在暂停了，因为我们从未在1号航站楼上进行交易.
The terminal #2 now halts because we never committed the transaction on terminal #1.
我的假设是，因为第一个终端#1上的条件不同于第二个终端中的陈述，即第二个终端不会等待第一个终端提交.我以互斥锁的定义为基础，该定义表示互斥锁可防止任何其他事务锁定同一行.这是一个错误的假设吗?如果是，如何才能锁定我们在第一个条件下拥有的行?
My assumption was that because the condition on the first terminal #1 is different from the statement in terminal #2 that the second terminal won't wait for the first one to commit. I'm basing my assumption on the definition of an exclusive lock which says that the exclusive lock prevents any other transaction from locking the same row. Is this a wrong assumption? If yes how can achieve to lock the rows that we have in the first condition?
在条件中使用主键时，似乎有所不同.在下面的情况下，终端2不在等待终端1提交.
It seems that it is different when I use the primary key in the condition. In the case below the terminal #2 is not waiting for the terminal #1 to commit.
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 ;
当我们具有不包含主键的条件时，排他锁到底会发生什么?我们要锁定整个桌子吗?
What happens exactly with the exclusive locks when we have conditions that do not include primary keys? Are we locking the whole table?
推荐答案
是的，当您对无索引列(例如user_id)有条件时，您将锁定表中的所有行.
Yes, you're locking all the rows in the table when you have a condition on an unindexed column like user_id.
这些锁适用于所有已检查"的行.您的条件WHERE user_id = <user_id>必须检查表中的 all 行，并逐行对其进行测试，以查看它们是否与<user_id>的值匹配.
The locks apply to all "examined" rows. Your condition WHERE user_id = <user_id> must examine all the rows in the table, and test them one by one to see if they match the value of <user_id>.
这两个查询都在检查整个行集，即使它们正在搜索<user_id>的不同特定值，因此它们也会冲突.
Both queries are examining the whole set of rows, even though they are searching for different specific values of <user_id>, so they conflict.
如果您在user_id列上有一个索引，则MySQL将首先使用该索引查找匹配的行，然后只有匹配的行将成为已检查的行，因此将被锁定.
If you had an index on the user_id column, then MySQL would use that index to find the matching rows first, then only matching rows would become examined rows, and therefore would be locked.
这与事务隔离级别无关.这些类型的锁出现在所有事务隔离级别中.
This has nothing to do with the transaction isolation level. These types of locks occur in all transaction isolation levels.
这篇关于MySQL独占锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
