Performance tuning test for Wait Event enq: TX – row lock contention.
Posted by Mir Sayeed Hassan on December 3rd, 2017
Performance tuning test for Wait Event enq: TX – row lock contention.
Simulate step by step procedure for Wait Event enq: TX – row lock contention, by updated (locking) row in 1st session & while trying to update the 2nd session with same row at the same
Consider the below example tested in our test env by using the 2 different session with same user as “MIR”.
Open the session 1 as “MIR” & try to update the record as shown below:
Session 1:
[oracle@oracleTestServer ~]$ !sq SQL> connect mir/mir; Connected.
SQL> sho user USER is "MIR"
SQL> select * from test1; ENO NAME ---------- -------------------- 1 mir 2 sayeed 4 hassan
SQL> update test1 set eno=3 where name='hassan'; 1 row updated.
Now login to the session 2 with same user as “MIR” & try to update the same record
Session 2:
[oracle@oracleTestServer ~]$ !sq SQL> connect mir/mir; Connected
SQL> sho user USER is "MIR"
SQL> update test1 set eno=3 where name='hassan';
(HUNG) — Since the row is locked by session 1, so the session 2 is going to wait, hence the lock contention occur & will analyze the cause in details
Analyze/debug the problem by using the sys user by using the command prompt
Get the statistic of the instance level with statistic wait time
[oracle@oracleTestServer ~]$ sqlplus / as sysdba SQL> show user USER is "SYS"
SQL> desc v$sysstat; Name Null? Type ----------------------------------------- -------- ---------------------------- STATISTIC# NUMBER NAME VARCHAR2(64) CLASS NUMBER VALUE NUMBER STAT_ID NUMBER
SQL> select NAME,VALUE from v$sysstat where CLASS=1 and NAME='application wait time'; NAME VALUE ---------------------------------------------------------------- ---------- application wait time 136291
SQL> / NAME VALUE ---------------------------------------------------------------- ---------- application wait time 154891 --
— You can see with above query as its increase the wait time because the application wait time Increasing as & other session is halt/hung
Now check with dynamic performance view as v$session, where its shows all the session connected to the database
SQL> select username,sid from v$session where username='MIR'; USERNAME SID ------------------------------ ---------- MIR 165 MIR 319
As per the above query, its shows two session connected with different sid, therefore check the session level statistics with wait time as v$sysstat
SQL> select s.sid,n.name,s.value from v$sesstat s,v$statname n where s.sid in(165,319) and n.name='application wait time' and n.STATISTIC#=s.STATISTIC#; SID NAME VALUE ---------- ------------------------- ------------------------------------ 165 application wait time 0 -- This conclude the session 2
319 application wait time 215701 — This conclude the session 1 & its increasing value of waiting
Hence the session 1 is updated query at first so its increasing the wait time, then session 2 is started & updated so the value is “0”
To capture the wait event information at instance level uses by V$system_event
SQL> select event,time_waited from v$system_event where event='enq: TX - row lock contention'; EVENT TIME_WAITED ---------------------------------------------------------------- ----------- enq: TX - row lock contention 348682 ---- Time taken for wait to complete the session 1
V$session_wait – it will be capture in two dimension which has already occur & in wait which are currently process
SQL> select sid,event,state,seconds_in_wait waiting_for from v$session_wait where sid in(165,319); SID EVENT STATE WAITING_FOR -------------------------------------------------------------------------------------------------------- 165 SQL*Net message from client WAITING 3297 --- its waiting for client to send some command to process 319 enq: TX - row lock contention WAITING 4043 --- Currently waiting for wait event
Get the which session is blocking
SQL> select blocking_session from v$session where sid=319; BLOCKING_SESSION ---------------- 165
Therefore the session 165 is blocking the session 319
Get the table whose row has been locked
SQL> select row_wait_obj# object_id,row_wait_file# relative_fno,row_wait_block# block_number from v$session where sid=319; OBJECT_ID RELATIVE_FNO BLOCK_NUMBER ---------- ------------ ------------ 297948 4 523
Get the table whose row is locked
SQL> col object_type for a30
SQL> col table_name for a30
SQL> select owner,object_type, object_name table_name, data_object_id from dba_objects where object_id=297948; OWNER OBJECT_TYPE TABLE_NAME DATA_OBJECT_ID ------------------------------ --------------------------------------------------------------------------------- MIR TABLE TEST1 299192
Get which machine the user logged in, session is holding lock, waiting for lock, service_name etc
SQL> col osuser for a30 SQL> col terminal for a30 SQL> col service_name for a30 SQL> col machine for a30 SQL> select sid,osuser,machine,terminal,service_name,logon_time from v$session where username='MIR'; SID OSUSER MACHINE TERMINAL SERVICE_NAME LOGON_TIM ------------------------------ --------------------------------------------------------------------------- --------- 165 oracle oracleTestServer.local pts/0 SYS$USERS 03-DEC-17 319 oracle oracleTestServer.local pts/1 SYS$USERS 03-DEC-17
Finally get the SQL Statement issue by the session.
SQL> select sql_id from v$session where sid=319; SQL_ID ------------- 898t9d7ma565y
SQL> set long 9999 SQL> select sql_fulltext from v$sql where sql_id='898t9d7ma565y'; SQL_FULLTEXT -------------------------------------------------------------------------------- update test1 set eno=3 where name='hassan'
Solution:
The 1st session already holding the lock performs a COMMIT or ROLLBACK.
Note:
If two sessions want to update rows at the same time, then the 2nd session waits for the first transaction to either perform commit or rollback by waiting for the TX lock. This type of TX enqueue wait corresponds to the wait event enq: TX – row lock contention.
Hence we conclude that the session 2 is waiting for the session 1 to complete the process, therefore the session 2 is in Wait Event enq: TX – row lock contention status
==========END==========