cancel
Showing results for 
Search instead for 
Did you mean: 

How to log when a command locks a table?

Former Member
0 Kudos

Hi,

there is a way to log when a command locks a table, and what command, using the Diagnose Monitor?

thanks for any tip.

Clóvis

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

Hi Clovis,

locking is one of the very core functionalities of the MaxDB. It does it all the time just like reading pages from disk into the cache or communicating over the network.

Logging out every lock-action would just lead to a huge bunch of data. Even a SELECT statement will lock the tables affected.

So the question is: what do you want to do with this information? Do you have locking problems or do you want to get these information for educational purposes or ...?

Basically you can always check the internal table LOCKS to see what session is locking what table(row).

Concerning the Diagnose Monitor: there is no command that would not lock the tables/rows that are used with it. So whenever you see a command in the Monitor, you can be sure, that is used locks when it ran (Ok, except someone played around with the Isolation level...)

Best regards,

Lars

Former Member
0 Kudos

Hi Lars,

the idea is to know when a command locks the table, not just one row, and another commands cant be executed in that table until the command that achieved the lock reach the end.

iow: know when a entire table become locked.

Best regards

Clóvis

lbreddemann
Active Contributor
0 Kudos

Hi Clovis,

even when you just do a SELECT on a single row the corresponding table will always get a SHARE lock - just to prevent other sessions from acquiring a exclusive lock on the table.

I assume you mean when a table gets an exclusive lock by a statement, e.g. through what is called a lock escalation.

Unfortunately you cannot see this happen in the command monitor, but only see that one (or more) lock escalations happened (check the lockliststatistics table or the dbanalyzer warnings).

The main reason for this is that the lock escalation is not only caused by a specific statement but also by the configuration of the lock lists (MAXLOCKS paramter).

All in all: lock escalations should be avoided. Either by setting MAXLOCKS to a higher value or by finding statements that read lots of rows at once.

KR Lars

Former Member
0 Kudos

Hi Lars,

I increased the MAXUSERTASKS from 150 to 300 in that way the MAXLOCKS increase automatically and the lock escalation goes out, appears that solved some system slow performance, now i will try to run dbanalyzer to check for more problems.

thanks for your insights

best regards

Clóvis

Answers (0)