问题描述
这两个锁定读取子句的确切区别是什么:
What is the exact difference between the two locking read clauses:
SELECT ... FOR UPDATE
和
SELECT ... LOCK IN SHARE MODE
为什么你需要一个而不是另一个?
And why would you need to use one over the other?
推荐答案
我一直试图了解两者之间的区别.我会记录我的发现，希望对下一个人有用.
I have been trying to understand the difference between the two. I'll document what I have found in hopes it'll be useful to the next person.
LOCK IN SHARE MODE 和 FOR UPDATE 都确保没有其他事务可以更新选定的行.两者的区别在于读取数据时如何处理锁.
Both LOCK IN SHARE MODE and FOR UPDATE ensure no other transaction can update the rows that are selected. The difference between the two is in how they treat locks while reading data.
LOCK IN SHARE MODE 不会阻止另一个事务读取被锁定的同一行.
LOCK IN SHARE MODE does not prevent another transaction from reading the same row that was locked.
FOR UPDATE 防止同一行的其他锁定读取(非锁定读取仍然可以读取该行；LOCK IN SHARE MODE 和 FOR UPDATE 是锁定读取).
FOR UPDATE prevents other locking reads of the same row (non-locking reads can still read that row; LOCK IN SHARE MODE and FOR UPDATE are locking reads).
这在更新计数器等情况下很重要，您在 1 个语句中读取值并在另一个语句中更新值.这里使用 LOCK IN SHARE MODE 将允许 2 个事务读取相同的初始值.因此，如果两个事务都将计数器加 1，则结束计数可能只会增加 1 - 因为两个事务最初读取的值相同.
This matters in cases like updating counters, where you read value in 1 statement and update the value in another. Here using LOCK IN SHARE MODE will allow 2 transactions to read the same initial value. So if the counter was incremented by 1 by both transactions, the ending count might increase only by 1 - since both transactions initially read the same value.
使用 FOR UPDATE 会锁定第二个事务，无法读取值，直到第一个事务完成.这将确保计数器增加 2.
Using FOR UPDATE would have locked the 2nd transaction from reading the value till the first one is done. This will ensure the counter is incremented by 2.
这篇关于MySQL InnoDB:“FOR UPDATE"和“LOCK IN SHARE MODE"之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！