AWR Reports of a 12.1.0.2 EE Oracle Database shows enq: TX - row lock contention with up to 80% of DB Time. The application is huge with more than 500 tables and 100.000 registered users, on average around 800 concurrent user sessions. Users complain about long waits. This is definitely an application problem also according to Oracle support Doc ID 1476298.1.
Still we are trying to support the application development. I think there are two types of enq: TX - row lock contention mode 4 and mode 6. In my opinion the problem is related to mode 6. The tables locked don’t have foreign key constrains nor bitmap indexes.
Lock Types:
- 1 Null Null
- 2 SS Sub share
- 3 SX Sub exclusive
- 4 S Share -> TX - row lock contention Mode 4
- a. Unique Index
- b. Foreign key
- c. Bitmap indexes
- 5 SSX Share/sub exclusive
6 X Exclusive -> TX - row lock contention Mode 6
- TX lock is acquired when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. It is used mainly as a queuing mechanism so that other sessions can wait for the transaction to complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction ID of the active transaction.
I collected the following information after an incident report:
The sessions 375, 969, 975 etc. are waiting for session 1162.
@utllockt.sql
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
1162 None
375 Transaction Exclusive Exclusive 196610 122968
969 Transaction Exclusive Exclusive 196610 122968
975 Transaction Exclusive Exclusive 196610 122968
1238 Transaction Exclusive Exclusive 196610 122968
1739 Transaction Exclusive Exclusive 196610 122968
How long are they waiting?
SQL> select session_id, LAST_CONVERT Sekunden, LAST_CONVERT/60 Minuten from
dba_locks where Session_id in (375, 1162) ;
SESSION_ID SEKUNDEN MINUTEN
---------- ---------- ----------
375 8072 134,533333
1162 5267 87,7833333
1162 549 9,15
1162 576 9,6
375 576 9,6
1162 574 9,56666667
1162 574 9,56666667
1162 574 9,56666667
375 2923 48,7166667
1162 4611 76,85
1162 576 9,6
1162 574 9,56666667
1162 549 9,15
1162 550 9,16666667
1162 550 9,16666667
1162 576 9,6
1162 576 9,6
What users are involved?
SQL> select sid, serial#, username from v$session where sid in (375, 1162);
SID SERIAL# USERNAME
---------- ---------- ------------------------------
375 31530 user_1
1162 46115 user1
Locks on objects in the database:
SQL> SELECT a.session_id, a.oracle_username, a.os_user_name, b.object_name
FROM v$locked_object a, sys.all_objects b
WHERE b.object_id = a.object_id
ORDER BY 2, 3; 2 3 4
SESSION_ID ORACLE_USERNAME OS_USER_NAME OBJECT_NAME
---------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
975 USER_1 osuser_333 ANXXXXX
969 USER_1 osuser_355 TREXXX
1162 USER_1 osuser_555 TGXXXX
1162 USER_1 osuser_555 REISXXXX
1162 USER_1 osuser_555 TGXXXXX
1162 USER_1 osuser_555 DOKXXXXXX
1162 USER_1 osuser_555 ANXXXXX
1162 USER_1 osuser_555 ANTRXXXX
1162 USER_1 osuser_555 ANTRAXXXN
1162 USER_1 osuser_555 DOKXXX
1162 USER_1 osuser_555 EAKTEPXXXX
1162 USER_1 osuser_555 FAHRXX
1162 USER_1 osuser_555 TRENNXXX
1162 USER_1 osuser_555 TRENXXXX
375 USER_1 osuser_321 ANXXXXX
Which SQL?
SQL> select SQL_ID from v$session where sid in (1162,375);
SQL_ID
-------------
413m9wtbz3w4b
SQL> select SQL_TEXT from v$SQL where sql_id='413m9wtbz3w4b';
Update AnXXXX set AnXXXXXX.BELXXX=:xaa Where AnwXXX.F14XX=:xab
Show sessions waiting for a TX lock:
SQL> SELECT * FROM v$lock WHERE type='TX' AND request>0;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000000FFAB126C0 0000000FFAB12738 2031 TX 196610 122968 0 6 1080 0 0
0000000FFAB0CBA0 0000000FFAB0CC18 1739 TX 196610 122968 0 6 1470 0 0
0000000FFAAFDFE8 0000000FFAAFE060 1238 TX 196610 122968 0 6 1426 0 0
0000000FFAB20148 0000000FFAB201C0 975 TX 196610 122968 0 6 1585 0 0
0000000FFAB20548 0000000FFAB205C0 969 TX 196610 122968 0 6 1404 0 0
0000000FFAB1AD40 0000000FFAB1ADB8 375 TX 196610 122968 0 6 1585 0 0
Show sessions holding a TX lock:
SQL> SELECT * FROM v$lock WHERE type='TX' AND lmode > 0;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000000FE5372538 0000000FE53725B8 1162 TX 196610 122968 6 0 1631 1 0
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Total Wait Wait % DB Wait
Event Waits Time (sec) Avg(ms) time Class
------------------------------ ----------- ---------- ---------- ------ --------
enq: TX - row lock contention 7 6924,9 989275.83 63.1 Applicat
…
What could be a solution to the lock problem from an application perspective? Any other information we should collect as a DBA to solve that issue?
