1

I was asked a question recently during an interview. Question is:

I have two different databases and I need to update tables on both databases. We want data consistency. I updated table in first database, then while updating second database I got an error. So what can I do in this situation?

I thought to take all in a transaction and if I got an exception I tought that I could rollback. But they said because of having 2 different databases, it is not possible to take them in same transaction. I am not sure if I understand correctly but I could not find other solution.

Now here what I am asking.

1) Is it possible to take two query for two different databases in a single transaction? As I read in some posts it is possible but I could not be sure after what they said.

2) If it is not possible to manage them in a transaction how can I rollback changes in the first database.

user1474111
  • 1,356
  • 3
  • 23
  • 47
  • It's a bit of a rubbish question in my opinion. I'd probably go along the lines of... I wouldn't commit either session until all steps are completed due to this restriction. For this reason I would then just rollback db1 as db2 wouldn't have changed due to the failure in the update statement. – bob dylan Mar 21 '16 at 13:05
  • read about database links, XA (distributed transactions). It is quite broad topic. – ibre5041 Mar 21 '16 at 13:32

1 Answers1

0

I think you can do a cross DB operation in procedure and wrap both the UPDATE statement in a transaction block like

create procedure usp_update
as
begin
begin trans
update table1 set col1=value where col2=value;
update db2..table1 set col1=value where col2=value; //cross DB update
commit;
if(error)
rollback;
end
Rahul
  • 76,197
  • 13
  • 71
  • 125