Jay Roy
asked on
Commiting after every few inserts
hi guys
I am trying to understand a store procedure. This is what i have:
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
>>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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.