How Many Rows Inserted?

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  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

• Rune Bivrin

  SSCrazy Eights

  Points: 8590

  More actions

  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.

• Christoph Muthmann

  Ten Centuries

  Points: 1190

  More actions

  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

• Carlo Romagnano

  SSC-Insane

  Points: 22747

  More actions

  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.

  • This reply was modified 4 years, 2 months ago by  Carlo Romagnano.
  • This reply was modified 4 years, 2 months ago by  Carlo Romagnano.

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  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