Handling 'enq: TX - row lock contention'
- Client session 1 : Recording current time and update customers row
SQL> SELECT TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI:SS') ctime FROM DUAL;
CTIME
-----------------
11/24/20 20:41:27
SQL> var V_CUSTOMER_ID number
SQL> exec :V_CUSTOMER_ID := 100
PL/SQL procedure successfully completed.
SQL> UPDATE CUSTOMERS
2 SET CUST_EMAIL = CUST_EMAIL || '' WHERE CUSTOMER_ID = :V_CUSTOMER_ID;
1 row updated.
- client Session 2 : Same update statement run on session.
SQL> UPDATE CUSTOMERS SET CUST_EMAIL = CUST_EMAIL || '' WHERE CUSTOMER_ID =100;
output
C:\Users\varunyadav>sqlplus sys/system123@orcl2 as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 24 20:46:49 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
- Query to check waiter and blocker session
SQL> col SESSIONS format A20
SQL> SELECT DECODE(REQUEST,0,'Holder SID: ','Waiter SID: ') ||
2 SID SESSIONS, ID1, ID2, LMODE, REQUEST, TYPE
3 FROM V$LOCK
4 WHERE (ID1, ID2, TYPE) IN (SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST > 0)
5 ORDER BY ID1, REQUEST;
SESSIONS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder SID: 126 131085 1379 6 0 TX
Waiter SID: 867 131085 1379 0 6 TX
- Information about waiter sessions details
SQL> set pagesize 20
SQL> col INFO for a200
SQL> SELECT
2 'SID: '|| S.SID || CHR(10) ||
3 'USERNAME: '|| S.USERNAME || CHR(10) ||
4 'EVENT: ' || S.EVENT || CHR(10) ||
5 'DESCRIPTION: ' || T.DESCRIPTION || CHR(10) ||
6 'CURRENT STATEMENT: ' || Q.SQL_TEXT || CHR(10) ||
7 'WAITING TIME (s): ' || S.SECONDS_IN_WAIT || CHR(10) ||
8 'P1TEXT: ' || S.P1TEXT || CHR(10) ||
9 'P1: ' || S.P1 || CHR(10) ||
10 'P2TEXT: ' || S.P2TEXT || CHR(10) ||
11 'P2: ' || S.P2 || CHR(10) ||
12 'P3TEXT: ' || S.P3TEXT || CHR(10) ||
13 'P3: ' || S.P3
14 AS INFO
15 FROM V$SESSION S, V$LOCK L, V$LOCK_TYPE T, V$SQL Q
16 WHERE S.SID = L.SID AND T.TYPE=L.TYPE AND S.SQL_ID = Q.SQL_ID
17 AND L.REQUEST>0;
INFO
--------------------------------------------------------------------------------
SID: 867
USERNAME: SOE
EVENT: enq: TX - row lock contention
DESCRIPTION: Lock held by a transaction to allow other transactions to wait for
it
CURRENT STATEMENT: UPDATE CUSTOMERS SET CUST_EMAIL = CUST_EMAIL || '' WHERE CUST
OMER_ID =100
WAITING TIME (s): 291
P1TEXT: name|mode
P1: 1415053318
P2TEXT: usn<<16 | slot
P2: 131085
P3TEXT: sequence
P3: 1379
- Query to retrieve particular affected lock row
SQL> SELECT 'SELECT * FROM "' || O.OWNER || '"."' || O.OBJECT_NAME || '"
2 WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1, ' || S.ROW_WAIT_OBJ# || ', ' ||
3 S.ROW_WAIT_FILE# || ', ' || ROW_WAIT_BLOCK# || ', ' ||
4 ROW_WAIT_ROW# || ');'
5 FROM DBA_OBJECTS O, V$SESSION S
6 WHERE S.ROW_WAIT_OBJ# = O.OBJECT_ID AND S.SID = &V_WSID;
Enter value for v_wsid: 867
old 6: WHERE S.ROW_WAIT_OBJ# = O.OBJECT_ID AND S.SID = &V_WSID
new 6: WHERE S.ROW_WAIT_OBJ# = O.OBJECT_ID AND S.SID = 867
'SELECT*FROM"'||O.OWNER||'"."'||O.OBJECT_NAME||'"WHEREROWID=DBMS_ROWID.ROWID_CRE
--------------------------------------------------------------------------------
SELECT * FROM "SOE"."CUSTOMERS"
WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1, 170508, 8, 4816, 38);
- Rollback client session 1:
SQL>rollback;
SQL> SELECT DECODE(REQUEST,0,'Holder SID: ','Waiter SID: ') ||
2 SID SESSIONS, ID1, ID2, LMODE, REQUEST, TYPE
3 FROM V$LOCK
4 WHERE (ID1, ID2, TYPE) IN (SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST > 0)
5 ORDER BY ID1, REQUEST;
no rows selected
SQL>
- ASH report of particular locking periods:
define dbid = '';
define inst_num = '';
define report_type = 'html';
define begin time
define begin_time ='-10'
define duration = 10;
define report_name = 'C:\Users\varunyadav\ash_enqtx.html';
define slot_width = '';
define target_session_id = '';
define target_sql_id = '';
define target_wait_class = '';
define target_service_hash = '';
define target_module_name = '';
define target_action_name = '';
define target_client_id = '';
define target_plsql_entry = '';
define target_container = '';
@?\rdbms\admin\ashrpti.sql
- Query for enqueue wait events since last database start
SQL> col EVENT format a40
SQL> col WAIT_CLASS format a11
SQL> SELECT EVENT, AVERAGE_WAIT,
2 TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS, WAIT_CLASS
3 FROM V$SYSTEM_EVENT
4 WHERE EVENT LIKE 'enq%'
5 ORDER BY TIME_WAITED;
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
enq: SQ - contention .14 0 Configurati
on
enq: PV - syncstart 1.35 0 Other
enq: CF - contention 5.34 0 Other
enq: WF - contention 20.17 0 Other
enq: CR - block range reuse ckpt .21 1 Other
enq: RO - fast object reuse .29 3 Application
enq: PR - contention 18.46 6 Other
enq: JG - queue lock 5.76 12 Other
enq: TX - row lock contention 233833.06 7,015 Application
9 rows selected.
SQL>
- Query to check object waited for lock to be released
SQL> col OBJECT_NAME for a10
SQL> col stats for a20
SQL> SELECT OBJECT_NAME, SUBSTR(STATISTIC_NAME, 1, 30) STATS, VALUE
2 FROM V$SEGMENT_STATISTICS
3 WHERE ( STATISTIC_NAME IN( 'ITL waits' , 'row lock waits' ))
4 AND VALUE>0 AND OBJECT_NAME NOT LIKE 'BIN$%'
5 ORDER BY VALUE DESC;
OBJECT_NAM STATS VALUE
---------- -------------------- ----------
EMP row lock waits 2
CUSTOMERS row lock waits 1
SQL>
- Query for most waited wait events of application class
SQL> col SQL_TEXT for a30
SQL> SELECT ROUND(APPLICATION_WAIT_TIME / 1000000) WAIT_TIME_S,
2 SQL_ID, SUBSTR(SQL_TEXT,1,30) SQL_TEXT
3 FROM V$SQLSTATS
4 WHERE APPLICATION_WAIT_TIME >0
5 ORDER BY APPLICATION_WAIT_TIME DESC FETCH FIRST 10 ROW ONLY;
WAIT_TIME_S SQL_ID SQL_TEXT
----------- ------------- ------------------------------
5372 9b21yxhcn6r9d UPDATE EMP SET SALARY=SALARY W
948 fwwngr2fq76ap UPDATE EMP SET SALARY=SALARY
695 1d0m79rpx5h4j UPDATE CUSTOMERS SET CUST_EMAI
2 6mcpb06rctk0x call dbms_space.auto_space_adv
1 b6usrg82hwsa3 call dbms_stats.gather_databas
SQL>