Cross database transactions - failover between commit?

Question

Cross database transactions - failover between commit?

Jared

SSC Guru

Points: 61793
More actions
December 18, 2013 at 8:27 am

#301481

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?
Jared
CE - Microsoft

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

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

Jared SSC Guru Points: 61793 More actions · Answer 2

Theoretically, which log would get the commit first? Or does the transaction manager apply the command at the same time?

Jared
CE - Microsoft

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

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

Jared SSC Guru Points: 61793 More actions · Answer 4

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?

Jared
CE - Microsoft