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

Question

我对SQL和锁定策略有疑问。例如，假设我在我的网站上有一个图片计数器。如果我有一个sproc或类似的东西来执行以下语句：

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

假设在时间t0特定image_id的计数器的值为‘0’。如果两个会话从t0同时开始更新同一个图像计数器s1和s2，则这两个会话都有可能都读取值‘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查询不会从其读取的记录中提取更新锁。

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