TX lock contention is an application coding, design and usage problem and can ONLY be fixed by changing application code with more frequent and explicit COMMIT statements and any other minor code changes. Oracle Support cannot fix TX lock wait issues other than helping to identify the objects and commands causing the waits.
This wait indicates time spent waiting for a TX lock, typically due to waiting to gain access to a row in a table that is currently locked by that transaction
This wait event can occur for several reasons.
If one user is wanting to update or delete a row or rows that another session is modifying. The session holding the lock will release it when it performs a COMMIT or ROLLBACK.
If a session is waiting due to potential duplicates in a UNIQUE index. If two sessions try to insert the same key value, the second session has to wait to see if an ORA-0001 should be raised or not. The session holding the lock will release it when it performs a COMMIT or ROLLBACK.
If a session is waiting due to a shared bitmap index fragment. Bitmap indexes index key values and a range of rowids. Each entry in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock.
Wait Time:
Oracle waits for the TX lock until it is either granted or the request times out, as can occur if a "WAIT" clause is included in the current SQL. Deadlock detection occurs for TX locks.
Finding Blockers:
Details of the object / block / row that caused the wait can usually be found in the ROW_WAIT_* columns of V$SESSION for the waiting session
SELECT row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
FROM v$session
WHERE event='enq: TX - row lock contention'
AND state='WAITING'
;
You can find current blockers by querying GV$LOCK like this:
SELECT distinct w.tx, l.inst_id, l.sid, l.lmode, l.request
FROM
( SELECT p2,p3,
'TX-'||lpad(ltrim(p2raw,'0'),8,'0')||'-'||lpad(ltrim(p3raw,'0'),8,'0') TX
FROM v$session_wait
WHERE event='enq: TX - row lock contention'
and state='WAITING'
) W,
gv$lock L
WHERE l.type(+)='TX'
and l.id1(+)=w.p2
and l.id2(+)=w.p3
ORDER BY tx, lmode desc, request desc
;
Possible actions:
1) Check the "Segments by Row Lock Waits" section of the AWR for clues about objects incurring high waits
2) Use ADDM reports to identify SQLs and sessions involved in high TX waits at systemwide level.
3)Drill down into individual SQLs or sessions showing high waits to understand which application code , which objects, and which SQL/s are involved in the waits.
4) ASH reports for sessions showing high "enq: TX - row lock contention" wait time can also help.
AWR Analysis for "enq: TX - row lock contention"
Look for this SQL in the top AWR sections, for example, look to see if it appears high in the following (or others):
* SQL ordered by Gets - this might imply that the query is reading too many buffers and could need tuning. If it runs slowly, this might cause it to hold locks for longer than necessary, causing contention.
* SQL ordered by Parse Calls - this might imply that the query is spending a lot of time being parsed or is being frequently parsed. Excessively long parse times might cause locks to be held too long
* SQL ordered by Executions - If the query is being executed more frequently than it needs to, it is possible that it is then contending with other processes unnecessarily.
These could give you some information as to why there is contention.
ASH Report Analysis for "enq: TX - row lock contention"
* Look at the ASH report. The "Top User Events" section allows you to confirm that there is TX contention during the period, in this case we are interested in: "enq: TX - row lock contention":
* The "Top Blocking Sessions" shows you the sessions that are most commonly blocking.
* You can look up the session details in the "Top Sessions" to see what that blocking session is doing.
* From the blocking session, you can see what that is waiting for (in this case 'log file sync') and then look into why it is waiting for that and address it.
* ASH also has a "Top DB Objects" section which may show you the top objects and associated events
With this information you should have the blocking and the waiting SQL. Since TX contention is a application design issue, it is then up to the customer to work out why they are stressing these locks so hard.
Reducing Waits / Wait times:
Reducing waits typically involves altering application code to avoid the contention scenario/s.
eg: altering code so that concurrent sessions work on non-intersecting data sets, committing more frequently if the blocker is a long running transaction, etc..
