|
|
Posted by dhek on 11/03/07 11:32
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
My query is executed on a SQL server 2005. I do not have the option to
change the configuration of it.
Am I supposed to use table locks in my query
SELECT A.c1, B.c1, C.c1, D.d1
FROM ...
WHERE ...
WITH (TABLOCK, UPDLOCK, HOLDLOCK)
UPDATE E.1
SET ...
WHERE ...
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns99DD791FBE224Yazorman@127.0.0.1...
> dhek (dhek@dhek.dk) writes:
>> I have 1 SQL statement selecting data from various tables and updating
>> other tables.
>>
>> The question then is how do I prevent other applications from modifying
>> the tables that I'm working on (that is while my transaction is being
>> executed)?
>>
>>
>> I know that the isolation level should be either REPEATABLE READ or
>> SERIALIZABLE. But I need confirmation on if one of these actually solve
>> my
>> issue - prevents other applications/threads from modifying/inserting data
>> into the same tables that I'm working on.
>
> It's difficult to give a single answer, since I don't know your exact
> requirements, so I have to answer in genric terms.
>
> If you want a consistent snapshot of how the data looks in this precise
> moment, the isolation level you should use is snapshot isolation. Snapshot
> isolation is available only in SQL 2005 and later. Furthermore the
> database
> must be configured to permit snapshot isolation. When you have snapshot is
> created when the transaction starts, or at latest when you start to read
> data. If data is updated while your query runs, you will not see these
> updates. This gives you a consistent view - but it may also give you
> outdated data, depending on how you look at it.
>
> On SQL 2000, snapshot isolation is not available, and the only foolproof
> way to get consistent data, is to set the database in single-user mode.
>
> In the default isolation level, READ COMMITTED, if you read the same
> row twice, you may get different results in different accesses. For
> instance, if you run:
>
> SELECT O.OrderID, E.EmployeeID, E.LastName
> FROM Orders O
> JOIN Employees E ON O.EmployeeID = E.EmployeeID
>
> You may see different last names for the same employee ID if the query
> plan uses a loop join, and the last name is updated while the query is
> running.
>
> In the next level, REPEATABLE READ locks are held, and you are guaranteed
> that reading the same row twice will yield the same result. However, if
> the last name of employee 8 was Grψnkjζr when the query started, and
> updated to Gravesen before you have read any orders with employee 8,
> you would see Gravesen in the result set.
>
> SERIALIZABLE adds protection against "phantom insert", so if you read the
> same range twice, you will get the same result. That is, if you run
> SELECT MAX(OrderID) FROM Orders twice in the same serializable
> transaction,
> you will get the same result. But if a order is added after you started
> the
> transaction, but before your query runs, the order will show up.
>
>
> --
> 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
Navigation:
[Reply to this message]
|