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.
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.
For DB2®, check the following monitor elements:
We suggest you look at the following: