asked on

manual locking (SQL Server 2005)?

i simplified my situation to the following case:
i have a table1:
recid | lock
1 | 0
2 | 0
3 | 0
4 | 0

i want to update (set lock=1) two records in this table, but only if BOTH of the two specified recids are unlocked (lock=0).
it must be done without transactions.
the isolation level must be read uncommitted.

i thought about each of the following update statements:
A)
update table1 set lock=1 where recid in(
select recid from table1 where recid in (2,3) and
(select count(*) from table1 where recid in (2,3) and lck=0) = 2
)

B)
update table1 set lock=1 where recid in (2,3) and
(select count(*) from table1 where recid in (2,3) and lock=0) = 2

(i wondered whether both A and B are actually identical, although their execution-plans are different)

is A and\or B are really 100% guaranteed in a multi-users environment?

i was wondering about two users, the first user is trying to lock recids 2 and 3, while the second is trying to lock recids 3 and 4.
i afraid that the following sequence may happen:
1. the server gets both of the queries on the same time.
2. for the first query, the server verifies that: (select count(*) from table1 where recid in (2,3) and lck=0) = 2
3. as a result of parallel processing, the server starts and finishes the second query: updating recids 3 and 4
4. now, the server continues the processing of the first query: updating recid 2 only! (since recid 3 has just
been already updated by the second query)

the result is that each of both the first user and the second users "think" that recid 3 is locked by him...

thanks in advanced!
ewilde

momi_sabag

i think you should pick option B since option A is less clear
but i do think that both of them are logically identical
anyway,
you don't have to fear the scenario you described since you are doing an update statement, so locks will be taken by sql server. You should look at each statement as an atomic statement, that is, the select part of the update is not separated from the update itself

i would not use uncommitted read here, just to be on the safe side

ewilde

ASKER

momi_sabag,
thank you very much for your answer.

but as i emphasized, i need the isolation level to be read-uncommitted.

if there is, after all such a problem, it would be very difficult to reproduce, that's why i would be glad to have a link to an official-microsoft-resource that claims that in such a situation, i indeed, still be on the safe side while in read-uncommitted isolation level.

thanks again,

ASKER CERTIFIED SOLUTION

ewilde

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial