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.

Configurable parameters for the test
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.