为什么要使用SELECT FOR UPDATE？（MySQL）

Question

关于我们使用 SELECT FOR UPDATE 的目的，我有一个问题。它到底是做什么的？

我有两个表，需要从其中一个表中选择行并更新相同的行。

例如：

Select 查询

SELECT * from  t1 WHERE city_id=2 for update

更新查询

UPDATE t1 SET final_balance = final_balance - 100 WHERE city_id ='2'

我的问题是 - 这是否真的会锁定读取操作直到我的更新完成，或者它具体处理的是什么？

我的想法是，在我的更新完成之前，任何人都无法从这些行中读取/更新。

- Rinto George

---

为什么不看手册呢？http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html - Marc B

“我的想法是没有人可以从这些行中读取/更新” - 这是错误的。行锁并不阻止读取访问（例如，普通的select语句而不带有for update）。 - user330315

Answer 1

SELECT ... FOR UPDATE将使用写锁（排他锁）锁定记录，直到事务完成（提交或回滚）。

为了选择记录并确保在更新之前不被修改，您可以启动一个事务，使用SELECT ... FOR UPDATE选择记录，进行一些快速处理，然后更新记录，然后提交（或回滚）事务。

如果在事务之外（自动提交处于打开状态）使用SELECT ... FOR UPDATE，则锁定仍会立即释放，因此请确保使用事务以保留锁定。

为了提高性能，请不要将事务保持打开状态太长时间，因此应立即执行更新操作。

- Marcus Adams

---

事务不保证锁定吗？换句话说，如果事务具有低隔离级别（例如read_uncommit），那么SELECT ... FOR UPDATE才有意义吗？ - J.J. Beam

在我看来，SELECT ... FOR UPDATE 通常应用于“读取提交”隔离级别下。 - CandyCrusher

在事务中，使用UPDATE语句可以保证行的锁定, 但对于SELECT(读取)操作不会。因此，如果您在事务的开始部分执行了一个没有带有FOR UPDATE的SELECT...语句，并且稍后在事务中需要使用这些选定行的信息以进行更新，则可能会发生其他事务已经更新了您之前在事务中查询的行的情况。因此，如果需要稍后使用所查询的行，请务必在事务中使用SELECT ... FOR UPDATE。更多信息请参见：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html - HelloWorld

Answer 2

在MySQL中，使用SELECT FOR UPDATE可以防止丢失更新和写偏斜。在MySQL中，只有SERIALIZABLE可以在没有SELECT FOR UPDATE的情况下防止丢失更新和写偏斜，但会出现死锁错误，因此即使对于SERIALIZABLE，我们也应该使用SELECT FOR UPDATE。您可以在我的答案和'丢失更新' vs '写偏斜'中了解更多关于丢失更新和写偏斜的内容。

现在在MySQL中，我将向您展示失去更新和写入偏斜以及如何使用REPEATABLE READ中的SELECT FOR UPDATE来防止它们。

首先，以下是失去更新的示例。下面是一个带有id、name和stock的产品表。

产品表：

id name stock

1 Apple 10 2 Orange 20

以下步骤展示了在MySQL中没有使用SELECT FOR UPDATE时的失效更新。*当客户购买产品时，产品的库存会减少：

流程	事务1（T1）	事务2（T2）	说明
步骤1	BEGIN;		T1开始。
步骤2		BEGIN;	T2开始。
步骤3	SELECT stock FROM product WHERE id = 2; 20		T1读取20，稍后更新为13，因为有客户购买了7个橙子。
步骤4		SELECT stock FROM product WHERE id = 2; 20	T2读取20，稍后更新为16，因为有客户购买了4个橙子。
步骤5	UPDATE product SET stock = '13' WHERE id = 2;		T1将20更新为13。
步骤6	COMMIT;		T1提交。
步骤7		UPDATE product SET stock = '16' WHERE id = 2;	T2在T1提交后将13更新为16。
步骤8		COMMIT;	T2提交。 *发生了丢失更新。

接下来，以下步骤展示如何在 MySQL 上使用 SELECT FOR UPDATE 防止丢失更新。*当客户购买产品时，产品的库存会减少：

流程	交易1 (T1)	交易2 (T2)	说明
步骤1	BEGIN;		T1开始。
步骤2		BEGIN;	T2开始。
步骤3	SELECT stock FROM product WHERE id = 2 FOR UPDATE; 20		使用“SELECT FOR UPDATE”，T1读取20，稍后更新为13，因为客户购买了7个橙子。
步骤4		SELECT stock FROM product WHERE id = 2 FOR UPDATE;	T2需要等待T1提交才能使用“SELECT FOR UPDATE”读取stock。
步骤5	UPDATE product SET stock = '13' WHERE id = 2;	等待...	T1将20更新为13。
步骤6	COMMIT;	等待...	T1提交。
步骤7		SELECT stock FROM product WHERE id = 2 FOR UPDATE; 13	现在使用“SELECT FOR UPDATE”，T2读取13，稍后更新为9，因为客户购买了4个橙子。
步骤8		UPDATE product SET stock = '9' WHERE id = 2;	T2在T1提交后将13更新为9。
步骤9		COMMIT;	T2提交。 *不会发生丢失更新。

第二，这是“写偏斜”的例子。下面是具有名称和用户的“事件”表。

事件表：

名称 用户 Make Sushi John Make Sushi Tom

以下步骤显示了在MySQL中没有使用“SELECT FOR UPDATE”的“写偏斜”。只有3个用户可以加入“制作寿司”活动。

流程	交易（JT）	交易（LT）	说明
步骤1	BEGIN;		T1开始。
步骤2		BEGIN;	T2开始。
步骤3	SELECT count(*) FROM event WHERE name = 'Make Sushi'; 2		T1读取2，因此只有一个用户可以加入。
步骤4		SELECT count(*) FROM event WHERE name = 'Make Sushi'; 2	T2读取2，因此只有一个用户可以加入。
步骤5	INSERT INTO event values ('Make Sushi', 'Lisa');		T1将Lisa插入到event表中。
步骤6	COMMIT;		T1提交。
步骤7		INSERT INTO event values ('Make Sushi', 'Kai');	T2将Kai插入到event表中。
步骤8		COMMIT;	T2提交。 4个用户加入。 *写偏斜发生。

接下来，以下步骤展示如何使用SELECT FOR UPDATE防止在MySQL上进行写入更新。*只有3个用户可以加入活动“制作寿司”：

流程	交易（JT）	交易（LT）	说明
步骤1	BEGIN;		T1开始。
步骤2		BEGIN;	T2开始。
步骤3	SELECT count(*) FROM event WHERE name = 'Make Sushi' FOR UPDATE; 2		使用“SELECT FOR UPDATE”，T1读取2，因此只有一个用户可以加入。
步骤4		SELECT count(*) FROM event WHERE name = 'Make Sushi' FOR UPDATE;	T2需要等待T1提交才能使用“SELECT FOR UPDATE”读取event表。
步骤5	INSERT INTO event values ('Make Sushi', 'Lisa');	等待中...	T1将Lisa插入event表。
步骤6	COMMIT;	等待中...	T1提交。
步骤7		SELECT count(*) FROM event WHERE name = 'Make Sushi' FOR UPDATE; 3	现在使用“SELECT FOR UPDATE”，T2读取3，因此没有用户可以加入。
步骤8		COMMIT;	T2提交。 3个用户加入。 *不会发生写偏斜。

- Kai - Kazuya Ito