manual locking (SQL Server 2005)?

Posted on 2009-02-16
Last Modified: 2012-05-06
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!

Question by:ewilde
    LVL 37

    Expert Comment

    i think you should pick option B since option A is less clear
    but i do think that both of them are logically identical
    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
    LVL 5

    Author Comment

    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,
    LVL 5

    Accepted Solution


    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

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now