Unanswered: Use TABLOCKX to control data export consistency?
SQL Server 2005
For some reason I'm not finding this addressed in the literature--which makes
me think I'm on the wrong track.
I'm exporting data to file for transfer to another system--looking for adds to a table since the last export date. The source data is ~900MM rows at this time and has been broken into 2 tables which are joined in a view. Data inconsistencies have been a problem and there are other users and processes that may be accessing the table with Inserts or Updates when the data export runs. Not sure if these can be a factor.
Data for the export is staged in a table so the actual query is something like:
WHERE InsertDate > <somedate>
Is there any advantage to adding a WITH (TABLOCKX) table hint here? I want to make sure nothing else is altering data. Reads could still be supported if there is an option for that.
TABLOCKX should try to get an exclusive lock, which would prevent readers from getting to the table. If you have Enterprise Edition, I would look into using Snapshot Isolation for the connection that does the build of the staging table. This will temporarily store the before images of any pages in tempdb while rows are updated in the table. Your process will read the before images from tempdb, and whatever has not changed in the table. Depending on how many updates are made to the table, you may need to expand tempdb somewhat.
Thanks, and, I'm assuming my datetime value, cut as the last statement prior to my run of the select statement for the export will be good enough for picking up next week's export where this one left off? Or perhaps it would be better to use the MAX(InsertDate) from the exported data set?
That may depend on the rate of insertions. How big of a problem is it, if there is a delay in setting up the snapshot, and a few records are either included twice, or not included? If resources are not a major problem, I would probably go for the max(Insertdate) from the previous export.
Another thing to look into is CDC (Change Data Capture), if you have SQL 2008. Again, this may depend on rates of change, and instead of tempdb, it may require Transaction Log space. I have not had the chance to play with it, so I should not say a whole lot.