Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: How to realize "select ... for update NOWAIT" ?

    For Oracle, I can use
    "select ... for update NOWAIT"
    to attempt to acquire a lock so that once succeeded, update from other session are prevented. Also, if this attempting fails. the statement will return instantly without being blocked.

    For DB2, although I can use
    "select ... for update with RR/RS" etc, I can not add "NOWAIT" option to prevent blocking.

    Does anyone know how to achieve the same effect?

    PS. I actually want to use "select ... for udpate" and "select ... for update NOWAIT" in a same application. So a session should support both.

    Thanks.

    -Xun Zhang

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Can you explain "without being blocked" in more detail.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2003
    Posts
    16
    Originally posted by Marcus_A
    Can you explain "without being blocked" in more detail.
    Suppose steps below:
    1. begin TX1
    2. in TX1, do
    update table1 set a = 1;
    3. begin TX2
    4. in TX2, do
    select * from table1 for update nowait;

    Since TX1 is holding a lock, so TX2 failed to acquire a lock. Then the "select * from table1 for update nowait" will return with an error. I called it "noblocking mode"

    On the other side, if in step 4 do
    select * from table1 for update
    instead. the TX2 will keep waiting until TX1 committed or rollbacked, then acquire the lock, then return successfully. I called it "blocking mode"

    So my question is:
    Do DB2 suppport a "noblocking mode" select ... for update?

    Thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Be careful when using the term "blocking," because it is used in DB2 to refer to cursor operations for remote clients that return multiple rows (a block) at one time to the buffer, even though they are actually selected one at a time by the application. This cuts down on communication/network time between client and server for cursor operations.

    Now to your question, which deals with locks, not blocking.

    The DB2 timeout parameter called locktimeout is set at the database level.

    This parameter specifies the number of seconds that an application will wait to obtain a lock. If you set this parameter to 0, locks are not waited for. In this situation, if no lock is available at the time of the request, the application immediately receives a -911.

    If you set this parameter to -1, lock timeout detection is turned off. In this situation a lock will be waited for (if one is not available at the time of the request) until either of the following:

    - The lock is granted
    - A deadlock occurs

    To the best of my knowledge, this cannot be changed at the SQL statement level.

    When you issue a select column-names from table1 for update, DB2 will take an SIX (intent to exclusive) lock. No one else can take another SIX lock or an X lock (exclusive for immediate update), but others can obtain read locks. Any else trying to take an SIX or X lock will wait for the time specified in the locktimeout parameter mentioned above for all SQL statements in the database.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2003
    Posts
    16
    Originally posted by Marcus_A
    The DB2 timeout parameter called locktimeout is set at the database level.
    Thank you very much. Let me confirm one thing:
    Since locktimeout is not a statement level parameter, so I can not have one transaction do "select for update" with locktimeout=0 and at the same time, have another transaction do "select for update" with locktimeout = -1.
    Is that understanding right?

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, that is my understanding.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can set the locktimeout parameter to a few seconds, and then for those applications where you want to wait more than a few seconds, the application that receives a -911 can keep trying a specified number of times until you want to give up.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Nov 2003
    Posts
    16
    Originally posted by Marcus_A
    You can set the locktimeout parameter to a few seconds, and then for those applications where you want to wait more than a few seconds, the application that receives a -911 can keep trying a specified number of times until you want to give up.
    Yes. that is a possible workaround.
    But since I am actually using JDBC to execute statements, the performance may be downgraded. I need to confirm that.

    Anyway, thanks for help and good idea.

    BTW, do you know whether I can call UDF in tigger or not? I posted the question in another thread. This UDF is created by Java sending some cache invalidation messages to Web Application via Socket.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Not sure about the UDF. Did you check the manuals?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Nov 2003
    Posts
    16
    Originally posted by Marcus_A
    Not sure about the UDF. Did you check the manuals?
    UDF is Use Defined Function, i.e. Function.
    I created a Function using Java, do u know if I can call it from a trigger?
    Thanks for keeping this talk on.

Posting Permissions

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