Post reply

Commit/Rollback inner transactions

  • July 24, 2009 at 9:00 am

    #137019

    Hi,

    Im trying to get a way for comitting my inner transaction even when the outer transaction is rolled out.

    I know the SQL server database engine does not allow this. But i just want to know is there any work around for this Issue. I know many of us will come across this situation and i would be great full if someone could post a solution for this.

    Regards

    Linz

  • July 24, 2009 at 9:08 am

    #1029762

    The only option you might have is to capture the change in something that doesn't get rolled back (a table variable for example), then apply the change all over again. Otherwise - it comes down to finding a way to change up the transactions so that you can roll back only the part you need rolled back, without rolling the other parts back.

    Meaning: keep the "big" transaction, but reorganize the inner transactions so that the stuff you DO want rolled back is in its own inner transaction (which is presumably at the same level as the other inner transactions you want kept).

    Only use the "big" transaction when you need everything gone.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • July 24, 2009 at 9:19 am

    #1029777

    Thanks Matt,

    I have tried the first option of putting my loggin into a table variable and in the end inserting into the actual table. That works.

    My Issue is with teh secomd part. I cannot put the DDL statements in an individual try catch as i need to rollback all the table creation if any one of the Table creation cause an error.

    To be clear.

    I have 5 table creation statement and if my 5th table creation bring up an error, i need to rollback all the other 4 table creation too.

    That was the reason i kept a rollback in the outer transaction.

    Any more suggestion, always welcome.

    Thanks

    Linz

  • July 25, 2009 at 5:44 am

    #1030135

    Linz,

    It would help if you provided a sample script to illustrate the problem and what behaviour you wish to produce.

    Generally, some combination of nested transactions, save points, and judicious table variable usage can solve most problems of this type.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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