Oracle Database troubleshooting enq: TX - row lock contentionTracing, debugging and fixing Row Lock...
Unfamiliar notation in Diabelli's "Duet in D" for piano
Is it appropriate to ask a former professor to order a library book for me through ILL?
What is 'Log Memory' in Query Store 2017
What does *dead* mean in *What do you mean, dead?*?
Is there a logarithm base for which the logarithm becomes an identity function?
How to make sure I'm assertive enough in contact with subordinates?
Should I file my taxes? No income, unemployed, but paid 2k in student loan interest
If nine coins are tossed, what is the probability that the number of heads is even?
Is "cogitate" used appropriately in "I cogitate that success relies on hard work"?
Can Witch Sight see through Mirror Image?
Is this Paypal Github SDK reference really a dangerous site?
Giving a talk in my old university, how prominently should I tell students my salary?
Ultrafilters as a double dual
Was it really inappropriate to write a pull request for the company I interviewed with?
Boss Telling direct supervisor I snitched
How does learning spells work when leveling a multiclass character?
A vote on the Brexit backstop
Why do we call complex numbers “numbers” but we don’t consider 2-vectors numbers?
Will the concrete slab in a partially heated shed conduct a lot of heat to the unconditioned area?
Limpar string com Regex
After Brexit, will the EU recognize British passports that are valid for more than ten years?
How to educate team mate to take screenshots for bugs with out unwanted stuff
Interpretation of linear regression interaction term plot
Is there a math expression equivalent to the conditional ternary operator?
Oracle Database troubleshooting enq: TX - row lock contention
Tracing, debugging and fixing Row Lock ContentionsOracle Trace File Interpretation: enq: TX - index contentionHow to troubleshoot enq: TX - row lock contention?enq: TX - row lock contention creating session waitsSelect query on an index column with gap lockingMySQL locking in Duplicate Key ErrorMySQL get a lock to update certain rows and prevent other sessions to read that rowHow to find the query that is still holding a lock?Row Locking in Postgres
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.
- TX lock is acquired when a transaction initiates
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?
oracle locking blocking row
asked Jan 25 at 10:14
r0ttr0tt
40231123
bumped to the homepage by Community♦ 26 secs ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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.
- TX lock is acquired when a transaction initiates
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?
oracle locking blocking row
asked Jan 25 at 10:14
r0ttr0tt
40231123
bumped to the homepage by Community♦ 26 secs ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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.
- TX lock is acquired when a transaction initiates
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?
oracle locking blocking row
asked Jan 25 at 10:14
r0ttr0tt
40231123
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.
- TX lock is acquired when a transaction initiates
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?
oracle locking blocking row
oracle locking blocking row
asked Jan 25 at 10:14
r0ttr0tt
40231123
asked Jan 25 at 10:14
r0ttr0tt
40231123
edited Jan 25 at 12:08
r0tt
asked Jan 25 at 10:14
r0ttr0tt
40231123
asked Jan 25 at 10:14
r0ttr0tt
40231123
asked Jan 25 at 10:14
r0ttr0tt
40231123
40231123
bumped to the homepage by Community♦ 26 secs ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 26 secs ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
From my experience, row lock contention is one the most difficult problem to troubleshoot.
It could be due to a missing commit in the application or missing index on foreign key, for instance.
Here are some questions to help to troubleshoot :
- How do you solve this issue ? Do you kill blocking session ?
- Can you reproduce the problem ? Do you know which action raise this situation ?
- Can you add a trace either at session level, database level or application level ?
answered Jan 25 at 10:42
Sy10100Sy10100
558
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228072%2foracle-database-troubleshooting-enq-tx-row-lock-contention%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
From my experience, row lock contention is one the most difficult problem to troubleshoot.
It could be due to a missing commit in the application or missing index on foreign key, for instance.
Here are some questions to help to troubleshoot :
- How do you solve this issue ? Do you kill blocking session ?
- Can you reproduce the problem ? Do you know which action raise this situation ?
- Can you add a trace either at session level, database level or application level ?
answered Jan 25 at 10:42
Sy10100Sy10100
558
add a comment |
From my experience, row lock contention is one the most difficult problem to troubleshoot.
It could be due to a missing commit in the application or missing index on foreign key, for instance.
Here are some questions to help to troubleshoot :
- How do you solve this issue ? Do you kill blocking session ?
- Can you reproduce the problem ? Do you know which action raise this situation ?
- Can you add a trace either at session level, database level or application level ?
answered Jan 25 at 10:42
Sy10100Sy10100
558
add a comment |
From my experience, row lock contention is one the most difficult problem to troubleshoot.
It could be due to a missing commit in the application or missing index on foreign key, for instance.
Here are some questions to help to troubleshoot :
- How do you solve this issue ? Do you kill blocking session ?
- Can you reproduce the problem ? Do you know which action raise this situation ?
- Can you add a trace either at session level, database level or application level ?
answered Jan 25 at 10:42
Sy10100Sy10100
558
From my experience, row lock contention is one the most difficult problem to troubleshoot.
It could be due to a missing commit in the application or missing index on foreign key, for instance.
Here are some questions to help to troubleshoot :
- How do you solve this issue ? Do you kill blocking session ?
- Can you reproduce the problem ? Do you know which action raise this situation ?
- Can you add a trace either at session level, database level or application level ?
answered Jan 25 at 10:42
Sy10100Sy10100
558
answered Jan 25 at 10:42
Sy10100Sy10100
558
answered Jan 25 at 10:42
Sy10100Sy10100
558
answered Jan 25 at 10:42
Sy10100Sy10100
558
558
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228072%2foracle-database-troubleshooting-enq-tx-row-lock-contention%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
