MySQL事务：多个并发事务和数据完整性
我正在使用事务来管理相当复杂的 Web 应用程序中多个 MySQL InnoDB 表的数据。简而言之，给定事务的工作原理如下：
- 从“user_point_totals”表中的一行读取数据
- 各种机制计算用户的新积分总数应该是多少
- 在“user_point_totals”表中创建一个新条目，反映更新后的总数
假设用户 A 执行了一些与积分相关的操作，执行步骤 1，执行线程将用户的积分总数读入内存，并且应用程序开始计算新的总数。同时，用户 B 执行的操作会影响用户 A 的总积分，并且另一笔交易开始；但是，第一个事务尚未完成，因此第二个线程获取与第一个事务相同的点总值作为起点（来自同一表行）。随后，事务 1 完成并创建一个新的用户总积分，并了解新值应该是什么，此后不久，事务 2 完成并为用户总积分创建一个新行。然而，第二笔交易的总点数现在不正确，因为它无法说明交易 1 创建的新总点数。
我的问题是：
- 由于交易的原子性质，这种情况是否不可能，我显然也不理解这一点我应该吗？
- 如果不是，如何确保在此类情况下存在数据完整性？
感谢您的考虑！
I'm using transactions for managing data across several MySQL InnoDB tables in a reasonably complex web application. Briefly, a given transaction works as follows:
- Data is read from a row in a "user_point_totals" table
- Various machinations calculate what the user's new point total should be
- A new entry is created in the "user_point_totals" table reflecting the updated total
Let's say that user A performs some action that has point-related ramifications, step 1 is executed, that thread of execution reads the user's point total into memory, and the application begins calculating the new total. Meanwhile, user B performs an action that has implications for user A's point total, and another transaction begins; however, the first transaction has not yet completed, so the second thread gets the same point total value as a starting point as the first transaction (from the same table row). Subsequently, transaction 1 completes and creates a new user point total with its perception of what the new value should be, and shortly thereafter, transaction 2 completes and creates a new row for the user's point total as well. However, the second transaction's point total is now incorrect, as it fails to account for the new total created by transaction 1.
My questions are:
- Is this scenario impossible due to the atomic nature of transactions, which I apparently don't understand as well as I should?
- If not, how does one ensure that data integrity exists in these sorts of situations?
Thanks for your consideration!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在技术层面上，您可以使用表锁定 MySQL 的（或行锁定）能力。这将允许您检测某人是否实际上正在使用该表计算某些内容。另一方面，这种技术需要考虑很多因素，例如进程崩溃时会发生什么等。
但在实际层面上，我怀疑您会想做这样的事情。
MySQL 中的 sum() 或 avg() 等运算符已经针对此需求进行了优化。如果您需要做的是对表的某些列进行求和并在表中获取答案，则可以使用视图或创建临时表（可能但速度较慢）。您不应拥有包含可从其他列计算的值的列。这种情况会导致不一致，因为如果关系不平衡，就不清楚哪个字段是正确的。 （这是输入脚本错误还是某些程序员故意重复使用该字段？）
第二点，请务必在 MySQL 实例上使用 InnoDB 表，否则您的系统将不会完全兼容 ACID，这意味着您将无法获得所需的原子性。
On the technical level, you could use the table-locking (or row-locking) abilities of MySQL. That would allow you to detect if someone is actually computing something using the table. On the other hand, this technique would require many considerations like what happens if a process crash, etc.
On the practical level, though, I doubt you would want to do something like this.
Operators like sum() or avg() in MySQL are already optimised for this need. If what you need to do is a sum over some columns of a table and get the answer in a table, you could use a view or create a temporary table (possible but slower). You should not have a column that contains a value that could be computed from other columns. This situation leads to incongruities since it is unclear which field is true if the relation doesn't balance. (Is it an input script error or a deliberate re-use of the field by some programmer?)
On a second note, be sure to use InnoDB tables on your MySQL instance, otherwise your system won't be fully ACID-compilant, meaning you won't get the atomicity nature you need.