Transactions

Question

Post reply

Transactions

IT researcher

SSCertifiable

Points: 7567
More actions
March 5, 2013 at 9:56 pm

#269286

Is it possible to update 2 Db with 2 different sqlConnections under same Transaction ?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

Not under the same transaction, but you can manage that on your own.

Connection 1: BEGIN TRAN

Connection 2: BEGIN TRAN

Connection 1: Modify some data

Connection 2: Modify some data

-- If modifications on connection 1 and 2 both succeed without error --

Connection 2: COMMIT TRAN

Connection 1: COMMIT TRAN

-- If modifications on connection 1 or 2 both encounter an error --

Connection 2: ROLLBACK TRAN

Connection 1: ROLLBACK TRAN

You could also look into leveraging the DTC (Distributed Transaction Coordinator). It is much more robust and does similar steps to what I have shown for you automatically. It can also work across different physical servers as well, and not only for database operations.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

this can put more light here

CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';

CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';

GO

CREATE USER user1 FOR LOGIN login1;

CREATE USER user2 FOR LOGIN login2;

GO

begin tran t

GRANT IMPERSONATE ON USER:: user2 TO user1;

SELECT SUSER_NAME(), USER_NAME();

EXECUTE AS user = 'user2';

REVERT;

GRANT IMPERSONATE ON USER:: user1 TO user2;

SELECT SUSER_NAME(), USER_NAME();

EXECUTE AS user = 'user1';

revert;

rollback tran t

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

IT researcher SSCertifiable Points: 7567 More actions · Answer 3

if connection 2: COMMIT TRAN is done initially (in code)

and some err in connection 1

then connection 2: cannot not be rolled back right ?

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

winmansoft (3/6/2013)
if connection 2: COMMIT TRAN is done initially (in code)
and some err in connection 1
then connection 2: cannot not be rolled back right ?

Correct. That's why you do leave TRAN on Conn 1 open while you do work on Conn 2, and then issue the commits one after the other. If the work succeeded, the COMMIT is almost guaranteed to succeed.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato