在两个不同的数据库中同时对两个表进行事务 [英] Transaction on two tables at the same time in two different databases

问题描述

我有 spring-boot 应用程序和 mybatis 作为我的 ORM.我也使用 sqlserver 2012.

I have spring-boot app with mybatis as my ORM. I also use sqlserver 2012.

我考虑以下场景:

void foo () {
    set current datasource as Datasource D1 (connected to database D1)
    do some insert on table T1 in database D1
    set current datasource as Datasource D2 (connected to database D2)
    do some insert on table T1 (the same name) in database D2
}

我希望能够确保它总是成功处理两个查询.否则(当其中至少一个失败时)事务将在两个数据库中回滚(我的意思是没有更改)(即使其中一个查询成功).

I would like to be able to ensure that it always succees both queries. Otherwise (when at least one of them fails) transaction will be rollback (I mean no changes) in both databases (even if one of them query succeed).

我认为 foo() 上面的 @Transactional 是不够的.
有没有可能获得这种效果?

I think that @Transactional above foo() is not sufficient.
Is it possible to gain this effect ?

推荐答案

否，如此处所述.

简而言之:无论您尝试什么，在某些极端情况下都行不通.正确的解决方法:

In a nutshell: Whatever you try, it won't work in some corner cases. The correct solution:

• 在数据库 1 中插入数据(带事务)
• 创建一个从数据库 1 读取数据并更新数据库 2 的 cron 作业
• 确保您的作业可以再次运行(因此它应该始终复制所有数据或记住它处理的最后一行).

通过这种方式，您始终可以进行简单的交易.当出现问题时，您可以重新开始该过程.不会丢失数据，最坏的情况是 DB 2 滞后一段时间.

This way, you always have simple transactions. When something goes wrong, you can start the process again. No data loss possible, the worst case scenario is that DB 2 lags behind for some time.

将 DB 1 中的数据插入两个表(您已经拥有的一个表和一个经过优化以供传输作业处理的传输"表)中的数据也可能有所帮助.

It may also help to insert the data in DB 1 in two tables (the one which you already have and a "transfer" table which is optimized for being processed by the transfer job).

这篇关于在两个不同的数据库中同时对两个表进行事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！