Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    7

    Unanswered: DB2 SELECT for UPDATE because of concurrency issue

    Hello,

    We have an online system that reads a DB2 table and gets the next account that needs a manual intervention (status code=02), then it updates the status-code to 03 (work in progress), then it reads a bunch of other data, then it needs to come back to the presentation layer program to commit.

    But before the commit is done, online user #2 comes in and uses the same menu option: get next account, and because the update (user #1) had not been committed yet, user #2 picks up that same account (status code still 02). Concurrency issue here.

    On the SELECT statement that reads the table, I was thinking of adding FOR UPDATE at the end of the SELECT, but i'm a bit worried about the impact.

    What does it do exactly?

    Will a SELECT .. FOR UPDATE lock at the page level? Will it prevent other queries to read?

    If user #2 triggers the same SQL (SELECT FOR UDPATE), will it cause timeouts or will it give him another account (to work on) that satisfies his query?

    I've also read the following:

    To propagate the FOR UPDATE WITH RS to the inner select you have to specify additionally USE AND KEEP UPDATE LOCKS.
    But i'm not too sure what it means



    Thanks
    C

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by cvcv View Post

    On the SELECT statement that reads the table, I was thinking of adding FOR UPDATE at the end of the SELECT, but i'm a bit worried about the impact.

    What does it do exactly?
    If you don't know what it does, why do you think it will help?

    Quote Originally Posted by cvcv View Post
    Will a SELECT .. FOR UPDATE lock at the page level?
    I think page-level locks are only available in DB2 for z. DB2 LUW has row- and table-level locks.

    Quote Originally Posted by cvcv View Post
    Will it prevent other queries to read?
    Not all queries.

    Quote Originally Posted by cvcv View Post
    If user #2 triggers the same SQL (SELECT FOR UDPATE), will it cause timeouts or will it give him another account (to work on) that satisfies his query?
    It will block, unless the lock timeout is set to 0. You can intentionally set it to 0 before running the query and handle the exception appropriately.
    Last edited by n_i; 11-12-13 at 18:06. Reason: I was wrong.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you use SELECT .. FOR UPDATE a lock on the row in question will be held such that another SELECT .. FOR UPDATE on the same row will wait until the first lock is released. A regular SELECT only will not be locked out. The wait time before a locktimeout occurs (-911 reason code 68) is determined by the LOCKTIMEOUT database parm.

    In order to make sure that only the one row is locked, you will need the proper index on the row, and if the table is not large, you can alter it to VOLATILE to force index usage. If the table has relatively few rows, DB2 may do a table scan and lock many rows.

    If the total elapsed time for the transaction is small enough, this should work fine. If there is operator think time involved before you commit, the lock wait time may be too long.

    But I am not sure what you application design is with respect the account rows, when you say "or will it give him another account (to work on) that satisfies his query?"
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Sep 2013
    Posts
    7
    Thanks for your help guys.

    I thought the SELECT for UPDATE would lock, then the next process calling with SELECT for UPDATE would not "see" the locked data.

    I found what I was looking for: SKIP LOCKED DATA

    Here's a link:
    DB2utor: DB2 9 Skipped Locked Data Transactions

    Thanks
    C

Tags for this Thread

Posting Permissions

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