Skip to end of metadata
Go to start of metadata

Use

Avg user wait time for <user_task_ID>: <abs_duration> (<rel_duration>) ms, <number_of> waits, <appl_pid_and_node>

 

Explanation

Waiting times occur when users do not get the required resources. 

In a lock collision, the wait for a lock to be released is very long. This can be caused by a transaction that takes too long. Long wait times (task state vwait) can also occur when many applications want to lock the same object. If the locks are requested by other sessions, the database system attempts to execute locking tasks with higher priority within the database kernel.

For information on the current locks, see the system tables SYSINFO.TRANSACTIONS, DOMAIN.LOCKS and DOMAIN.LOCKWAITS of the database instance.

 

User Response

As waiting situation have an remarkable impact on performance you should find out the reason. If the user task is waiting to get a lock, f.e. there might be possibilities to make improvements on application level.

Check the following aspects of the application:

    • Is it suitable for isolation level 0 (uncommitted)?
    • Can the time between the lock request and the COMMIT statement be reduced?
    • Can you avoid simultaneous locks on the same row by splitting tables

 

More Information

Relevant  DB-Analyzer Files
Helpful with ...
DBAN_LOCKS.csvInformation about lock statistics. 
DBAN_TRANSACTIONS.csv Information about timeouts caused by locks, lock list escalations. 
DBAN_USER_TASKS_ACTIVITIES.prt  
SAP Note
Helpful with ...
  
Expert Sessions (Link to trainings)
Content / Helpful with ...
Session 27: SAP MaxDB Multi Tasking Find general information about the concept of multi tasking.
Session 12: Analysis of SQL Locking SituationsIn case of waiting situations because of SQL locks you find detailed information about the locking bebaviour.  
Documentation (links)
Helpful with ...
 

Concepts of the Database System SQL Locks

 <übernommen>
 System Tables 
  • No labels