Determining the amount of inventory lock contention

Transactions that hold inventory item record locks can block other transactions that need the same record. A certain amount of lock contention is acceptable especially if transactions are blocked infrequently or for short periods of time and if there is no material impact on processing throughput or end-user response times.

Determining level of lock contention in Oracle

You can determine the level of inventory lock contention with the following techniques. In Oracle:

AWR reports provide a measure of the total amount of time (in seconds) all transactions waited for record locks. This metric is found in the "Wait Events for DB" section (page 2) of a AWR report. In the following example, transactions waited for enqueues for a total of 741 seconds in that 30-minute measurement interval:

Wait Events for DB: YRAC05  Instance: YRAC051  Snaps: 15202 -15203
                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read           903,826          0      6,246      7      3.0
db file scattered read            879,659          0      4,281      5      2.9
enqueue                             3,542          6        741    209      0.0
library cache pin                     375        231        719   1918      0.0
buffer busy waits                 116,687          0        449      4      0.4
log file sync                     129,571          0        134      1      0.4

Dividing that number of enqueue wait times (741 seconds) by the measurement interval (30 minutes) shows that the enqueue contention was on average 0.41 blocked seconds per second. From a statistical point of view, one transaction was blocked 41% of the time every second. If you have ten concurrently running transactions, at one extreme, this statistic could be interpreted as all transaction was blocked 4.1%. At the other extreme, one transaction could have been completely blocked for 719 seconds.

In the example above, the lock contention is minimal. As a guideline, high lock contention situations are characterized as:

If enqueue wait times are significant, run the following query to identify the sessions that are blocked, the amount of time that they were blocked for, and the objects they are blocked on:

   select sid,last_call_et, sql_text
   from v$session vs, v$sqlarea sa
   where last_call_et > 0 and
        vs.sql_hash_value = sa.hash_value and 
        vs.lockwait > ' '
   order by last_call_et desc;
   
   SID      LAST_CALL_ET       SQL_TEXT                                
   13               1       SELECT  /*YANTRA*/   YFS_ORDER_HEADER.* 
                         FROM YFS_ORDER_HEADER YFS_ORDER_HEADER     
                         WHERE ENTERPRISE_KEY =:"SYS_B_0" AND  
                               ORDER_NO = :"SYS_B_1"  FOR UPDATE 
   

In the example above, session (SID=13) blocked for 1 second while trying to lock a YFS_ORDER_HEADER record.

We suggest you look at the following:

This query, along with the contention level derived from AWR, lets you determine the extent of the lock contention.

Determining the level of lock contention in DB2

For DB2®, check the following monitor elements:

We suggest you look at the following: