1

I have a table that is constantly updated by some scripts. The scripts are adding new values and changing tuples but never deleting any tuple.

I need to run some queries, that use all rows in the table (about 2 million at the moment) for joining. The Problem is that the scripts lock the table in a way, that my select statements are blocked and are executed very slowly (compare 16minutes vs a few seconds if running without any script in the background).

Is it possible to lock the table in a way, that i read a current snapshot and do not care about changes? For most applications it is sufficient to allow that ghost entries are present (data is changed/added while the select query was running). It is not necassary in the most cases to lock the whole table, but rather use a column lock (which i think will not work on my MS SQL Server 2005)

3

Sounds like you want to consider READ COMMITTED SNAPSHOT ISOLATION, which uses tempdb to provide a point-in-time version of the data and prevents writers from blocking readers and vice-versa (as long as it's ok that readers do not see the most current version of the row). With a table that large and having this much of an impact by concurrent write operations, the impact to tempdb could be huge, and there's no way for us to predict it, so you will need to test it.

Kendra Little has a great intro post from the administration side here:

And Paul White goes into detail about what happens at the query level here, in his series on ACID and the various transaction isolation levels in SQL Server:

Official documentation which you'll want to review, especially to understand how it works and the limitations involved:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.