Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: DB2 Row locking inside procedure.

    Hi,
    We have a situation while locking a row in a stored procedure.
    Our database is running under Oracle compatibility mode in DB2.

    The problem is multiple users are trying to select the same set of records from a table. These records are fetched one by one using a cursor.

    To overcome this, we are selecting a row and locking it using the FOR UPDATE clause, so that others users will not be able to update this row.
    The lock timeout is set to 1, due to which other users are getting the -911 error.

    In the next fetch, an error 501(cursor is closed) is coming, to overcome which we are repoening the cursor and fetching the records again.

    Is there any way to prevent the cursor from closing on the 911 error ?

    The above logic has also been implemented in Oracle 10g by using the FOR UPDATE NO WAIT clause while locking the row.

    Is there any equivalent for the NO WAIT option in DB2 which will not result in closure of the cursor ?

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

    Is there any way to prevent the cursor from closing on the 911 error ?
    You could try to mplement an exception handler in the stored procedure that does not roll back the transaction.

    Quote Originally Posted by in10s View Post


    Is there any equivalent for the NO WAIT option in DB2 which will not result in closure of the cursor ?
    Issue SET CURRENT LOCK TIMEOUT NOT WAIT in the same session or in the stored procedure itself.

  3. #3
    Join Date
    Jun 2012
    Posts
    2
    Thanks for the reply,

    Quote Originally Posted by n_i View Post
    You could try to implement an exception handler in the stored procedure that does not roll back the transaction.
    The 911 exception is being handled, but still the cursor is getting closed resulting in 501 error for the next fetch.
    A point which i need to mention is that the stored procedure is written in oracle and is running under oracle compatibility mode in DB2. The exception handler syntax of oracle is present in the procedure.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    With DB2, even if you have an exception handler to catch the -911 (rc 2 or 68) the dbms has already rolled back the transaction. Rollback closes all open cursors. So if you want your cursors to stay opened, choose a design that avoids or very significantly reduces the -911 frequency , by whatever strategy. Additionally if the code assumes that cursors remain opened after -911 then that code has to be re-written.

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
  •