Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186

    Unanswered: Multiple procedure call for same procedure in Multithreaded application

    Hi,

    We have a application which starts multiple threads. These threads call the same Db2 UDB stored procedure at the same time. The problem is due to locking of Db2 objects the process is running very slow.

    Right now the isolation level used is default

    Certain things that can be taken care to avoid this are:
    1. Use of DB2_SQLROUTINE_PREPOPTS for setting isolation at session level
    2. Tuning the stored procedure code to avoid locks

    One thing which i feel might be a problem is that when we call a SP the SYSPROCEDURE table is read and if the isolation level at the database level is RR, then this might create a lock on the row. When the same procdure is again called from a different thread then this might cause wait state.

    Please give me your inputs and also let me know if my thinking is wrong.

    Cheers,
    Prashant

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    We have a application which starts multiple threads. These threads call the same Db2 UDB stored procedure at the same time. The problem is due to locking of Db2 objects the process is running very slow.
    Why are they locking each other ? Are they trying to update the same record ? or is one of the threads trying to read a record which is being updated ?
    In either of the circumstances, I guess you'll have to revisit your application design ..

    Or, if the locking is caused by tablescans ? Consider the use of indexes to avoid tablescan ..

    Right now the isolation level used is default

    Certain things that can be taken care to avoid this are:
    1. Use of DB2_SQLROUTINE_PREPOPTS for setting isolation at session level
    2. Tuning the stored procedure code to avoid locks
    And, also making sure you are using Type-2 indexes to avoid next key locking or setting DB2_RR_TO_RS to YES.


    One thing which i feel might be a problem is that when we call a SP the SYSPROCEDURE table is read and if the isolation level at the database level is RR, then this might create a lock on the row. When the same procdure is again called from a different thread then this might cause wait state.
    If my understanding is right, using RR as an isolation level will not impact the 'system level' read of the system catalog tables .. If you are calling a procedure, the row is locked in read-mode ..

    Do a lock snapshot and that should show why the waits ...

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Couple of more questions on DB2_SQLROUTINE_PREPOPTS registry variable:
    1. Isolation level set under this will be used by stored procedures, but it needs restart of instance. So if we have to use it only for specific procedures, how can we achieve that. (e.g. The current isolation level is RR and for SP i want it to be RS)
    2. Is it only beneficial for stored procedures that use more cursors?

    We have the setting DB2_RR_TO_RS to YES.

    I will look for the snapshot of locks etc and get back with details.

    cheers,
    Prashant

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by dahalkar_p
    Couple of more questions on DB2_SQLROUTINE_PREPOPTS registry variable:
    1. Isolation level set under this will be used by stored procedures, but it needs restart of instance. So if we have to use it only for specific procedures, how can we achieve that. (e.g. The current isolation level is RR and for SP i want it to be RS)
    In V8.2, SET_ROUTINE_OPTS procedure was introduced, which sets options for the current session only ..

    http://publib.boulder.ibm.com/infoce...n/r0011873.htm

    If you are not on 8.2 yet, an option is to BIND the source bind files of the package.


    2. Is it only beneficial for stored procedures that use more cursors?
    Depends on the application requirement.

    We have the setting DB2_RR_TO_RS to YES.

    I will look for the snapshot of locks etc and get back with details.
    cheers
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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