哪个应该采取主动任务回滚，App或DB？ [英] Which should take initiative task on rollback, App or DB?

问题描述

MySql InnoDB设置为autocommit关闭并使用默认隔离级别REPEATABLE READ。有两种情况，两个不同的交易T1和T2按照以下时间顺序运行：

MySql InnoDB is set autocommit off and used default isolation level REPEATABLE READ. There are two scenarioes that two distinct transactions T1 and T2 run in the time sequence below,

1）

time    T1                  T2

t1  update row 1->OK
t2                      update row 2->OK
t3  update row 2->wait->timeout error
t4  commit or rollback or retry t3

T1获得超时错误，因为它不能捕获T2在T2尚未释放的写锁定，然而，如果T1在t4提交它导致T1的部分更新，即行1被更新，但行2没有，因此ACID的原子性规则被这种做法所违反。

T1 gets timeout error at t3 because it can't grap the write lock on row 2 that T2 doesn't release yet, nevertheless, if T1 commits at t4 it leads to "partial" update for T1, i.e. row 1 is updated but row 2 not, thus the "atomicity" rule of ACID is violated by this practice.

根据ACID的原子性规则，事务应该完成成功或失败，

According to the "atomicity" rule of ACID a transaction should either "complete" succeed or fail but not partially.

APP必须在t3收到错误时，请求T1回滚或重试超时更新，直到在t4提交之前成功，从而实现原子性规则。

APP must either request T1 to rollback or retry the timed out update till success before commit at t4 upon receiving the error at t3, thus achieve the atomicity rule.

2）

time    T1              T2

t1  update row 1->OK
t2                  update row 2->OK
t3  update row 2->wait
t4                  update row 1-> DB detects deadlock then forces T2 rolled back
      wait->OK

仅向APP传递超时错误，并且它由APP决定回滚T1，但是在2）中不仅DB检测到死锁错误，而且还用于滚动将要死锁T2。

In 1) DB only delivers the timeout error to APP and it is up to APP that decides to rollback T1 or not, but in 2) not only DB detects the deadlock error but also acts to rollack the would-be deadlocker T2.

理论上，在1）DB也可以回滚T1，但在2）DB可能只会取消会导致死锁，然后提供死锁错误到APP的操作，它是由APP

Theoretically, in 1) DB can also act to rollback T1, but in 2) DB might only cancel the operation that would cause a deadlock then deliver deadlock error to APP, and it is up to APP that decides to rollback T2 or not.

问题在于什么样的具体条件匹配DB选择是APP还是自己应该在检测到错误时处理回滚

The question lies on what concrete conditions are matched for DB to choose whether APP or itself should handle rollbacks when errors are detected firstly on DB level.

推荐答案

回滚应始终由客户端应用程序处理，不数据库。客户端可以作为单个工作单元执行许多不同的操作，因此，客户端应当控制何时将该工作发送到数据库或回滚。

Rollbacks should always be handled by the client application, not the DB. The client may be performing many different operations as a single "unit of work", therefore, the client should have control over when that work is comitted to the database or rolled back.

参考

您可以参考此有用的链接从Tom Kyte，谁感到这么强烈的这个问题，他甚至建议删除提交/回滚从PL / SQL（Oracle的过程语言;我知道你的数据库是mysql，但概念保持不变。）

References
You can refer to this helpful link from Tom Kyte, who feels so strongly about this issue that he's even suggesting removing commit/rollback from PL/SQL (Oracle's procedural language; I know your DB is mysql, but the concept remains the same).

另一个令人信服的原因
CLIENT APPLICATION，唯一能够真正控制事务
流程的
应该是

another compelling reason for the CLIENT APPLICATION, the only thing that can really control transaction flow, should either

a）commit或b）rollback

a) commit or b) rollback

它的工作。 （与触发器，
自动事务和
其他时，我将删除commit
和回滚在plsql如果我有我的方式
：）

its work. (along with triggers, autonomous transactions and when others, I would do away with commit and rollback in plsql if I had my way :)

这篇关于哪个应该采取主动任务回滚，App或DB？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！