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:
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
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!