SQL 原子增量和锁定策略 - 这安全吗？
我有一个关于 SQL 和锁定策略的问题。举个例子，假设我的网站上有一个图像查看计数器。如果我有一个存储过程或类似的存储过程来执行以下语句：
START TRANSACTION;
UPDATE images SET counter=counter+1 WHERE image_id=some_parameter;
COMMIT;
假设特定 image_id 的计数器在时间 t0 的值为“0”。如果更新同一图像计数器 s1 和 s2 的两个会话在 t0 同时启动，这两个会话是否有可能都读取值“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。
我的问题是：
- 这种情况可能吗？
- 如果是这样，事务隔离级别重要吗？
- 是否有冲突解决程序可以将此类冲突检测为错误？
- 可以使用任何特殊语法来避免问题（例如比较和交换（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
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;
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
End result: incorrect value '1' for image_id=15, should have been 2.
My questions are:
- Is this scenario possible?
- If so, does the transaction isolation level matter?
- Is there a conflict resolver which would detect such a conflict as an error?
- Can one use any special syntax in order to avoid a problem (something like Compare And Swap (CAS) or explicit locking techniques)?
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.
EDIT:
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
UPDATE查询在其读取的页面或记录上放置更新锁。当决定是否更新记录时，锁要么被解除，要么升级为排它锁。
这意味着在这种情况下：
s2将等到s1决定是否写入计数器，而这种情况实际上是不可能的。它将是这样的：
请注意，在
InnoDB中，DML查询不会解除它们读取的记录的更新锁。这意味着在全表扫描的情况下，已读取但决定不更新的记录仍将保持锁定状态，直到事务结束，并且无法从另一个事务更新。
UPDATEquery 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:
s2will wait untils1decides whether to write the counter or not, and this scenario is in fact impossible.It will be this:
Note that in
InnoDB,DMLqueries 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.
如果锁定没有正确完成，那么肯定有可能出现这种类型的竞争条件，并且默认锁定模式（已提交读）确实允许这种情况。在此模式下，读取者仅在记录上放置共享锁，因此他们都可以看到 0、递增它并向数据库写入 1。
为了避免这种竞争情况，需要对读操作设置排它锁。 “可序列化”和“可重复读取”并发模式可以做到这一点，并且对于单行上的操作它们几乎是等效的。
要使其完全原子化，您必须：
您还可以使用 HOLDLOCK (T-SQL) 或等效提示强制对读取进行独占锁定，具体取决于您的 SQL 方言。
单个更新查询将以原子方式执行此操作，但您无法在不确保读取获取排他锁的情况下拆分操作（可能读取值并将其返回给客户端）。 您需要以原子方式获取值才能实现序列，因此更新本身可能并不是您所需要的全部。 即使使用原子更新，您仍然需要在更新后读取值的竞争条件。读取仍然必须在事务内进行（将其获取的内容存储在变量中）并发出读期间的独占锁。
请注意，要在不创建热点的情况下执行此操作，您的数据库需要对自主（嵌套）事务有适当的支持 在存储过程中。请注意，有时“嵌套”用于指代链接事务或保存点，因此该术语可能有点令人困惑。我对此进行了编辑以引用自主交易。
如果没有自治事务，您的锁将由父事务继承，这可以回滚全部事务。这意味着它们将被保留，直到父事务提交，这可以将您的序列变成一个热点，使用该序列序列化所有事务。任何其他尝试使用该序列的操作都将阻塞，直到整个父事务提交为止。
IIRC Oracle 支持自治事务，但 DB/2 直到最近才支持自治事务，而 SQL Server 也不支持。我不知道 InnoDB 是否支持它们，但是 Grey 和 Reuter< /a> 继续详细说明它们的实施有多么困难。实际上，我猜很可能不会。 YMMV。
If the locking is not done properly it certainly is possible to get this type race condition, and the default locking mode (read committed) does allow it. In this mode, the reads only place a shared lock on the record, so they can both see 0, increment it and write 1 out to the database.
In order to avoid this race condition, you need to set an exclusive lock on the read operation. 'Serializable' and 'Repeatable Read' concurrency modes will do this, and for an operation on a single row they are pretty much equivalent.
To make it completely atomic you have to:
You can also force an exclusive lock on the read with a HOLDLOCK (T-SQL) or equivalent hint, depending on your SQL dialect.
A single update query will do this atomically but you can't split the operation (perhaps to read the value and return it to the client) without ensuring that the reads take out an exclusive lock. You will need to get the value out atomically in order to implement a sequence, so the update by itself is probably not quite all you need. Even with the atomic update, you still have a race condition to read the value after the update. The read will still have to take place within a transaction (storing what it got in a variable) and issue an exclusive lock during the read.
Note that to do this without creating a hot spot your database needs to have proper support for autonomous (nested) transactions within a stored procedure. Note that sometimes 'nested' is used to refer to chaining transactions or save points, so the term can be a bit confusing. I've edited this to refer to autonomous transactions.
Without autonomous transactions your locks are inherited by the parent transaction, which can roll back the whole lot. This means they will be held until the parent transaction commits, which can turn your sequence into a hot spot that serialises all transactions using that sequence. Anything else trying to use the sequence will block until the whole parent transaction commits.
IIRC Oracle supports autonomous transactions but DB/2 didn't until fairly recently and SQL Server doesn't. Off the top of my head I don't know whether InnoDB supports them, but Grey and Reuter go on at some length about how difficult they are to implement. In practice I'd guess it's quite likely that it might not. YMMV.