“enq: TX – row lock contention”-case 2

due to “Waits due to Unique or Primary Key Constraint enforcement

–Session#1: ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );

–Session#1: insert into tx_eg values (10,’New’,’MALE’);
–Session#2: insert into tx_eg values (10,’OtherNew’,null);

–DBA:

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type='TX';

SID       TY     ID1         ID2      LMODE     REQUEST    BLOCK
—–    —– — ———- ———- ———- ———- ———-
150     TX     327690   298        0                 4                     0
150     TX     524335   262          6                0                     0
159     TX      327690   298        6                  0                     1

This shows SID 150 is waiting for the TX lock held by SID 159 and it wants the lock in share mode (as REQUEST=4).

check out the v$session_wait for wait events

SQL> select sw.event,sw.sid,sw.wait_class from v$session_wait sw where sw.event like ‘%enq%’;

EVENT                                                       SID                      WAIT_CLASS
—————————–                   ———-          ——————
enq: TX – row lock contention            150                Application

–Ses#1:commit;
–Ses#2:ORA-00001: unique constraint (SCOTT.TX_EG_PK) violated
–Ses#2:rollback;

-EOF-