CODEWITHSUNDEEP
t-sqltransactionssql-server-2012
Marek Stejskal
Marek Stejskal

Reputation: 2708

SQL Transactions - allow read original data before commit (snapshot?)

I am facing an issue, possibly quite easy to solve, I am just new to advanced transaction settings.

Every 30 minutes I am running an INSERT query that is getting latest data from a linked server to my client's server, to a table 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 thing is, each time the job runs the ImportTable is locked for the query run time (2-5 minutes) and nobody can read the records. I wish the table to be read-accessible all the time, with as little downtime as possible.

Now, I read that it is possible to allow SNAPSHOT ISOLATION in the database settings that could probably solve my problem (set to FALSE at the moment), but I have never played with different transaction isolation types and as this is not my DB but my client's, I'd rather not alter any database settings if I am not sure if it can break something.

I know I could have an intermediary table that the records are inserted to and then inserted to the final table and that is certainly a possible solution, I was just hoping for something more sophisticated and learning something new in the process.

PS: My client's server & database is fairly new and barely used, so I expect very little impact if I change some settings, but still, I cannot just randomly change various settings for learning purposes.

Many thanks!

Upvotes: 3

Views: 1364

Answers (2)

paparazzo
paparazzo

Reputation: 45096

You are making this harder than it needs to be
The problem is the 2-5 minutes that you let be part of a transaction
It is only a few thousand rows - that part takes like a few milliseconds
If you need ImportTable to be available during those few milliseconds then 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 worried about concurrent update to ImportTableStaging then use a #temp

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28930

Inserts wont normally block the table ,unless it is escalated to table level.In this case,you are deleting table first and inserting data again,why not insert only updated data?.for the query you are using transaction level (rsci)snapshot isolation will help you,but you will have an added impact of row version which means sql will store row versions of rows that changed in tempdb.

please see MCM isolation videos of Kimberely tripp for indepth understanding ,also dont forget to test in stage enviornment.

Upvotes: 1

Related Questions