If the inventory lock contention level is high,
relative to your processing concurrency levels, or if you feel that
your processing throughput or end-user response times are impacted,
we suggest the following course of action.
Look at the lock-holding times. Run each inventory
processing transaction with SQLDEBUG traces or possibly VERBOSE traces.
VERBOSE traces provides more data but can be more intrusive than SQLDEBUG:
- See how long, on average, the transactions take.
- See when the first inventory locks are obtained
(and as a result, how long they are held) within that transaction
boundary. The goal is to keep lock-holding times short.
- See if there are places in the transaction that
take a long time to process and the processing occurs when inventory
item locks are held. For example:
- The transaction may have a user-exit that calls
out to external systems. If that external system slows down or is
unable to scale, the user-exit time increases. This elongates the
lock-holding times.
- There may be SQL statements that run for a long
time and can be optimized with better database statistics or an additional
index.
- Look at the GC logs - Make sure the transaction
is not slowed down by long costly garbage collection pauses.
Reducing lock-holding times can have compounding
effects - as lock-holding times decreases, transactions finish faster
and, as a result, lower concurrency levels.