Table Locking durint Truncate/Insert

Question

Table Locking durint Truncate/Insert

Richard Warr

SSCertifiable

Points: 6966
More actions
October 13, 2011 at 2:49 am

#249169

Hi,
Every week I want to repopulate a table by doing something along the lines of:
TRUNCATE TABLE tblA
INSERT INTO tblA SELECT ... FROM tblB
That works fine (and takes under five minutes) but we also have an automatic process running that sends data from a view looking at tblA to another server. We don't know when this process will run so need to be sure that tblA will always be populated and that this process doesn't kick off in between the TRUNCATE and the INSERT or halfway through the INSERT etc.
Is it as simple as putting the TRUNCATE/INSERT into a transaction? If so, in the extremely unlikely event that the automatic process kicks off, will it just wait until the transaction completes and the tblA is populated?
Many thanks,
Richard

Viewing 4 posts - 1 through 3 (of 3 total)

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

bkubicek SSChampion Points: 10948 More actions · Answer 1

Richard Warr (10/13/2011)
Hi,
Every week I want to repopulate a table by doing something along the lines of:
TRUNCATE TABLE tblA
INSERT INTO tblA SELECT ... FROM tblB
That works fine (and takes under five minutes) but we also have an automatic process running that sends data from a view looking at tblA to another server. We don't know when this process will run so need to be sure that tblA will always be populated and that this process doesn't kick off in between the TRUNCATE and the INSERT or halfway through the INSERT etc.
Is it as simple as putting the TRUNCATE/INSERT into a transaction? If so, in the extremely unlikely event that the automatic process kicks off, will it just wait until the transaction completes and the tblA is populated?
Many thanks,
Richard

I am sure someone will correct me if I am wrong here, but I think you have the right idea although I think you may want to change your truncate table to a delete from tblA inside of a transaction. As long as the delete and the insert are inside of a transactin the transaction will block the other process until it is finished. Now the other process might timeout while it is waiting. You would also want to make sure the other process isn't using a (nolock) hint which would do a dirty read.

Richard Warr SSCertifiable Points: 6966 More actions · Answer 2

Thanks for the reply. I was using the TRUNCATE purely for speed as the table may contain 10 million records in a few months time. There's also less of an impact on the log files.

However, I'll happily change that to a DELETE if it makes things safer.

bkubicek SSChampion Points: 10948 More actions · Answer 3

My concern about the truncate has to do with the truncate command does not log the removing of the records. The truncate command is logged and you can roll it back. Still since the size of your table is so large it would seem best to truncate, so that it is quicker and doesn't take up log space.