asked on

Commiting after every few inserts

hi guys

I am trying to understand a store procedure. This is what i have:

CREATE PROCEDURE FINAL_SP
AS
DECLARE @BatchSize INT = 500
DECLARE @MyRowCount INT
WHILE 1 = 1
BEGIN    
  BEGIN TRAN
    INSERT INTO Final WITH (TABLOCK)
(ID,NAME,LEG_IDF) 
 SELECT TOP(@BatchSize)
 ID,Name,LEG FROM FINAL_ST WHERE NOT EXISTS(select 1 from Final where Final.checksum = FINAL_ST.checksum)

set @MyRowCount = @@ROWCOUNT
 COMMIT TRAN
    IF @MyRowCount < @BatchSize 
    BREAK    
END
GO

Open in new window

There are totally 300,000 rows which get inserted in the Final table.
I want to make sure that COMMIT happens once every 500 rows are inserted into the Final table.
so to test it I am running this sql

select COUNT(*) from Final
>>I dont see any data.

>>select COUNT(*) from Final(nolock)
I see 50000 rows.

So my conclusion is if the COMMIT was really working , then

select COUNT(*) from Final
should have shown me some data.

So Am i right to say that the COMMIT is not happening after every 500 rows ?

I will greatly appreciate any response.

Thanks.

SOLUTION

QuinnDex

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

>> So Am i right to say that the COMMIT is not happening after every 500 rows? <<

No.

The COMMIT will commit the existing transaction and free all existing locks from that transaction.

But then the code immediately begins another transaction and INSERT, which takes new locks.

Btw, be sure you have an index on Final.checksum to get decent performance.

Jay Roy

ASKER

>>you are locking Final with the statement WITH (TABLOCK) this will stay locked until all rows are inserted
But TABLOCK is a shared lock which is released after the insert statement finishes execution, so after every 500 inserts, this TABLOCK will be released and data COMMITTED.
so i should be able to see the committed rows using
select COUNT(*) from Final

Is my understanding correct ?

Thanks.

Jay Roy

ASKER

ScottPletcher, just saw your response. I see what you are saying.

>>But then the code immediately begins another transaction and INSERT, which takes new locks

So are you saying that the COMMIT is happening. Is there a way to actually see that COMMIT is taking place?

thanks.

Scott Pletcher

Indirectly you can.

Start the INSERT loop running. Then after a few minutes open another query session in that db, and type in this command:

DBCC OPENTRAN

That will show the oldest transaction still active: if it's later than shortly after you started the loop, you know the older transaction(s) have committed already.

You could also run SQL Profiler and view the COMMIT being issued.

Also, you should get an error if the COMMIT statement fails for any reason.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial