如何在 MySQL 中实现原子事务？

Question

我正在开发我的演示项目 - 这是一个简单的银行系统。

我遇到了一个问题。

我需要在我的账户里添加一些虚拟货币。

但我需要像“原子操作”一样进行操作，即在更新之前查询一些数据。

例如：

Query table A // select from table A
Query table B // select from table B
if (A + B > X) 
Add money // insert into table C

问题是，在查询A或B期间，另一个线程可能会开始一些工作。

我应该使用什么样的mysql技术？

例子： 快乐的例子

User see A = 1, B = 1 in dashboard
User will send request

SELECT A
SELECT B
INSERT A + B // result is 2

糟糕的例子

User see A = 1, B = 1 in dashboard
User will send request

SELECT A
// SOMEONE CHANGED B RIGHT NOW TO 10 !
SELECT B
INSERT A + B // result is 12

- teteyi3241

---

你应该使用 TRANSACTION。 - Luuk

查询语句并不重要，你需要在插入和更新操作周围套上一个事务，这样它们就可以顺利执行，或者当发生错误时，已经完成的操作会被回滚，从而使得数据库看起来好像什么也没有发生。你只修改表 C。 - RiggsFolly

但是有人可以在选择中“破坏”数据。就像，有人可以像“关键部分”一样更改我的数据。 - teteyi3241

你所谓的“break”数据，在你释放事务锁后1毫秒内发生的情况有什么阻止措施？ - RiggsFolly

这就是锁读取的作用。 - Shadow

Answer 1

仅使用事务并不能满足您的需求。在MySQL中，普通的读查询无法防止其他会话更新行。请参阅MySQL中的锁定读取：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html 为了避免您提到的竞态条件，您需要一次性获得对A和B的锁定。您可以通过使用JOIN或UNION对两个资源进行锁定读取来实现此目的。

您还可以锁定整个表，并原子地锁定多个表。请参阅https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html。

- Bill Karwin

---

嗨，比尔，"SERIALIZABLE"是我这个例子的事务解决方案吗？ - teteyi3241

SERIALIZABLE将普通读取转换为锁定读取，就像您对每个查询执行了SELECT ... LOCK IN SHARE MODE一样。这将阻止针对已锁定行的并发更新，但您仍需要注意制作一个可以锁定多个资源的单个SELECT。 - Bill Karwin

Answer 2

如果我理解正确，我曾经在不使用锁定读取的情况下完成了此操作，方法如下：

假设用户A想要将5美元从他们的帐户转移到用户B的帐户。安全地执行该操作的伪代码如下：

1.开始一个事务。

2.更新帐户余额：UPDATE Account SET Balance = Balance - 5 WHERE User = 'A' AND Balance >= 5

3.如果第2步返回的受影响行数为零，则回滚事务-这表示资金不足，否则继续。

4.更新用户B的帐户余额：UPDATE Account SET Balance = Balance + 5 WHERE User = 'B'

5.提交事务。

我相信这应该消除了任何竞争条件，并消除了不必要的读取。

- Eric Petroelje

Answer 3

也许有很多好选择，但我通常的做法是在读取操作上加锁。你可以使用 SELECT ... FOR UPDATE 在行上设置锁定，这将对该行进行排他锁定。

User see A = 1, B = 1 in dashboard
User will send request

SELECT A FOR UPDATE => no transaction can access A 
SELECT B FOR UPDATE => no transaction can access B 
INSERT A + B // result is 12

性能方面的缺点是很明显的。尽管我们只是选择A和B，但我们会放置独占锁，这将阻塞所有读者和写入者。因此，请尽量将事务设置得尽可能短。

此外，可能会出现一些死锁情况。例如，假设有两个事务Tx1和Tx2。

Tx1                                             Tx2
SELECT A FOR UPDATE
                                                SELECT B FOR UPDATE       
SELECT B FOR UPDATE 
                                                SELECT A FOR UPDATE

在上述情况下，由于Tx1和Tx2对A和B进行了独占锁定，后续事务无法访问A和B。Tx1将尝试访问B，但它被Tx2锁定，Tx2将尝试访问A，但它被Tx1锁定。这是可能发生的死锁情况。一些数据库管理系统（如MySQL）会自动检测到死锁并中止事务。

- Seon Woo Kim