Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: how to use locked status as select condition?

    for example:
    select * from tab_name where rownum=1 and (not be locked by others);
    so, how to express the condition(not be locked by others) in sql?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I understood well, you'll have to use the SELECT FOR UPDATE statement.
    When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is a useful feature because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records which have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the “before image” of the data).

    There are times, however, when you will want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking.

    When you issue a SELECT...FOR UPDATE statement, the RDBMS automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, holding the records “for your changes only” as you move through the rows retrieved by the cursor. No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT.
    An example would be this:
    Code:
    select empno, ename, sal
    from emp
    where deptno = 10
    for update of sal;
    The OF list of the FOR UPDATE clause does not restrict you to changing only those columns listed. Locks are still placed on all rows; the OF list just gives you a way to document more clearly what you intend to change. If you simply state FOR UPDATE in the query and do not include one or more columns after the OF keyword, then the database will then lock all identified rows across all tables listed in the FROM clause.

  3. #3
    Join Date
    Mar 2004
    Posts
    5

    thank you, Littlefoot

    but you dont catch me.
    for example detailed:
    in table tab_name, there are a lot of records. and many people(not one) process these records one by one.
    person Tom get a connection to db, and person Mike also get one.
    Tom issue a sql: select * from tab_name where rownum=1 for update; he just need one record to process for this time.
    then, Mike alse issue the same sql: select * from tab_name where rownum=1 for update; he also want get a record from table to process.

    problem rise: because Tom process his job with a long time, and he locked a record, then Mike(issue sql a little later) will not get select return until Tom finish his work.
    so i think if there is a sql like this:
    select * from tab_name where rownum=1 and (record not locked by other peron);
    thus Mike can get another record(it isnt processed by other person on the same time);

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    This is a script that I use to show me what locks are currently in place on the database - maybe you could chop it & change it to help you get the info you're after...
    Attached Files Attached Files
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The other alternative is to use select...for update nowait and catch the exception if someone has locked that record.

    Alan

  6. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    This will help you even more (& perhaps go so far as to answer your question in full):

    http://www.jlcomp.demon.co.uk/faq/locked_rows2.html

    HTH!
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •