使用MySQL的FOR UPDATE锁定时，究竟锁定了什么? [英] When using MySQL's FOR UPDATE locking, what is exactly locked?

问题描述

这不是完整/正确的MySQL查询专用伪代码:

This is not a full/correct MySQL query only pseudo-code:

Select *
 from Notifications as n
 where n.date > (CurrentDate-10 days)
 limit by 1
 FOR UPDATE

http://dev.mysql.com/doc/refman/5.0/en/select .html 指出: 如果将FOR UPDATE与使用页锁或行锁的存储引擎一起使用，则查询检查的行将被写锁，直到当前事务结束为止.

http://dev.mysql.com/doc/refman/5.0/en/select.html states: If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction

这里是MySQL锁定返回的仅一条记录还是它必须扫描以查找单个记录的所有记录?

Is here only the one record returned locked by MySQL or all records it has to scan to find the single record?

推荐答案

我们为什么不尝试一下?

Why don't we just try it?

设置数据库

CREATE DATABASE so1;
USE so1;
CREATE TABLE notification (`id` BIGINT(20), `date` DATE, `text` TEXT) ENGINE=InnoDB;
INSERT INTO notification(id, `date`, `text`) values (1, '2011-05-01', 'Notification 1');
INSERT INTO notification(id, `date`, `text`) values (2, '2011-05-02', 'Notification 2');
INSERT INTO notification(id, `date`, `text`) values (3, '2011-05-03', 'Notification 3');
INSERT INTO notification(id, `date`, `text`) values (4, '2011-05-04', 'Notification 4');
INSERT INTO notification(id, `date`, `text`) values (5, '2011-05-05', 'Notification 5');

现在，开始两个数据库连接

Now, start two database connections

连接1

BEGIN;
SELECT * FROM notification WHERE `date` >= '2011-05-03' FOR UPDATE;

连接2

BEGIN;

如果MySQL锁定所有行，则以下语句将阻塞.如果仅锁定返回的行，则不应阻塞.

If MySQL locks all rows, the following statement would block. If it only locks the rows it returns, it shouldn't block.

SELECT * FROM notification WHERE `date` = '2011-05-02' FOR UPDATE;

实际上确实会阻止.

有趣的是，我们也无法添加将要读取的记录，即

Interestingly, we also cannot add records that would be read, i.e.

INSERT INTO notification(id, `date`, `text`) values (6, '2011-05-06', 'Notification 6');

也有障碍！

我现在不能确定在锁定一定百分比的行时，MySQL是否只是继续执行并锁定整个表，还是在确保SELECT ... FOR UPDATE查询的结果永远不会真正聪明的地方?在保持锁的状态下被另一个事务(具有INSERT，UPDATE或DELETE)更改.

I can't be sure at this point whether MySQL just goes ahead and locks the entire table when a certain percentage of rows are locked, or where it's actually really intelligent in making sure the result of the SELECT ... FOR UPDATE query can never be changed by another transaction (with an INSERT, UPDATE, or DELETE) while the lock is being held.

这篇关于使用MySQL的FOR UPDATE锁定时，究竟锁定了什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！