detecting cause of excessive locktimeouts

[adrbaetu]

I have problems debugging a application that I'm testing on its behaviour while running concurrent to other (process) instances of the same app.

It would help me if there is a way to trace/monitor the locks activated by each sqlstatement along with the issuing app.instance.
for example in form of a table
________________________________________________________________
STMT | LOCKs |
| LockType, Obj(Row-PK/Tbl)| AppName, Time
--------------------+---------------------------+--------------------
SELECT * FROM ... |S, 12 | DBROM1,
|S, 435 | 12:03:17 123
|... |
--------------------+---------------------------+--------------------
UPDATE ... | X, Tablename | DBROM2,
12:03:17 335

(pls. excuse the mess, this is a formating issue in this forum, I couldn`t work around )

Maybe there are applications to support such investigations.
I`m greatfull for anny hint,

Thanks

 

[gregsimpson]

adrbaetu,

Does the supplied Lock Snapshot not help you.

Issue

GET SNAPSHOT FOR LOCKS ON "your database name"

For more detailed info make sure you have turned on the Lock Monitor Switch

db2 update monitor switches using LOCK on

It can be turned off by

db2 update monitor switches using LOCK off

There are similar snapshots you can take against the application, table spaces etc.

Cheers
Greg

 

[adrbaetu]

gregsimpson,

thanks for the fast reply.
I got few snapshots while 2 instances of the app. were running. I`ll have to evaluate how much I can find out using the produced log-entries.

But...
The issue is that the 2 inst. sometime come into a deadlock where the locktimeout along with a internal rollback resolves the conflikt.
In this situation both inst. wait quite long(~30s). The target is to have around 40 of this inst. running in parallel.
If I let 4 inst. run in parallel the result is almost 100% of failed transactions.
Each inst. represent the interaction of a User with the system, this means that almost all actions of the user end up with "Sorry, couldn`t process your request. Please try again."

Conclusion..
I have to understand exactly what happens in DB2 while processing the statements of the inst. concurrently.
The information that would let me understand could be the answer to:
"When is which lock set for which statement of which inst?"

Than I can see the exact order of the lock-req., statement execution(or fetch) and determin the logical error in the
app.

I have the feeling there must be some kind of tools/software, that can use the monitoring capabilities of DB2 and visiualize this in form of a table or graph.
I can think of a way to realize such a app. but this would be overkill. I'd rather preffer one that already exists

Anny Ideas?

Thanks,
Take care

Regards,
Adrian Baetu/PLATO AG

 

[gregsimpson]

Adrian,

I have used the DGI tools extensively on UNIX in the past. They do now have a graphical interface I believe. What OS are you using?

The DGI people will give you a free trial of their products, which hopefully at the same time, will enable you to solve your problems.

http://www.breakthroughdb2.com/productindx.shtml

Without sounding insulting, are your tests indicative of the real world. For example are all your instances trying to update the same rows or are you emulating a more real world scenario, where by different users would be dealing with different rows on the tables?

You may also wish to play with the LOCKLIST and MAXLOCKS parameters.

LOCKLIST defines the amount of memory allocate for the locks.
MAXLOCKS defines the % of the total locklist permitted to be allocated to a single application.

If one APP exceeds the % of the locklist as defined by the MAXLOCKS config param. The db manager will attempt to free memory by obtaining a table lock and releasing row locks for this app.

Many apps connected to the db fill the locklist by acquiring a large number of locks.DB2 will attempt to free memory by obtaining a table lock and releasing row locks.

The isolation level used by the app has a big effect on lock escalation

CS will acquire row level locks. IF required table level locks can be obtained. CS usually only acquires a a small number of locks as it only gaurantees the integrity of the data in the current row.

RS locks all rows in the original result set.

RR may or may not obtain row locks on all rows to determine the result set. If it does not, then a table lock will be obtained instead.

Perhaps, your isolation level is worth exploring further.

Cheers
Greg

 

[sathyarams]

Statement Event Monitors and Deadlock Event Monitors will be useful



For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[adrbaetu]

gregsimpson,

Thanks for the suggestions.
I`m using W2K as OS, and DB2 UDB V8.14 .
I`ll take a look on the DGI site maybe they offer something for W2K as well. At least I`ll get some usefull keywords for google

> Without sounding insulting..
Don't worry about that, I'm greatfull for your help. Feel free to asume what ever might help.

I'll be a bit more detailed this time. This might be helpfull for others who encounter the same problem.
On the other hand you or someone else might see the cause
of my problem and provide help.

> tests indicative of the real world..
My Test is simulating a series of similar transactions but each of them working on different entries in the target table :
Transaction T1 - repeats 10 times
1) INSERT INTO RFunction ..
2) COMMIT

Transaction T2 - repeates 10 times for each T1
1) INSERT INTO RFunction ..
2) INSERT INTO RFuncNode ...
3) SELECT ... FROM RFuncNode WHERE Col2 = ? AND Col3 < ? ORDERBY Col3 ASC
4) UPDATE ... RFuncNode ..
5) SELECT ... FROM RFuncNode WHERE Col5 = ? ...
6) UPDATE ... RFuncNode ..
7) COMMIT

Comments:
- The Selects in 3 & 5 define a subset of the RFuncNode Table that dosen`t include the newly inserted RFuncNode entry in step 2.
- The Update's change only the the newly inserted RFuncNode entry in step 2.
- The successfull Inserts grant that each transaction works on entries that are not directly accessed by other processes or subsequent Transactions.
- This Test simulates 2 common and usually subsequent Actions of a User:
1) Submit new Systemelement
2) Connect new Function to the previously created Systemelement.

