Posted by Erland Sommarskog on 11/03/07 22:13

dhek (dhek@dhek.dk) writes:
> Well, my issue is that I'm reading data from tables A, B, C and D and
> updates table E.
>
> What I need to prevent is 2 things:
>
> 1) New rows must not be added to either table A and B while my transaction
> is executing
> 2) Existing rows must not be modified

I'm not sure that I get this. Assuming you assemble data into a temp
table or a table variable, and you batch goes:

INSERT #tmp(...)
SELECT ....
FROM A, B
...

INSERT #tmp(...)
SELECT ...
FROM C, D

UPDATE E
SET ...
FROM E
JOIN #tmp...

Why would it be an issue if some adds or modifies rows into A or B
once you have run that SELECT statement? I can possibly understand
that you don't want permit rows to be added or modified in C or D while
you are reading A and B. But once you have read A or B, it cannot matter
if modifications happens while your transaction is running, or if they
are held up until your transaction completes.

> My query is executed on a SQL server 2005. I do not have the option to
> change the configuration of it.

Well, if you want to read that is consistent at a certain moment in
time, snapshot isolation is your only foolproof option. It also has the
advantage of not blocking updates.





--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]