在事务期间锁定MYSQL表中的行 [英] Locking a row in a MYSQL table during a transaction
问题描述
我有很多这样的查询:
START TRANSACTION
SELECT amount FROM piggybank WHERE id = 2
UPDATE piggybank SET amount = amount + 5 WHERE id = 1
COMMIT
我需要确保在事务结束之前,没有其他人可以读取或写入ID为1和2的行. (锁定整个表也可以) 问题是,当我阅读MySQL手册时,它说启动一个事务将清除在启动它之前进行的所有锁定,反过来,锁定将提交任何传出的事务.
I need to make sure that no one else can read or write the rows with ID 1 and 2, until the transaction ends. (Locking the whole table would be fine too) The problem is, that when I read the MySQL manual, it said that starting a transaction will clear any locks made before starting it and in turn, locking will commit any outgoing transaction.
但是我需要同时锁定行(或表)并同时使用事务.
But I need to both lock the rows (or table) and use the transactions at the same time as well.
推荐答案
在事务内使用select ... for update
查询应会为您提供所需的语义-其他更新将被锁定,而其他尝试采用相同锁定的会话将被锁定阻止直到提交:
using a select ... for update
query inside the transaction should give you the semantics you want - other updates will be locked, and other sessions attempting to take this same lock will block until you commit:
START TRANSACTION
SELECT * FROM piggybank WHERE id IN (1, 2) FOR UPDATE;
SELECT amount FROM piggybank WHERE id = 2;
UPDATE piggybank SET amount = amount + 5 WHERE id = 1;
COMMIT
这篇关于在事务期间锁定MYSQL表中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!