SQL Transactions - Allow read source data before commit (snapshot?)
I ran into a problem, maybe quite easy to solve, I'm just new to advanced transaction settings.
Every 30 minutes I run a query INSERT
that gets the latest data from the linked server on my client server to a table that we can call ImportTable. For this, I have a simple job that looks like this:
BEGIN TRAN
DELETE FROM ImportTable
INSERT INTO ImportTable (columns)
SELECT (columns)
FROM QueryGettingResultsFromLinkedServer
COMMIT
The point is that every time the job starts, the ImportTable is blocked for the query execution time (2-5 minutes) and nobody can read the records. I want the table to be always readable, with minimal downtime.
Now I have read what can be resolved SNAPSHOT ISOLATION
in the database settings that might solve my problem (it is currently set to FALSE
), but I have never played with different types of transaction isolation and since this is not my DB but mine client, I'd rather not change any of the database options unless I'm sure if that might break something.
I know that I could have a staging table that records are inserted into and then inserted into the final table and this is definitely a possible solution, I was just hoping for something more complex and learned something new along the way.
PS: My client server and database are fairly new and hardly ever used, so I expect very little impact if I change some of the settings, but still I can't just accidentally change various settings for educational purposes.
Many thanks!
source to share
Inserts usually do not lock a table unless it has gone down to the table level. In this case, you drop the table first and insert the data again, why not insert only the updated data ?. For the query you are using the transaction level (rsci), then you will have an additional impact on the row version, which means that sql will store the rows of rows of rows that have changed in tempdb.
see video of Kimberely tripp MCM isolation for in-depth understanding, also remember to test the environment.
source to share
You are making it harder than it needs to be. The problem is that you think the transaction part is 2-5 minutes It's only a few thousand rows - this part takes a few milliseconds
If you need an ImportTable that will be available within a few milliseconds, put it in a SnapShot
Delete ImportTableStaging;
INSERT INTO ImportTableStaging(columns)
SELECT (columns)
FROM QueryGettingResultsFromLinkedServer;
BEGIN TRAN
DELETE FROM ImportTable
INSERT INTO ImportTable (columns) with (tablock)
SELECT (columns)
FROM ImportTableStaging
COMMIT
If you are concerned about simultaneously updating ImportTableStaging use #temp
source to share