Deadlock detection search level

[Former Member]

Hi,

We are a non-SAP user of MaxDB and are having some deadlock problems with version 7.5.0.34.

What we are seeing is that deadlocks don't seem to be getting detected and the request timeout doesn't seem to be enforced.

I am trying to find out more information on what the search depth value for the extended parameter DEADLOCK_DETECTION does? How does increasing or decreasing this parameter affect how MaxDB finds deadlocks?

TIA and regards,

Andrew.

[lbreddemann]

>

> Hi,

>

> We are a non-SAP user of MaxDB and are having some deadlock problems with version 7.5.0.34.

>

> What we are seeing is that deadlocks don't seem to be getting detected and the request timeout doesn't seem to be enforced.

Have you got some output of

select * from locks

that document this behaviour?

> I am trying to find out more information on what the search depth value for the extended parameter DEADLOCK_DETECTION does? How does increasing or decreasing this parameter affect how MaxDB finds deadlocks?

Well usually there is no need to increase this parameter.

Basically it's about how many many hops of the lock-graph are followed until it is assumed that there is no crosswise lock request (aka deadlock).

regards,

Lars

[Former Member]

Hi Lars,

I did run a

select * from lockstatisitcs

A CSV dump of the output can be found [here|http://members.iinet.net.au/~unit1/lock.csv]

Unfortunately, the dump is incomplete as I was in a rush to get the system back up and running and I didn't notice that I only grabbed the first 100 rows.

During normal operation, I would be lucky to ever see any locks when I run this command.

I'm reasonably sure that our application is creating the situation where the deadlock can arise (I think its the way we are using DBCP), but I'm not sure about why we never see MaxDB issue a rollback deadlock error.

Thanks,

Andrew.

[lbreddemann]

> Unfortunately, the dump is incomplete as I was in a rush to get the system back up and running and I didn't notice that I only grabbed the first 100 rows.

Hmm... unfortunately these 100 rows are not sufficient. E.g. for the lock requests there aren't the information about the lock holders in the file.

> During normal operation, I would be lucky to ever see any locks when I run this command.

>

> I'm reasonably sure that our application is creating the situation where the deadlock can arise (I think its the way we are using DBCP), but I'm not sure about why we never see MaxDB issue a rollback deadlock error.

What do you mean with "DBCP" ?

Concerning the deadlock - I would be pretty surprised to find that there actually is a database deadlock.

The important difference between a heavy locking situation (which perfectly can grind the system to standstill) and a deadlock is, that the deadlock cannot be resolved by principle. One has to cut off one of the sessions for that.

It's a closed circular locking-graph seen from a theoretical point of view.

The deadlock detection can only look for this kind of locking situation (the real deadlocks).

Could it be that your application threads/processes/instances do have to enqueue and wait on other shared resources?

E.g. like a session management singleton or something like that?

This could lead cross-layer deadlock which are basically not detectable from within a single layer (the db).

Another thing: have you checked whether you hit lock escalations?

Maybe increasing the value of the MAXLOCKS parameter would already solve the issue.

regards,

Lars

[Former Member]

Hi Lars,

Sorry about taking so long to get back to you.

DBCP is Apache Commons Database Connection Pool. We use it with Apache Tomcat to provide a connection pool for our application.

I've been able to capture another instance when this has occurred. A CSV file of it can be found [here|http://members.iinet.net.au/~unit1/hart_lockstat.csv].

I think the lock by session java@115ae0 on the last row is preventing session java@8d59f1 from getting the lock on row 39. To complete the deadlock, row_exclusive locks by java@8d59f1 are preventing java@115ae0 from getting the lock on row 53.

The trouble is, the database stayed in this state for over 10 minutes before I restarted it. Shouldn't the deadlock detection have picked this up and rolled backed one of the sessions?

MAXLOCKS is set to 6920. REQUEST_TIMEOUT is set to 5000.

I was thinking that I could drop the value of REQUEST_TIMEOUT (5000s is a bit long to be waiting anyway) so it will kick a session that has been waiting on a lock for too long.

Regards,

Andrew.

[lbreddemann]

Hello Andrew,

thanks for the file.

This is what I made up of it

T39 -> java@115ae0
T40 -> java@8d59f1

 T40                              ---RX---> T39 (TS, T_GOLF_SCORECARD_AUDIT)


 T40 (RX, T_GOLF_SCORECARD_HOLE)  <--TS-T-- T39

So, yes, there's a deadlock present here.

Unfortunately the deadlock recognition is pretty limited (although the documentation does not say much on these limits).

What irritates me a bit is that there are requests for Table-Sharelocks in TEMP mode.

What Isolation level do you use for your application?

The other thing is (has nothing to do with this issue, but as a general hint) MAXLOCKS < 50000 is pretty small.

Remember that for each rowlock you need also a table lock. So it's very easy to reach this number of locks.

And the smaller this parameter is set, the more likely lock escalations become.

regards,

Lars

[Former Member]

Hi, Lars,

i'm interested in this topic, about question of REQUEST_TIMEOUT there is a way to set or know today the max time that maxdb wait today for a lock?

regards

Clóvis

[lbreddemann]

Hi Clovis,

sure, no problem with that.

Check http://maxdb.sap.com/training/internals_7.6/locking_EN_76.pdf, page 9 ff.

This should answer most of your questions.

If there still remain open points you may think about a new thread.

And before I forget... I did not forget about the BW feature stuff... I just had not yet time for it :-[

regards,

Lars