Monitoring locking on a specified table

[Former Member]

Hi there,

Helppp....does anyone know how to monitor locks in a particular table (eg: NRIV)in Solution Manager or CCMS please ?

Many thanks,

Mina

[Former Member]

Hi Mina,

what do you exactly mean with locks? deadlocks,exclusive locks, etc.?

Regards,

Pascal

[Former Member]

Hi Pascal/All,

Sorry for the delay as the request to setup this monitoring was put onhold, but now it's back again.

It is the deadlock on table NRIV, specifically for object RF_BELEG.

Please help if you have come accross this setup.

Many thanks,

Mina

[Former Member]

Hi Mina Wirama,

As a workaround, can you consider TCode SM12?

[Former Member]

Hi Pascal,

Could you please give me more info on it ? isn't it SM12 only monitor locking on tables, but not the deadlocks ?

Thanks,

Mina

[Former Member]

Hi Mina,

If you wish to be alerted when a deadlock has happened on your system you may want to consider doing the following:

SAP CCMS has built in functionality to allow you to monitor text files and search for a string. You can therefore find the appropriate log file for your database and setup log file monitoring to search for the text string "deadlock" or the error code for your particular database. If you do a search on OSS you will find a note that describes how to setup log file monitoring. It is quite simple.

See 535199 CCMS agents: Monitoring log files

N.P.C

[Former Member]

Hi,

Thank you for the info and help. Unfortunately, the log for db2 (db2diag.log) doesn't much info on the deadlock of a table. It only provides SQL0911N info without mentioning the table. Life might be easier if they system runs on oracle database...:)

Thanks,

Mina

[Former Member]

Hi Mina

sorry if this reply comes too late for you..

However at my current client, they had immense NRIV locking problems for many years when they ran R/3 invoicing jobs in parallel.

As I am also from an Oracle background, I originally assumed that the deadlocks were due to poor DB2 performance and spent months investigating this angle.

The deadlocks for this can be seen in transaction DB01, but I never saw NRIV locks in SM12 (due to the buffered nature of the NRIV access I think). You can also see deadlocks and timeouts for DB2 in transaction /nDB2.

Finally I relasied that actually the bottleneck was not in the DB but in the app servers - the batch processes frequently displayed semaphore 8 (unable to access free dia wp). Monitiring this situiation in SM66/SM50 I saw that the batch jobs spawned many workflow & RFC requested which satruated all the free dia wp's on the same app server, preventing the number range from being re-populated (this needs a free dia wp). However this gives the impression that it's due a database deadlock as in DB01 and in the ST22 dump, all clues point to this.

So we shifted all WF and RFC on to separate apps servers and cleared the issue (so far at least). The batch jobs continue to run as they are able to replenish the number ranges now as there are many free dia wp's on the batch servers. The spawned asynchronous RFC's and workflows are processed on separate servers and do not block the batch processes.

I hope this makes sense - it took us a long time to realise the solution and hence I'm posting it here in case it helps other people.

Good luck

Ratna