Initially I wouldn't expect any dedlocks or locktimeouts for this test scenario even if 100 processes are running concurrently.
Still this is the problem CLI is reporting:
SQLSTATE = 40001
Native Error Code = -911
[IBM][CLI Driver][DB2/NT] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001

Since the transactions have a avarege duration between 100ms to 600ms, and the LOCKTIMEOUT is set to 30s I would expect locktimeouts earliest up to 60 parallel running processes. actually I encounterthem already with 2 concurrent instances of the TestAPP.


After deeper investigations on isolation levels and locking issues in DB2 I came to a more or less satisfactory solution ( read at the end of the posting ).

STILL there are open questions I couldn't answer!?!

There is a deadlock situation I encounter:
success/process A B
--------------------------------------------
true INSERT
true INSERT
false SELECT
false SELECT
DEADLOCK!!

Following settings are active:
- We have 1 persistent connection per process to the same DB,using the CS - Isolation level, AutoCommit Off and Read Write Access Mode.

- Each step of the transaction allocates a new statement handle and frees the handle after execution and eventual fetch's using always the central allocated connection handle.

- Following Statement attributes are used:
Close Behaviour = CC_RELEASE
Concurrency = CONCUR_LOCK
Cursor Hold = CURSOR_HOLD_ON
Scrollable = NONSCROLLABLE
Sensitivity = UNSPECIFIED
Cursor Type = FORWARD_ONLY
Prefetch = PREFETCH_ON
Query Optimization Level = 3
Stmt Isolation = READ_COMMITED ( = CS )

Comment:
The SELECT I'm talking here of is the step 3 or 5, and the INSERT is step 2 of the Transaction 2.
Both statements access the same table RFuncNode!

Questions:
Why is the INSERT of B allowed but the SELECT is not?
( as mentioned the set of entries defined by the SELECT dose not include the new inserted entry! )

Solution( but not satisfactory )
The only way to overcome this situation was to explicitely Lock the complete table exclusively till to the next COMMIT.

Our target was to take advantage of the rowlevel locking capabilities of DB2 and encrease the cuncurrency performance of our application.

Moreover this behaviour of DB2 is not understandable. Eather this is a bug, or I am still missing something!?!

My monitoring and snapshot sessions show me that there are no Lockescalations or deadlocks only locktimeouts:
Locks held currently = 18
Lock waits = 370
Time database waited on locks (ms) = 880936
Lock list memory in use (Bytes) = 2880
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 1
Lock Timeouts = 13
Number of indoubt transactions = 0

A overall Solution was reached using following constellation:
1) Exclusive locking of target table before inserts.
2) speciffics for Read Only Transactions:
a) Read Only Access Mode for the connection
b) Read Uncommited statement isolation level

3) speciffics for Read Write Transactions
a) Read Write Access Mode for the connection
b) Read Commited statement isolation level

4) average Connection attributes
a) Autocommit - off
b) Connect Type - coordinated transaction
c) Sync point - Two Phase
d) Isolation Level - READ_COMMITED ( = CS )

5) average Statement attributes
a) Close Behaviour = CC_RELEASE
b) Concurrency = CONCUR_LOCK
c) Cursor Hold = CURSOR_HOLD_ON
d) Scrollable = NONSCROLLABLE
e) Sensitivity = UNSPECIFIED
f) Cursor Type = FORWARD_ONLY
g) Prefetch = PREFETCH_ON
h) Query Optimization Level = 3

Last but not least,
I would like to eliminate point 1 ( Table locking ) but I don't know how to solve the deadlock situation discribed
above.

I'm greatfull for any suggestions and help?

Thanks,