Db Locks Test
Multiple transactions can access the same database object, such as a table, at the same time. To isolate the transactions from one another, the database system sets locks for database objects. The Db Locks test monitors the locking activity on MaxDB.
Target of the test : A MaxDB server
Agent deploying the test : An internal agent
Outputs of the test : One set of results for the MaxDB server being monitored.
Parameter | Description |
---|---|
Test Period |
How often should the test be executed. |
Host |
The host name of the server for which the test is to be configured. |
Port |
The port number to which the server is listening. |
DatabaseName |
The test connects to a database on MaxDB and extracts performance statistics from the system tables in the database. Therefore, provide the name of a database in the DatabaseName text box. |
Username |
Since users with the SYSDBA privilege alone are allowed access to system tables, specify the name of such a user against Username. |
Password |
Provide the Password that corresponds to the specified Username. |
Confirm Password |
Confirm the password by retyping it here. |
Measurement | Description | Measurement Unit | Interpretation |
---|---|---|---|
Num locks |
Indicates the number of locks currently in the database. |
Number |
A consistent increase in the value of this measure indicates a contention for locks. |
Transaction holding locks |
Indicates the number of transactions to which locks have been assigned, currently. |
Number |
|
Transaction request locks |
Indicates the number of transactions requesting locks in the last measurement period. |
Number |
|
Num oms locks |
Indicates the number of OMS locks currently in the database. |
Number |
|
Deadlock rate |
Indicates the rate of deadlocks. |
Deadlocks/Sec |
A deadlock may arise due to various situations including bad design of queries and deficient coding practices. A deadlock is a situation where both/all the lock requestors are in a mutual or a multi-way tie. Any deadlocks are detrimental to database application performance. |
Collision rate |
Indicates the rate of lock collisions. |
Collisions/Sec |
A lock collision occurs when tasks running in different threads attempt to access a global storage area in parallel. The synchronization required for this often leads to an increased collision rate. Generally, the risk of collision rises with the number of processors used (MAXCPU general database parameter). In multiprocessor systems, you should therefore check whether the database system can fulfill the needs of the applications with fewer CPUs. If high collision rates occur in multiprocessor central systems (database system and application running on the same computer), check whether the computer’s CPU is overloaded, and whether the database threads are blocked by other applications. In this case, the database threads that contain user tasks should receive REAL TIME PRIORITY from the operating system. To avoid operating system blocks however, the value of MAXCPU must be at least one lower than the number of actual CPUs. If the high collision rates occur in the DATAn, SPLITn or TREEn regions, increase the values of both the general database parameter CACHE_SIZE and the special database parameters _DATA_CACHE_RGNS and _TREE_RGNS. If the high collision rates occur in the TRACE or BUFWRTR regions, then activate the database trace temporarily for troubleshooting only. Note: One exception to this in liveCache instances is high collision rates in the OMSVDIR and CNSTVIEW regions. This is normal for certain actions, such as a simultaneous CIF queue transfer. |
Escalation rate |
Indicates the rate of escalations. |
Escalations/Sec |
Escalations show the total number of rows locked by a single user session. If more than a certain percentage of the rows of a table are locked by a single user session, then the database system locks the entire table. You can specify the maximum number of possible row locks in the lock list in the general database parameter MAXLOCKS. The database system attempts to convert the row lock to a table lock if a task holds more than 0.1*MAXLOCKS row locks in a table. If too many escalations occur, increase the parameter value. Whether escalations lead to problems depends strongly on the application in question. If escalations occur, check the application to see whether you can split any change transactions that lock a lot of rows into several individual transactions. |
Row locks |
Indicates the rate of row locks. |
Locks/Sec |
A consistent increase in the value of this measure could indicate a probable escalation. |
Table locks |
Indicates the rate of table locks. |
Locks/Sec |
|
Request timeouts |
Indicates the rate at which lock requests exceeded the timeout value. |
Timeouts/Sec |
If the value of this measure is high, you might want to reset the REQUEST_TIMEOUT value. |