Cross database transactions - failover between commit?

  • 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

  • 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

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

    Jared
    CE - Microsoft

  • 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

  • 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

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

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