Post reply

How Many Rows Inserted?

  • March 18, 2021 at 12:00 am

    #3856593

    Comments posted to this topic are about the item How Many Rows Inserted?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 18, 2021 at 7:46 am

    #3859296

    Interesting.

    How I parsed this is :

    • The explicit BEGIN TRAN increases the @@TRANCOUNT to 1.
    • The INSERT increases it to 2.
    • The COMMIT decreases it to 1
    • The SELECT ... FROM #table doesn't affect the count, as it is already > 0
    • The ROLLBACK decreases it back to 0

    The following SELECT ... FROM #table increases it to one again, so the code is actually left with a dangling transaction, which might be why you got 1 after SET IMPLICIT_TRANSACTIONS ON if you ran the code more than once.

    Just because you're right doesn't mean everybody else is wrong.

  • March 18, 2021 at 7:49 am

    #3859297

    Same for me!

    Just try to insert a

    Set implicit_transactions off;

    just after the Rollback;

    Have a nice day,Christoph

  • March 18, 2021 at 9:05 am

    #3859323

    "In this code, the SET IMPLICIT TRANSACTIONS ON sets a @@trancount of 1. I am not sure why this occurs, but it means that after the COMMIT, there is still a transaction that is affected by the rollback. At the end, there are 0 rows in the table."

    The SET IMPLICIT TRANSACTIONS ON DOESN'T modify @@trancount. So, as from BOL, the first IO sets the @@trancount to 1. In this case BEGIN TRAN is the first IO (@@trancount=1) and BEGIN TRAN increments @@trancount (now it values 2). The rollback restore all data (also nested transaction) and sets @@trancount to 0.

  • March 25, 2021 at 9:43 pm

    #3862206

    Per Paul Randal in https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/

    The commit of a nested transaction has absolutely no effect – as the only transaction that really exists as far as SQL Server is concerned is the outer one. Can you say ‘uncontrolled transaction log growth’? Nested transactions are a common cause of transaction log growth problems because the developer thinks that all the work is being done in the inner transactions so there’s no problem.

    The rollback of a nested transaction rolls back the entire set of transactions – as there is no such thing as a nested transaction.

    Your developers should not use nested transactions. They are evil.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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