Mysql SELECT FOR UPDATE - 奇怪的问题
我对 MySQL 的锁定功能有一个奇怪的问题（至少对我来说:)）。
我有一张表：
create table `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1
包含以下数据：
+----+
| ID |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 10 |
| 11 |
| 12 |
+----+
现在我有 2 个客户端，在开始时执行了这些命令：
设置自动提交=0;
设置会话事务隔离级别可串行化；
开始；
现在是最有趣的部分。第一个客户端执行此查询：（意图插入 id 等于 9 的行）
从测试中选择 *，其中 id = 9 进行更新；
空集（0.00 秒）
然后第二个客户端执行相同操作：
从测试中选择 *，其中 id = 9 进行更新；
空集（0.00 秒）
我的问题是：为什么第二个客户端不阻塞？第一个查询应该设置独占间隙锁，因为已使用 FOR UPDATE 并且第二个客户端应该阻塞。
如果我错了，有人可以告诉我如何正确地做吗？
我使用的MySql版本是：5.1.37-1ubuntu5.1
I have a strange issue (at least for me :)) with the MySQL's locking facility.
I have a table:
create table `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1
With this data:
+----+
| id |
+----+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 10 |
| 11 |
| 12 |
+----+
Now I have 2 clients with these commands executed at the beginning:
set autocommit=0;
set session transaction isolation level serializable;
begin;
Now the most interesting part. The first client executes this query: (makes an intent to insert a row with id equal to 9)
SELECT * from test where id = 9 FOR UPDATE;
Empty set (0.00 sec)
Then the second client does the same:
SELECT * from test where id = 9 FOR UPDATE;
Empty set (0.00 sec)
My question is: Why the second client does not block ? An exclusive gap lock should have been set by the first query because FOR UPDATE have been used and the second client should block.
If I am wrong, could somebody tell me how to do it correctly ?
The MySql version I use is: 5.1.37-1ubuntu5.1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
因为那时，返回（空）结果是安全的 - 没有为 id=9 的记录设置锁定，因为该记录不存在，因此无法更新 - 我认为你不能依赖 innodb在这种情况下设置读锁。但它应该在 id=9 上设置写锁。
如果稍后某个事务确实更新了表，并与另一个事务接触了相同的数据，则更新可能会阻塞其中一个事务，并且如果另一个事务提交了该数据，则稍后会失败。在这种情况下，事务失败是完全正常的 - 让您自行处理 - 这通常是重试事务的问题。
如果有一条 id=9 的记录，您可能会看到 2.
select块，直到第一个事务完成，因为现在有一条记录必须被读取锁定，以防第一个事务完成决定更新该行。Because at that time, it's safe to return the (empty) result - there's no lock to set for the record with id=9 as that doesn't exist and thus it cannot be updated - I don't think you can rely on innodb setting a read locks in such a case. It should set a write lock on id=9 though.
If at a later time, one of the transactions do update the table, and touch the same data as another transaction - the update would likely block in one of the transactions and later on fail if the other transaction commited that data. It's perfectly normal for transactions to fail in scenarios like this - leaving you to handle it - which usually is a matter of retrying the transaction.
If there were a record with id=9 you'd probably see the 2.
selectblock until the first transaction is finished, as now there is a record that have to be read locked in case the first transaction decides to update that row.http://dev.mysql.com/doc/refman/ 5.0/en/lock-tables.html
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html