Summer Sale! All accounts are 50% off this week.

janewinkler's avatar

Transactions with multiple database connections

I'm currently working on an import tool that inserts data into two separate databases. The connections are set accordingly in the config folder. Everything I've come across regarding multiple database connection focuses on interacting with each separately. However, I need to insert into both. I'm wondering how to best implement transactions so that the commit and rollback works for both connections

It seems kind of janky to do this but I can't find anything else on this:

DB::beginTransaction(); //default connection is to db1`
DB::connection('db2')->beginTransaction();

try{
//logic
}catch(){
	DB::rollBack();
    DB::connection('db2')->rollBack();
}

DB::commit();
DB::connection('db2')->commit();
0 likes
10 replies
gitwithravish's avatar

@janewinkler This looks okay to me, but if you want to do this in fewer lines then do this

DB::transaction(function($conn){
    DB::connection('db2')->transaction(function($conn){
        // do your magic here
    });
});
khoibv's avatar

I think this is not right. What happen if DB::commit() is success and DB::connection('db2')->commit() is fail? In that case, we can not rollback in DB1, and consistency was break.

1 like
Tray2's avatar

@janewinkler What database are you using? If you are using Oracle databases you can check out database links. Then you don't have to manually switch between the databases. I think that works in Postgree as well but unfortunately its not supported by MySQL.

IsaiasGonzalezDMC's avatar

Hello I have a question, is it possible to use the beginTransaction as a function? For example

 public function handleProcessDb($conn,$dbOracle){
	 try{
	 DB::connection($dbOracle)->beginTransaction(function($conn){
	 // code of insert or select
	 DB::insert(...);
	 DB::insert(...);
	 DB::insert(...);

	DB::commit();
	});
	}catch(\Exception $e){
	DB::rollback();
	}
}

Please or to participate in this conversation.