• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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

0
ewilde
Asked:
ewilde
  • 2
1 Solution
 
momi_sabagCommented:
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
0
 
ewildeAuthor Commented:
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,
0
 
ewildeAuthor Commented:
momi_sabag,

i found a solution!

the scenario i was  afraid of is possible. it dose not help that the update statement locks the records. another transaction who read the same data under read-uncommitted isolation level would ignore this lock. BUT!: combining read-committed along with READPAST, makes it possible to update all-or-nothing while never being blocked, which  is exactly what i need:

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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now