Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: SQL for JDBC - how do I lock a row?

    Hi,

    I am connecting to DB2 UDB via JDBC ( Java application.).
    When I try to do a 'select for update' ( autoCommit(false) )
    the AS400 platform tells me to journal the file.

    In an attempt to get around journaling, I am looking for an SQL statement which will place an exclusive lock on the selected row.

    HOW DO I DO THIS?

    Please advise me!

    Many thanks - S.

  2. #2
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37
    SELECT ... FOR UPDATE does not apply any row-level locking explicitely. It rather means that you want to update the result sets of the SELECT statement. WITH RR, on the other hand, will tell DB2 this statement will use RR isolation level to control concurrent access to the target table.

    During the SELECT phase, DB2 will use S (share) row locks to prevent UPDATE, phantom insert, etc. During SELECT phase, the other sessions (concurrent activation groups) can read your table.

    Only when you get to the UPDATE, will DB2 escalate the S row
    lock to X (exclusive) row lock. During UPDATE, the other sessions (concurrent activation groups) cannot read your table.

    So, is there a JDBC-compliant command you can use to force a row-level lock? No. Sorry to disappoint you.

    DB2 will enforce the approriate level of locking. Keep in mind, DB2 tries to maximise the concurrent data access as much as possible.

    To lock the entire table in question, you can use the LOCK TABLE <table name> EXCLUSIVE MODE ALLOW READ. Refer to DB2 UDB for AS/400 SQL Reference for details.

    You may also find this article interesting on DB2/400 concurrent access scenarios:
    http://www.databasejournal.com/featu...0896_2247351_1

    Cheers,

    Julius

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I always hesitate to answer DB2/400 questions, because even though I used it years ago, it is a different animal than the other DB2's. I don't really know how much it has changed since I last used it.

    But in DB2 UDB (which now includes 390, Linux, Unix, and Windows) the statement "'select ... for update ...” (used in a cursor) causes DB2 to issue an SIX lock. An SIX lock is "Share With Intent To Update." This allows other Share locks, but no other SIX locks (and no other X locks). When the “update where current of cursor” takes place, then the X lock is issued. This should be committed as soon as possible to facilitate concurrency with other processes.

  4. #4
    Join Date
    Sep 2003
    Posts
    3
    [QUOTE][SIZE=1]Originally posted by jsasvari
    SELECT ... FOR UPDATE does not apply .............................


    Many thanks for your posting.
    I am currently experimenting with the locks managed during the life of the ResultSet of the 'SELECT FOR UPDATE'.

    As the control we need is to exclude other 'SELECT FOR UPDATE's, the presence of an initial cursor on the queried row seems to lock out a second transaction OK.

    This may be obvious to someone with the experience but new to me!

    Thanks again,

Posting Permissions

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