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.
royjaydAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QuinnDexCommented:
you are locking Final with the statement WITH (TABLOCK) this will stay locked until all rows are inserted, so you will not get any rows returned, when you use from Final(nolock) you are over riding the lock and so getting results

also your if statement should have an else clause


IF @MyRowCount < @BatchSize 
   BREAK
 ELSE
    CONTINUE

Open in new window

0
Scott PletcherSenior DBACommented:
>> 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.
0
royjaydAuthor Commented:
>>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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

royjaydAuthor Commented:
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.
0
Scott PletcherSenior DBACommented:
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.
0
Scott PletcherSenior DBACommented:
>> so i should be able to see the committed rows using
select COUNT(*) from Final

Is my understanding correct ?
<<

I see what you are saying.  But SQL's locks are often not that granular/discrete.

In your case, though, they never could be, since you've explicitly told SQL to lock the entire table while the INSERT is running.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.