MySQL设置用户变量锁定行，并且不服从可重复读取

Question

我遇到了一种"SET @my_var = (SELECT .)“的非法行为在交易中：

1. 第一个是锁行(这取决于它是否是唯一的索引)。

例子-

START TRANSACTION;

SET @my_var = (SELECT id from table_name where id = 1);

select trx_rows_locked from information_schema.innodb_trx;
ROLLBACKL;

输出是1行锁定，这是奇怪的，它不应该获得一个读取锁。

而且，等效的语句SELECT id INTO @my_var不会产生锁。

在UPDATED语句之后(对于2个并发请求)，它可能导致死锁。

1. 在可重复阅读中-

SELECT语句中的SET获取数据的新快照，而不是使用原始快照。

第1场会议：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;                             
START transaction;         
SELECT data FROM my_table where id = 2; # Output : 2

第2场会议：

UPDATE my_table set data = 3 where id = 2 ;

第1场会议：

 SET @data = (SELECT data FROM my_table where id = 2);
 SELECT @data; # Output : 3, instead of 2
 ROLLBACK;

但是，我希望@data将包含来自第一个快照(2 )的原始值。

如果我使用SELECT data into @data from my_table where id = 2，那么我将得到期望值- 2；

您知道SET = (SELECT ..)与SELECT data INTO @var FROM ..不同行为的来源吗？

谢谢。

Answer 1

正确--当您在将结果复制到变量或表的上下文中SELECT时，它隐式地工作就像您使用了锁读 SELECT ... FOR SHARE一样。

这意味着它在所检查的行上放置一个共享锁，还意味着该语句只读取最近提交的行版本，就好像您的事务处于读提交隔离级别。

我不知道为什么SELECT ... INTO @var不在MySQL 8.0中执行相同的隐式锁定。我的记忆是，在较早版本的MySQL中，它确实会锁定该查询形式。我已经在手册上找了一份解释，但我还找不到。

其他情况下，隐式锁定SELECT检查的行，从而读取数据，就好像您的事务已被读取提交一样：

• INSERT INTO <table> SELECT ...
• UPDATE或DELETE多表，即使不更新或删除给定的表，连接的行也会被锁定。
• 触发器内的SELECT