Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cross database transactions - failover between commit? Expand / Collapse
Author
Message
Posted Wednesday, December 18, 2013 8:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:58 AM
Points: 2,706, Visits: 3,426
This may be an unlikely case, but we are trying to figure out what happens if we have a cross database transaction similar to below and then issue a commit and the server fails over after commiting to one log yet before commiting to the second:

BEGIN TRAN

INSERT INTO TEST1.dbo.employee (lastname, firstname)
SELECT 'Karney','Jared'

INSERT INTO TEST2.dbo.employeeaddress (empId, number, street, city, state, zip)
SELECT 1, '1122','N. Hostess Ave.', 'Chicago', 'IL', '60000'

COMMIT

Based on my knowledge, if the server went offline or failed over at this exact moment between "commit" being written to one log and then the other, upon startup only 1 database would show the inserted row. This is because 1 insert has been hardened to the log while the other would rollback on startup. Is this correct?


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1524171
Posted Wednesday, December 18, 2013 10:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
While I think what you are asking IS physically possible, the timing to achieve it is astronomically difficult. In short, I wouldn't even bother to worry about it.

I view this as an academic discussion as opposed to something that any of us are likely to ever encounter..

CEWII
Post #1524228
Posted Wednesday, December 18, 2013 11:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:58 AM
Points: 2,706, Visits: 3,426
Theoretically, which log would get the commit first? Or does the transaction manager apply the command at the same time?

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1524266
Posted Wednesday, December 18, 2013 3:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
SQLKnowItAll (12/18/2013)
Theoretically, which log would get the commit first? Or does the transaction manager apply the command at the same time?

I don't think we can answer that question, but I don't think we can claim SAME time, there would almost certainly be a lag of some kind between the two. My point is this is an academic discussion. The timing required to experience this is EXTREMELY hard to accomplish. Given that I just don't think its likely to happen so I think I'll bow out.

CEWII
Post #1524387
Posted Wednesday, December 18, 2013 8:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:58 AM
Points: 2,706, Visits: 3,426
It becomes important when the two databases are set up in availability groups interesting, huh?I know these don't 'support' these kinds of transactions... but what do they do then?

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1524430
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse