enq: TX - row lock contention

When a session updates a row in a table the row is locked by the sessions transaction. Other users may SELECT that row and will see the row as it was BEFORE the UPDATE occurred. If another session wishes to UPDATE the same row it has to wait for the first session to commit or rollback. The second session waits for the first sessions TX lock in EXCLUSIVE mode.

TX- row lock contention is most common lock which occurs when another transaction is trying to lock sme rows in a block for UPDATE/DELETE .

This usually is an application issue. The waiting session will wait until the blocking session commits or rolls back its transaction.
There is no other way to release the lock. (Killing the blocking session will cause its transaction to be rolled back.)
TX lock 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. Please work with Developers to fix the code and to alleviate TX lock waits.

1=>> Waits due to Row being locked by an active Transaction (enq: TX - row lock contention)

DEMONSTRATION

Step 1=> create Sample table as below and insert records,

SQL> create table test_enq(id int);

Table created.

SQL> insert into test_enq values (1);

1 row created.

SQL>  insert into test_enq values (2);

1 row created.

SQL>  insert into test_enq values (3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_enq;

    ID
----------
     1
     2
     3

Step 2=> Take 1 more session and try to update rows as below,

Sess#1 ->

SQL> update test_enq set ID=10 where id=1;

1 row updated.

SQL>

Sess#2 ->

SQL> update test_enq set ID=10 where id=1;

Waiting !!!!

Now Sess#2 will be in waiting mode


Step 3=> Login from DBA account and check wait event as below

SQL> Select sid,SERIAL#,event,username,INST_ID from gv$session where username ='ABHI_TEST';

       SID    SERIAL# EVENT                                   USERNAME                        INST_ID
---------- ---------- ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------
        24    35449 enq: TX - row lock contention                       ABHI_TEST                     1
       156    32390 SQL*Net message from client                       ABHI_TEST                     1

SQL>

Let we check Holder details as below.

SQL> SELECT sid,type,id1,id2,lmode,request FROM gv$lock WHERE type='TX';

       SID TY         ID1    ID2     LMODE      REQUEST
---------- -- ---------- ---------- ---------- ----------
    24 TX      327703    936         0        6
       279 TX      327703    936         6        0


Solution => Either Developer has to change code or Kill blocking session.


2=>> Waits due to Unique or Primary Key Constraint Enforcement(enq: TX - row lock contention)

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.

DEMO

==> Add Primary Key for demo table.

SQL> ALTER TABLE TEST_TSPITR ADD CONSTRAINT TEST_TSPITR_PK PRIMARY KEY( IND );

Table altered.

SQL> desc TEST_TSPITR
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 IND                                       NOT NULL NUMBER(38)

==> Sess#1

SQL> sELECT sid FROM v$session WHERE audsid = userenv('sessionid');

       SID
----------
       977

==> Sess#2

SQL> sELECT sid FROM v$session WHERE audsid = userenv('sessionid');

       SID
----------
         8
==> insert Same value from both session as below.

Sess#2 ->

SQL>  insert into TEST_TSPITR values (4);

1 row created.

Sess#1 -> 

SQL>  insert into TEST_TSPITR values (4);

Waiting !!!!!!!!!!!

==> Check Wait details from DBA user as below.

SQL> select username,event,sid from gv$session where sid in (977,8);

USERNAME             EVENT                                                                   SID
-------------------- ---------------------------------------------------------------- ----------
ABHI_TEST            SQL*Net message from client                                               8
ABHI_TEST            enq: TX - row lock contention                                           977

As we can see session is waiting and there is EVENT "enq: TX - row lock contention" .

==> Let we check Lock details as below.

SQL> select SID,LMODE,REQUEST from v$lock where SID in(977,8) order by 1 desc;

       SID      LMODE    REQUEST
---------- ---------- ----------
       977          3          0
       977          0          4
       977          6          0
       977          4          0
         8          3          0
         8          6          0
         8          4          0

7 rows selected.

SQL>

Here we can see SID 8 HAS GONE through  Lock mode (3-> row-X(SX)   to 6-> Exclusive(X) to 4-> Share (S) ) And SID 977 is on waiting through (4 -> Share(S)  to 6-> Exclusive(X) to 4-> Share (S)).