Sybase Lock Waits Test

For every lock type auto-discovered, this test reports the total number of locks currently held, and the average wait time for locks. For execution, this test requires the enable monitoring and wait event timing configuration parameters to be enabled.

Target of the test : A Sybase ASE server on which the MDA tables have been installed

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every lock type being monitored

Configurable parameters for the test
Parameter Description

Test Period

How often should the test be executed.

Host

Refers to the IP address of the Sybase ASE server.

Port

The port number at which the Sybase server listens to.

User

To enable this test to connect to the Sybase server and collect the required metrics, it is enough if you configure the test with the name of a Sybase user who has the “mon_role”. However, for best results, it is recommended that you configure all Sybase tests with the credentials of a Sybase user who has the “mon_role”, “sa_role’, and “sybase_ts_role”.

Password

The password corresponding to the above user

Confirm Password

Confirm the password by retyping it here.

Detailed Diagnosis

To make diagnosis more efficient and accurate, the eG Enterprise embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option.

The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled:

  • The eG manager license should allow the detailed diagnosis capability
  • Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Number of locks

 

The number of locks of this type that are currently held.

 

Number

 

Ideally, both these values should be low. A large number of locks or a high wait time for locks can result in lock contention and can adversely impact server performance. You can use the detailed diagnosis of the Number of locks measure to view the complete lock details and to determine the process and user responsible for the lock.

Furthermore, you can use the following locking guidelines to reduce lock contention and speed performance:

  • Use the lowest level of locking required by each application. Use isolation level 2 or 3 only when necessary.

    Updates by other transactions may be delayed until a transaction using isolation level 3 releases any of its shared locks at the end of the transaction.

    Use isolation level 3 only when nonrepeatable reads or phantoms may interfere with your desired results.

    If only a few queries require level 3, use the holdlock keyword or at isolation serializing clause in those queries instead of using set transaction isolation level 3 for the entire transaction.

    If most queries in the transaction require level 3, use set transaction isolation level 3, but use noholdlock or at isolation read committed in the remaining queries that can execute at isolation level 1.

    If you need to perform mass inserts, updates, or deletes on active tables, you can reduce blocking by performing the operation inside a stored procedure using a cursor, with frequent commits.

  • If your application needs to return a row, provide for user interaction, and then update the row, consider using timestamps and the tsequal function rather than holdlock.
  • If you are using third-party software, check the locking model in applications carefully for concurrency problems.

Also, other tuning efforts can help reduce lock contention. For example, if a process holds locks on a page, and must perform a physical I/O to read an additional page, it holds the lock much longer than it would have if the additional page had already been in cache.

Better cache utilization or using large I/O can reduce lock contention in this case. Other tuning efforts that can pay off in reduced lock contention are improved indexing and good distribution of physical I/O across disks.

Moreover, you can configure a server-wide lock-wait limit with the configuration parameter lock wait period. This can reduce lock wait time considerably.

Avg wait time sec

The average wait time of locks of this type.

 

Secs

 

The detailed diagnosis of the Number of locks measure provides the complete lock details including the user and the process that initiated the lock, the object that has been locked, the lock type, the lock level, the lock wait time, and the exact page number and row number that has been locked. This information enables administrators to effectively troubleshoot lock-related issues, accurately identify what caused the lock, and optimize the process code/query so that, the lock is released.

DDnumoflocks

Figure 1 : The detailed diagnosis of the Number of locks measure