Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    1

    Unanswered: select for update

    Hello,

    I am very new to SQL and DB2. I am trying to use the select for update sql command to read a row and temporarily lock it so that another transaction cannot access that row. However, it appears that other transactions are reading a row that is locked and should not be read, and it is causing errors. To be specific, here is what I want to do:

    • My Java servlet is invoked via a web request.
    • The servlet sends a 'select for update' statement to the DB2 table to read a row and temporarily lock it
    • The servlet then immediately sends another request to the DB2 table to execute an update command on one of the columns (a flag that indicates that row is in use)
    • The servlet then continues processing
    • The sevlet completes processing and sends another update statement to the DB2 table to release that row (update the flag column to indicate no longer in use)


    The problem comes between the 2nd and 3rd bullets. If another transaction comes in after the 'select for update' statement but before the in-use-flag is updated, it appears to be reading the same row that should have been locked.

    Could it be that the lock only prevents the row from being updated, but still allows another transaction to read the locked row?

    I hope I made a little sense. Any help will be greatly appreciated.

    Thanks,
    lugos

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by lugos
    Could it be that the lock only prevents the row from being updated, but still allows another transaction to read the locked row?
    That may very well be the case.

    See if the following makes sense:

    http://www.dbforums.com/t1202151.html

    http://www.dbforums.com/t862330.html

  3. #3
    Join Date
    Oct 2005
    Posts
    109
    Check the documentation for isolation levels.
    You might want to upgrade to RS or RR (default is CS cursor stability, which only locks the row at hand).
    Juliane

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A lock generated by a select is compatible with a lock generated by select for update. So if you want the other application to wait if someone else has a select for update lock, then make the application also do a select for update, even if they have no intention of udpating the row.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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