Lots of inserts? Part I

Question

Lots of inserts? Part I

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

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

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 1

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: 9996 More actions · Answer 2

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

asutorius SSC Enthusiast Points: 138 More actions · Answer 3

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: 5851 More actions · Answer 4

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 More actions · Answer 5

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

Thank you

djj SSCoach Points: 18831 More actions · Answer 6

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: 148809 More actions · Answer 7

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: 259105 More actions · Answer 8

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: 259105 More actions · Answer 9

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 More actions · Answer 10

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 SSCarpal Tunnel Points: 4356 More actions · Answer 11

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 SSC-Insane Points: 20396 More actions · Answer 12

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 More actions · Answer 13

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: 693 More actions · Answer 14

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: 9996 More actions · Answer 15

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