SQL 原子增量和锁定策略 - 这安全吗？

Question

我有一个关于 SQL 和锁定策略的问题。举个例子，假设我的网站上有一个图像查看计数器。如果我有一个存储过程或类似的来执行以下语句:

START TRANSACTION;
UPDATE images SET counter=counter+1 WHERE image_id=some_parameter;
COMMIT;

假设特定 image_id 的计数器在时间 t0 的值为“0”。如果更新同一图像计数器 s1 和 s2 的两个 session 在 t0 同时启动，这两个 session 是否有可能都读取值“0”，将其增加到“1”并都尝试将计数器更新为“1” '，那么计数器的值将是“1”而不是“2”？

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok

最终结果:image_id=15 的值“1”不正确，应该是 2。

我的问题是:

1. 这种情况可能吗？
2. 如果是这样，事务隔离级别重要吗？
3. 是否有冲突解决程序可以将此类冲突检测为错误？
4. 可以使用任何特殊语法来避免问题(例如比较和交换 (CAS) 或显式锁定技术)吗？

我对一般答案感兴趣，但如果没有，我对 MySql 和 InnoDB 特定的答案感兴趣，因为我正在尝试使用这种技术在 InnoDB 上实现序列。

编辑:以下情况也可能会导致相同的行为。我假设我们处于 READ_COMMITED 或更高的隔离级别，因此 s2 从事务开始时获取值，尽管 s1 已经将“1”写入计数器。

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: read counter for image_id=15, get 0 (since another tx), store in temp2
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok

Answer 1

UPDATE 查询在其读取的页面或记录上放置更新锁。

当决定是否更新记录时，锁要么被解除，要么提升为独占锁。

这意味着在这种情况下:

s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1

s2会等待s1决定是否写入计数器，而这种情况实际上是不可能的。

将会是这样的:

s1: place an update lock on image_id = 15
s2: try to place an update lock on image_id = 15: QUEUED
s1: read counter for image_id=15, get 0, store in temp1
s1: promote the update lock to the exclusive lock
s1: write counter for image_id=15 to (temp1+1), which is 1 
s1: commit: LOCK RELEASED
s2: place an update lock on image_id = 15
s2: read counter for image_id=15, get 1, store in temp2
s2: write counter for image_id=15 to (temp2+1), which is 2

请注意，在 InnoDB 中，DML 查询不会解除其读取的记录的更新锁。

这意味着，在全表扫描的情况下，已读取但决定不更新的记录仍将保持锁定状态，直到事务结束，并且无法从另一个事务更新。