竞争条件 UPDATE 修改信用列 - 回滚时会发生什么？
好吧，我尝试搜索，但没有找到答案 - 我很好奇 ROLLBACK 如何处理竞争条件。例如：
如果我有一个表（CompanyAccount），它跟踪一家公司有多少积分可供购买（每个公司的数据库表中只有一行），并且同一家公司可能有多个用户可以减少单个公司账户的积分，ROLLBACK发生错误时会发生什么？
示例：
假设：我已正确编写更新来计算“信用”新余额，而不是猜测新的信用余额是什么（即我们不会尝试告诉 UPDATE 语句新的信用余额/值是什么，我们说采取信用列中的任何内容并减去 UPDATE 语句中的减量值）...
这是更新语句编写方式的示例：
UPDATE dbo.CompanyAccount SET 信用 = 信用 - @DecrementAmount WHERE CompanyAccountId = @CompanyAccountId
如果“信用”列有 10,000 个信用。用户 A 导致 4,000 个积分的减少，用户 B 导致 1000 个积分的减少。由于某种原因，在用户 A 递减期间会触发回滚（在事务期间大约有 1/2 打的表中的行被插入）。如果用户 A 赢得竞争条件并且新余额为 6,000（但尚未提交），那么如果用户 B 的减量发生在应用回滚之前，会发生什么情况？余额列是否从 6,000 变为 5,000，然后回滚到 10,000？
我不太清楚 ROLLBACK 将如何处理这个问题。也许我过于简单化了。如果我误解了 ROLLBACK 的工作原理，或者我是否需要担心这种风格的其他风险，有人可以告诉我吗？
感谢您的意见。
ok, I tried searching and have not found an answer to this - I am curious how the ROLLBACK handles race conditions. For example:
If I have a table (CompanyAccount) which keeps track of how many credits an company has available for purchase (there is only one row in a database table per company) and there are potentially multiple users from the same company who can decrement the credits from the single company account, what happens in case of an error when a ROLLBACK occurs?
Example:
Assumptions: I have written the update properly to calculate the "Credit" new balance instead of guessing what the new credit balance is (i.e. we don't try to tell the UPDATE statement what the new Credit balance/value is, we say take whatever is in the credit column and subtract my decrement value in the UPDATE statement)...
here is an example of how the update statement is written:
UPDATE dbo.CompanyAccount
SET Credit = Credit - @DecrementAmount
WHERE CompanyAccountId = @CompanyAccountId
If the "Credit" column has 10,000 credits. User A causes a decrement of 4,000 credits and User B causes a decrement of 1000 credits. For some reason a rollback is triggered during User A's decrement (there are about a 1/2 dozen more tables with rows getting INSERTED during the TRANSACTION). If User A wins the race condition and the new balance is 6,000 (but not yet COMMIT'ed) what happens if User B's decrement occurs before the rollback is applied? does the balance column go from 6,000 to 5,000 and then gets ROLLBACK to 10,000?
I am not too clear on how the ROLLBACK will handle this. Perhaps I am over-simplifying. Can someone please tell me if I misunderstand how ROLLBACK will work or if there are other risks I need to worry about for this style.
Thanks for your input.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在你给出的例子中，不会有任何问题。
第一个事务将具有排它锁，这意味着第二个事务在第一个事务提交或回滚之前无法修改该行。它只需要等待（阻塞）直到锁被释放。
如果您有多个语句，情况会变得更加复杂。您可能应该阅读不同的隔离级别以及它们如何允许或防止“更新丢失”等现象。
In the example you have given there will be no problem.
The first transaction will have an exclusive lock meaning the second one can not modify that row until after the first one has committed or rolled back. It will just have to wait (blocked) until the lock is released.
It gets a bit more complicated if you have multiple statements. You should probably read up on different isolation levels and how they can allow or prevent such phenomena as "lost updates".
回滚是事务的一部分，并且在回滚期间将维护锁。 ACID 中的 *A*tomic。
在所有锁被释放之前，用户B不会启动。
会发生什么：
但是，如果用户 B 已经读取了余额，那么在更新时可能会不一致。这取决于您实际执行的操作以及顺序，因此需要了解隔离级别 （以及幻像和不可重复读取的问题）
SERIALIZABLE 或 REPEATABLE READ 的替代方案可以在事务模式下使用 sp_getapplock 来对事务的信号部分进行标记。
Rollback is part of the transaction and locks will be maintained during the rollback. The *A*tomic in ACID.
User B will not start until all locks are released.
What happens:
However, if User B has already read the balance then it my be inconsistent when it comes to UPDATE. It depends on what you're actually doing and in what order, hence the need to understand isolation levels (and the issues with phantom and non-repeatable reads)
An alternative to SERIALIZABLE or REPEATABLE READ may to use sp_getapplock in transaction mode to semaphore parts of the transaction.