Nested Transactions

  • timwell

    SSCarpal Tunnel

    Points: 4948

    Comments posted to this topic are about the item Nested Transactions

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question on the fundamentals ..

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Nice start to the week.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Bangla

    Hall of Fame

    Points: 3137

    Nice question on basics...

  • Anipaul

    SSC-Insane

    Points: 24681

    Good one....

  • free_mascot

    One Orange Chip

    Points: 27168

    Like the basic question.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • sqlnaive

    SSCoach

    Points: 17435

    Very good QOTD Tim. 🙂

  • twin.devil

    SSC-Insane

    Points: 22208

    Very good question ...

    very handy to understand the working nested transaction and SAVE point

  • TomThomson

    SSC Guru

    Points: 104765

    Nice question.

    Has one small problem though: the code leaves a transaction open - nothing is actually committed.

    It's clearly stated in BOL:-

    Although each COMMIT TRANSACTION statement has a transaction_name parameter, there is no relationship between the COMMIT TRANSACTION and BEGIN TRANSACTION statements. The transaction_name parameters are simply readability aids to help the programmer ensure that the proper number of commits are coded to decrement @@TRANCOUNT to 0 and thereby commit the outer transaction.

    Thus the COMMIT TRAN A stement simply decrements the trancount to close the innermost the innermost open transaction since there are still two transactions open when it is encountered: the transaction named A and the transaction named B, since ROLLBACK TRAN B rolled back to the savepoint labelled B, and that savepoint includes some work done in teh transaction named B. The transaction named A, which is the outermost transaction, remains uncommitted.

    Tom

  • marlon.seton

    SSCrazy

    Points: 2563

    L' Eomot Inversé (10/28/2013)


    Nice question.

    Has one small problem though: the code leaves a transaction open - nothing is actually committed.

    It's clearly stated in BOL:-

    Although each COMMIT TRANSACTION statement has a transaction_name parameter, there is no relationship between the COMMIT TRANSACTION and BEGIN TRANSACTION statements. The transaction_name parameters are simply readability aids to help the programmer ensure that the proper number of commits are coded to decrement @@TRANCOUNT to 0 and thereby commit the outer transaction.

    Thus the COMMIT TRAN A stement simply decrements the trancount to close the innermost the innermost open transaction since there are still two transactions open when it is encountered: the transaction named A and the transaction named B, since ROLLBACK TRAN B rolled back to the savepoint labelled B, and that savepoint includes some work done in teh transaction named B. The transaction named A, which is the outermost transaction, remains uncommitted.

    Will dropping the table remove the open transaction on the table?

  • sqlnaive

    SSCoach

    Points: 17435

    L' Eomot Inversé (10/28/2013)


    Nice question.

    Has one small problem though: the code leaves a transaction open - nothing is actually committed.

    It's clearly stated in BOL:-

    Although each COMMIT TRANSACTION statement has a transaction_name parameter, there is no relationship between the COMMIT TRANSACTION and BEGIN TRANSACTION statements. The transaction_name parameters are simply readability aids to help the programmer ensure that the proper number of commits are coded to decrement @@TRANCOUNT to 0 and thereby commit the outer transaction.

    Thus the COMMIT TRAN A stement simply decrements the trancount to close the innermost the innermost open transaction since there are still two transactions open when it is encountered: the transaction named A and the transaction named B, since ROLLBACK TRAN B rolled back to the savepoint labelled B, and that savepoint includes some work done in teh transaction named B. The transaction named A, which is the outermost transaction, remains uncommitted.

    Eagle eye L'eomot.

    I checked the transaction level one by one and here is what i found:

    BEGIN TRAN A

    INSERT INTO TranTable (col) Values ('abc')

    select @@TRANCOUNT as 'trancount 1' -- 1

    BEGIN TRAN B

    INSERT INTO TranTable (col) Values ('def')

    select @@TRANCOUNT as 'trancount 2' -- 2

    SAVE TRAN B

    select @@TRANCOUNT as 'trancount 3' -- 2

    BEGIN TRAN C

    INSERT INTO TranTable (col) Values ('ghi')

    select @@TRANCOUNT as 'trancount 4' -- 3

    COMMIT TRAN C

    select @@TRANCOUNT as 'trancount 5' -- 2

    ROLLBACK TRAN B

    select @@TRANCOUNT as 'trancount 6' -- 2

    INSERT INTO TranTable (col) Values ('xyz')

    COMMIT TRAN A

    select @@TRANCOUNT as 'trancount 7' -- 1

    GO

    The following link states that "ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT."

    http://technet.microsoft.com/en-us/library/ms181299(v=sql.105).aspx

    Really important point L'eomot. Cheers 🙂

  • TomThomson

    SSC Guru

    Points: 104765

    marlon.seton (10/28/2013)


    Will dropping the table remove the open transaction on the table?

    No - the transaction is still open, and the table is not dropped until the transaction is committed: subsequent commands within the same transaction can't see the table, attempts in another context to access the table will hang up because the uncommitted transaction which has issued the DROP tatement has an exclusive lock (not a data lock, this one will hold up an access attempt even if the other connection uses WITH (NOLOCK). If the session is closed without the transaction being committed (eg by the server closing down) the transaction is rolled back (so the table is not dropped).

    Tom

  • marlon.seton

    SSCrazy

    Points: 2563

    L' Eomot Inversé (10/28/2013)


    marlon.seton (10/28/2013)


    Will dropping the table remove the open transaction on the table?

    No - the transaction is still open, and the table is not dropped until the transaction is committed: subsequent commands within the same transaction can't see the table, attempts in another context to access the table will hang up because the uncommitted transaction which has issued the DROP tatement has an exclusive lock (not a data lock, this one will hold up an access attempt even if the other connection uses WITH (NOLOCK). If the session is closed without the transaction being committed (eg by the server closing down) the transaction is rolled back (so the table is not dropped).

    Thanks for the info.

  • steve.jacobs

    SSCommitted

    Points: 1830

    Nice question geared to the fundamentals. I almost got it wrong (reading it over too quickly). Thanks. 🙂

  • sestell1

    SSChampion

    Points: 10230

    L' Eomot Inversé (10/28/2013)


    Nice question.

    Has one small problem though: the code leaves a transaction open - nothing is actually committed.

    It's clearly stated in BOL:-

    Although each COMMIT TRANSACTION statement has a transaction_name parameter, there is no relationship between the COMMIT TRANSACTION and BEGIN TRANSACTION statements. The transaction_name parameters are simply readability aids to help the programmer ensure that the proper number of commits are coded to decrement @@TRANCOUNT to 0 and thereby commit the outer transaction.

    Thus the COMMIT TRAN A stement simply decrements the trancount to close the innermost the innermost open transaction since there are still two transactions open when it is encountered: the transaction named A and the transaction named B, since ROLLBACK TRAN B rolled back to the savepoint labelled B, and that savepoint includes some work done in teh transaction named B. The transaction named A, which is the outermost transaction, remains uncommitted.

    Nice catch Tom!

    "When a transaction begins, resources used during the transaction are held until the completion of the transaction (namely, locks). When part of a transaction is rolled back to a savepoint, resources continue to be held until the completion of the transaction or a rollback of the complete transaction."

Viewing 15 posts - 1 through 15 (of 26 total)

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