Multiple procedure call for same procedure in Multithreaded application

dahalkar_p · #1 (**permalink**) 02-27-06, 15:00

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,

sathyaram_s · #2 (**permalink**) 02-27-06, 17:19

Quote:

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 ..

Quote:

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.

Quote:

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

dahalkar_p · #3 (**permalink**) 03-02-06, 14:34

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,

sathyaram_s · #4 (**permalink**) 03-02-06, 21:43

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.

Quote:

2. Is it only beneficial for stored procedures that use more cursors?

Depends on the application requirement.

Quote:

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