Lots of inserts? Part I

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    I expected the answer to be that there was an error because tbl did not exist. Since that wasn't an option, I did some experimenting around the open transactions and got a surprise.

    Here is my full code:

    create table dbo.tbl (x char(5000));

    go

    BEGIN TRAN

    INSERT INTO dbo.tbl VALUES

    ( REPLICATE('a',5000) )

    GO 100

    COMMIT

    GO 2

    select count(*) from dbo.tbl;

    go

    rollback;

    go

    select count(*) From dbo.tbl;

    go

    drop table dbo.tbl;

    Unsurprisingly, the first select returns 100. But the second returns 0! In fact any number below 100 on the GO after the commit results in an empty table. Somehow the COMMIT is being ignored. When I do use 100 then the rollback generates an error that there are no open transactions. Can anybody explain? I also tried just using GO 3 after the insert and the commit only had any effect when it was also 3. 2 left an empty table.

  • George Vobr

    SSCrazy Eights

    Points: 9088

    Thanks Kenneth for the very interesting question and perfect explanations with references. 🙂

  • asutorius

    SSC Enthusiast

    Points: 138

    I received the following reply when running that script against AdventureWorks database.

    Beginning execution loop

    Msg 208, Level 16, State 1, Line 2

    Invalid object name 'tbl'.

    ** An error was encountered during execution of batch. Continuing.

  • edwardwill

    SSCertifiable

    Points: 5036

    asutorius (7/27/2016)


    I received the following reply when running that script against AdventureWorks database.

    Beginning execution loop

    Msg 208, Level 16, State 1, Line 2

    Invalid object name 'tbl'.

    ** An error was encountered during execution of batch. Continuing.

    You have to infer the schema for tbl from the script and create it before running the script.

  • asutorius

    SSC Enthusiast

    Points: 138

    You have to infer the schema for tbl from the script and create it before running the script.

    Thank you

  • djj

    SSCoach

    Points: 18738

    edwardwill (7/27/2016)


    I ran the script and then this

    SELECT COUNT(*) FROM tbl

    Guess what number it returned? (HINT - an integer between 99 and 101)

    Yes you will see the 100, however they are not all committed, or did you close the session and then do the select?

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    tom.w.brannon (7/27/2016)


    I expected the answer to be that there was an error because tbl did not exist. Since that wasn't an option, I did some experimenting around the open transactions and got a surprise.

    ...

    Unsurprisingly, the first select returns 100. But the second returns 0! In fact any number below 100 on the GO after the commit results in an empty table. Somehow the COMMIT is being ignored. When I do use 100 then the rollback generates an error that there are no open transactions. Can anybody explain? I also tried just using GO 3 after the insert and the commit only had any effect when it was also 3. 2 left an empty table.

    Yes, nested transactions[/url] are a trap for the unwary.

    John

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Nice question!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Carlo Romagnano (7/27/2016)


    Waiting for commiting all transactions, in reality, the table is empty for the other connections.

    The table is not empty. The table is locked while the transactions are open.

    If you select the table using with(nolock) - not that I endorse using it - you will retrieve the 100 rows just fine.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    edwardwill (7/27/2016)


    I ran the script and then this

    SELECT COUNT(*) FROM tbl

    Guess what number it returned? (HINT - an integer between 99 and 101)

    But if you query the table from another connection, the SELECT will hang because the transactions are uncommitted. Only when you COMMIT the other 99 transactions (run COMMIT/GO 99 in the original connection), will the SELECT will return 100 rows.

  • chgn01

    Hall of Fame

    Points: 3599

    If I use #tbl for insert values, and use 'dbcc opentran' to see the active transactions, it give me

    No active open transactions.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    can some one tell me why? Thanks.

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • Kenneth.Fisher

    SSCoach

    Points: 19684

    tom.w.brannon (7/27/2016)


    I expected the answer to be that there was an error because tbl did not exist. Since that wasn't an option, I did some experimenting around the open transactions and got a surprise.

    Here is my full code:

    create table dbo.tbl (x char(5000));

    go

    BEGIN TRAN

    INSERT INTO dbo.tbl VALUES

    ( REPLICATE('a',5000) )

    GO 100

    COMMIT

    GO 2

    select count(*) from dbo.tbl;

    go

    rollback;

    go

    select count(*) From dbo.tbl;

    go

    drop table dbo.tbl;

    Unsurprisingly, the first select returns 100. But the second returns 0! In fact any number below 100 on the GO after the commit results in an empty table. Somehow the COMMIT is being ignored. When I do use 100 then the rollback generates an error that there are no open transactions. Can anybody explain? I also tried just using GO 3 after the insert and the commit only had any effect when it was also 3. 2 left an empty table.

    Just wait until part 2!

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Stephanie Giovannini

    SSCertifiable

    Points: 7422

    tom.w.brannon (7/27/2016)


    I expected the answer to be that there was an error because tbl did not exist. Since that wasn't an option, I did some experimenting around the open transactions and got a surprise.

    Here is my full code:

    create table dbo.tbl (x char(5000));

    go

    BEGIN TRAN

    INSERT INTO dbo.tbl VALUES

    ( REPLICATE('a',5000) )

    GO 100

    COMMIT

    GO 2

    select count(*) from dbo.tbl;

    go

    rollback;

    go

    select count(*) From dbo.tbl;

    go

    drop table dbo.tbl;

    Unsurprisingly, the first select returns 100. But the second returns 0! In fact any number below 100 on the GO after the commit results in an empty table. Somehow the COMMIT is being ignored. When I do use 100 then the rollback generates an error that there are no open transactions. Can anybody explain? I also tried just using GO 3 after the insert and the commit only had any effect when it was also 3. 2 left an empty table.

    All 100 transactions are nested transactions. That means the only "real" transaction is the first one. Until the very first transaction is committed, nothing is "really" committed.

    With a nested transaction, each COMMIT "closes" the innermost transaction, but nothing is really committed. Once 100 COMMIT statements have been executed, the outermost "real" transaction commits. At that point, other connections can see the 100 committed rows. In the code posted, it's not possible to commit some of the rows and rollback the rest.

    However, ROLLBACK doesn't work like COMMIT on nested transactions. Any ROLLBACK statement executed in a nested transaction context applies to the outermost transaction. While it takes 100 commits to commit those 100 rows, it only takes one rollback to undo all of it.

    This particular scenario can cause headaches when stored procedures that open explicit transactions call other stored procedures that also open explicit transactions. If the nested stored procedure rolls back, then the transaction in the calling stored procedure is also rolled back. It's why you might see a check of @@TRANCOUNT or XACT_STATE() before the stored procedure calls ROLLBACK (to find out if the transaction was already rolled back).

  • Recce70

    Say Hey Kid

    Points: 676

    All 100 transactions are nested transactions. That means the only "real" transaction is the first one. Until the very first transaction is committed, nothing is "really" committed.

    With a nested transaction, each COMMIT "closes" the innermost transaction, but nothing is really committed. Once 100 COMMIT statements have been executed, the outermost "real" transaction commits. At that point, other connections can see the 100 committed rows. In the code posted, it's not possible to commit some of the rows and rollback the rest.

    However, ROLLBACK doesn't work like COMMIT on nested transactions. Any ROLLBACK statement executed in a nested transaction context applies to the outermost transaction. While it takes 100 commits to commit those 100 rows, it only takes one rollback to undo all of it.

    This particular scenario can cause headaches when stored procedures that open explicit transactions call other stored procedures that also open explicit transactions. If the nested stored procedure rolls back, then the transaction in the calling stored procedure is also rolled back. It's why you might see a check of @@TRANCOUNT or XACT_STATE() before the stored procedure calls ROLLBACK (to find out if the transaction was already rolled back).

    Ditto. I view it as a single connection and a single transaction.

    Now the transaction needs a lot of "Committing", but it's still a single transaction.

    I always view it as only the first BEGIN TRANSACTION starts a transaction and all the rest just increment @@TRANCOUNT.

  • George Vobr

    SSCrazy Eights

    Points: 9088

    chgn01 (7/27/2016)


    If I use #tbl for insert values, and use 'dbcc opentran' to see the active transactions, it give me

    No active open transactions.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    can some one tell me why? Thanks.

    On MS SQL 2014 SP2 I tried this code and the number of transactions

    in the session was correct. Try this:

    USE tempdb;

    GO

    create table #tbl (x char(5));

    go

    BEGIN TRAN;

    INSERT INTO #tbl VALUES

    ( REPLICATE('a',5) );

    GO 3

    select count(*) from #tbl;

    go

    -- DBCC OPENTRAN displays information about the oldest active transaction only:

    DBCC OPENTRAN;

    PRINT 'Open Transactions: ' + CAST( @@trancount as varchar);

    go

    rollback;

    GO

    DBCC OPENTRAN;

    PRINT 'Open Transactions: ' + CAST( @@trancount as varchar);

    go

    select count(*) from #tbl;

    go

    drop table #tbl;

    The list of active transactions, see for example:

    https://www.sqlservercentral.com/Forums/Topic987056-146-1.aspx

Viewing 15 posts - 16 through 30 (of 40 total)

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