Nested Transactions

  • Comments posted to this topic are about the item Nested Transactions

  • 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]

  • Nice start to the week.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Nice question on basics...

  • Good one....

  • Like the basic question.

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

  • Very good QOTD Tim. 🙂

  • Very good question ...

    very handy to understand the working nested transaction and SAVE point

  • 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

  • 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?

  • 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 🙂

  • 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

  • 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.

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

  • 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 25 total)

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