SQL原子增量和锁定策略-这样安全吗? [英] SQL atomic increment and locking strategies - is this safe?

问题描述

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

I have a question about SQL and locking strategies. As an example, suppose I have a view counter for the images on my website. If I have a sproc or similar to perform the following statements:

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'?

Assume that the counter for a specific image_id has value '0' at time t0. If two sessions updating the same image counter, s1 and s2, start concurrently at t0, is there any chance that these two sessions both read the value '0', increase it to '1' and both try to update the counter to '1', so the counter will get value '1' instead of '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.

End result: incorrect value '1' for image_id=15, should have been 2.

我的问题是:

1. 这种情况可能吗?
2. 如果是，那么事务隔离级别是否重要?
3. 是否有一个冲突解决程序可以检测到诸如错误之类的冲突?
4. 有人可以使用任何特殊的语法来避免出现问题(例如比较并交换"(CAS)或显式锁定技术)吗?

我对通用答案感兴趣，但是如果没有答案，我会对MySql和InnoDB特定的答案感兴趣，因为我试图使用此技术在InnoDB上实现序列.

I'm interested in a general answer, but if there are none I'm interested in MySql and InnoDB-specific answers, since I'm trying to use this technique to implement sequences on InnoDB.

以下情况也是可能的，从而导致相同的行为.我假设我们处于隔离级别READ_COMMITED或更高，因此s2从事务开始就获取了值，尽管s1已经向计数器写入了"1".

The following scenario could also be possible, resulting in the same behavior. I'm assuming that we are in isolation level READ_COMMITED or higher, so that s2 gets the value from the start of the transaction although s1 already wrote '1' to the counter.

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

推荐答案

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

UPDATE query places an update lock on the pages or records it reads.

在决定是否更新记录时，该锁将被解除或提升为排他锁.

When a decision is made whether to update the record, the lock is either lifted or promoted to the exclusive lock.

这意味着在这种情况下:

This means that in this scenario:

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决定是否写入计数器，这种情况实际上是不可能的.

s2 will wait until s1 decides whether to write the counter or not, and this scenario is in fact impossible.

就是这样:

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查询不会从读取的记录中删除更新锁.

Note that in InnoDB, DML queries do not lift the update locks from the records they read.

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

This means that in case of a full table scan, the records that were read but decided not to update, will still remain locked until the end of the transaction and cannot be updated from another transaction.

这篇关于SQL原子增量和锁定策略-这样安全吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！