Transaction on two tables at the same time in two different databases

Question

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

I consider following scenario:

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).

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

Jackiejackinoffice

maybe if you do the second insert like this "insert into D2.T1" then you can do both inserts statements in one call – Wexford

You will have to manage a transaction against each database. – Rimarimas

I don't understand your answers. Guys, can you be more precisely ? – Jackiejackinoffice

I dont know if this solves the transaction problem you have, but you can insert in a table from database D2 while connected to database D1. To do that, just add the database name and 2 periods before the tablename. Example insert into db2..table1 – Wexford

Answer 1

No, as explained here.

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

• Insert the data in database 1 (with transaction)
• Create a cron job which reads the data from database 1 and updates database 2
• Make sure that your job can be run again (so it should either always copy all the data or it should remember the last row it has processed).

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.

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).

Binary

Answer 2

SQL Server provides the ability to manage transactions distributed across databases and/or servers. You start the distributed transaction with BEGIN DISTRIBUTED TRANSACTION

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-distributed-transaction-transact-sql

Karena