This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 18, 2009 4:34 AM by CharlesHooper RSS

enq TX - row lock contention

613179 Newbie
Currently Being Moderated
Hi,

I have a table:

MyTable (
f1     varchar2(4),
f2     varchar2(2),
f3     varchar2(10),
f4     varchar2(4),
f5     number(2),
f6     date,
f7     varchar(2)
PrimaryKey (f1,f2))

the table has 1 b-tree and 2 bitmap inserts.
I receive enq TX - row lock contention during insert

insert into MyTable (f1,f2,f6) values (:a,:b,sysdate);

and the insertion is too slow. The only wait observed on the database is row lock contention which has a huge occurrence (more than 512msec waits). Can you help me with this problem. How does the inserts affect row locks and what is the reason for waits?
regards
  • 1. Re: enq TX - row lock contention
    561093 Oracle ACE
    Currently Being Moderated
    Hi,

    Even INSERT's can cause "enq TX - row lock contention" locks and it is evident in your case.
    How does the inserts affect row locks and what is the reason for waits?
    This is how INSERT's cause "enq TX - row lock contention" locks:

    Session 1:
    SQL> conn test/test
    Connected.
    SQL> create table t1 ( a number primary key);
    
    Table created.
    
    SQL> insert into t1 values (1);
    
    1 row created.
    
    SQL>
    Session 2:
    SQL> conn test/test
    Connected.
    SQL> insert into t1 values (1);
    As a Primary Key constraint has been applied to the table T1 on column A, your second session simply hangs (waiting for the first session to commit or rollback).

    At this time, when you query V$SESSION you will see "enq TX - row lock contention" lock.
    SQL> @wait
    
    SID_SER_USER           PROGRAM                   EVENT                          STATUS   LAST_CALL_ET SQL_ID       
    ---------------------- ------------------------- ------------------------------ -------- -----------
    37 -  9 - TEST         sqlplus.exe               enq: TX - row lock contention  ACTIVE            102 5wgdtttp8rmdc
    Hope you got it now.


    Regards

    Asif Momen
    http://momendba.blogspot.com
  • 2. Re: enq TX - row lock contention
    Tubby Guru
    Currently Being Moderated
    Give Tom's first response a read (and the rest of the thread if you'd like).

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6042872196732

    Also maybe check out the reply (ctrl-f for it) in which he says "Bitmaps and multi-users = oxymoron of the highest degree."

    Does that help?

    Edited by: Tubby on Feb 16, 2009 1:11 PM
  • 3. Re: enq TX - row lock contention
    613179 Newbie
    Currently Being Moderated
    Dear Citrus

    1. Regarding to your comment, the first session locks the row and blocks other sessions from inserting. So why should such a simple insert last so much (the database server is a powerful one and there is no other wait class on it) and make the other sessions wait so much. Is it an application code problem which does not commit or rollback rapidly? The rate of insertion is less that 1000 records/hour!

    2. I renamed the table and created a similar one with a similar name, without index and even Primary Key, but no improvement in performance :( The contention exists even without primary key!!

    The stranger thing is that this application with this database used to work properly for about 2 years, maybe someone has changed something and I have to find out. I think this is an abnormal situation. Can you help me please :(
  • 4. Re: enq TX - row lock contention
    Anand... Guru
    Currently Being Moderated
    Hi..

    refer to [http://support.confio.com/blog/post/solving-waits-on-enq-tm---contention/8/]


    Anand
  • 5. Re: enq TX - row lock contention
    613179 Newbie
    Currently Being Moderated
    Dear Anand and Tubby
    There is no foriegn keys for the table. There is also no deadlocks.
    The situation is getting critical. Please help.
  • 6. Re: enq TX - row lock contention
    CharlesHooper Expert
    Currently Being Moderated
    armizi wrote:
    Dear Anand and Tubby
    There is no foriegn keys for the table. There is also no deadlocks.
    The situation is getting critical. Please help.
    Problems with missing indexes on foreign keys would show as TM enqueues, but you are indicating problems with TX enqueues. You need to determine the currently held MODE and the requesting MODE of the enqueue, as well as the object(s) involved. See my posts in the following threads for assistance (note that the formatting of some of the posts are poor following the conversion of the Oracle forums last year):
    Tuning row lock contention wait events
    Re: enq TX  locked row
    Re: enqueue and logfile parallel write

    It could be a primary key problem; a unique index problem; a trigger firing attempting to insert, delete, or update a row; a problem with initrans; waiting for an index split to complete; problems with bitmap indexes; updating a row already updated by another session; or something else. You need to target sampling of V$SESSION_WAIT, V$LOCK, and V$SESSION; or a 10046 trace of the sessions at level 8 or 12 while the enqueue is in process.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 7. Re: enq TX - row lock contention
    613179 Newbie
    Currently Being Moderated
    I dropped two bitmap indexes and the row lock contention for the insert is eliminated. But the insert operation is still too slow. There is only enq TX - row lock contention on a delete operation on another table. No other wait class is observed in the ADDM report.
    Is it possible that the contention on the other table cause the slow operation of inset in this table?
  • 8. Re: enq TX - row lock contention
    Tubby Guru
    Currently Being Moderated
    Sorry you took my post as a deadlock issue, although that was the original thread of it, it got into bitmap indexes and how they cause contention, which was the issue you were seeing, i guess i should have specified that a little more and relied on you reading the thread a little less.

    With the indexes dropped, and you having issues with it 'taking too long' you'll likely have to trace the operation to see where your time is being spent.

    Doing a 10046 trace and enabling wait events would likely be a good route to start on.
  • 9. Re: enq TX - row lock contention
    Anand... Guru
    Currently Being Moderated
    Hi..

    Can you check whether the table is having some trigger enabled on it as insert operation is taking time.Also, enable 10046 tracing on the session doing the insert.

    [http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php]


    Anand

    Edited by: Anand... on Feb 17, 2009 11:15 PM
  • 10. Re: enq TX - row lock contention
    CharlesHooper Expert
    Currently Being Moderated
    armizi wrote:
    I dropped two bitmap indexes and the row lock contention for the insert is eliminated. But the insert operation is still too slow. There is only enq TX - row lock contention on a delete operation on another table. No other wait class is observed in the ADDM report.
    Is it possible that the contention on the other table cause the slow operation of inset in this table?
    The row lock contention is likely just a side effect of another problem - the slow insert by another session.

    As suggested in my previous post, and the follow-up by Anand, switch to a 10046 trace at level 8 or 12 to determine the source of the problem. You know that inserts are taking a long time, so it is reasonable to have the problem narrowed down to just a couple sessions. Find the SID and SERIAL# for the sessions by querying V$SESSION, and then see my posts in the following links to enable and read a trace:
    Enabling a 10046 trace:
    Re: SQL_TRACE help to newbie in oracle

    Reading a 10046 trace:
    Re: some 10046 traceing questions.....

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 11. Re: enq TX - row lock contention
    613179 Newbie
    Currently Being Moderated
    The table has no tigger. I've got some trace and i'm studying these files.
    thank you

    Edited by: armizi on Feb 18, 2009 11:26 AM
  • 12. Re: enq TX - row lock contention
    613179 Newbie
    Currently Being Moderated
    Regarding to your advices, I studied 10046 SQL traces generated with level 12 and 8. I found out this suspicious entry in the trace files:

    WAIT #0: nam='SQL*Net message from client' ela= 1661116 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949646618059
    and:
    WAIT #0: nam='SQL*Net message from client' ela= 5175611 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949651882744


    Am I correct, are these elapsed times too huge for this event? All other events had very small elapsed times. If this is true, what is the next step?
    I'm really under pressure. Please help
  • 13. Re: enq TX - row lock contention
    CharlesHooper Expert
    Currently Being Moderated
    armizi wrote:
    Regarding to your advices, I studied 10046 SQL traces generated with level 12 and 8. I found out this suspicious entry in the trace files:

    WAIT #0: nam='SQL*Net message from client' ela= 1661116 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949646618059
    and:
    WAIT #0: nam='SQL*Net message from client' ela= 5175611 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949651882744


    Am I correct, are these elapsed times too huge for this event? All other events had very small elapsed times. If this is true, what is the next step?
    I'm really under pressure. Please help
    In a trace file, the above lines may be very important, and may explain why you are seeing the enqueue waits.

    The first of the waits indicates that the database server waited for the user (or the user's computer or the network) for 1.661116 seconds. The second of the waits indicates that the database server waited for the user for 5.175611 seconds. This may mean that the application displayed something on the user's screen, and then waited for the user to click OK, or type in a little bit of information such as the last invoice processed. The 'SQL*Net message from client' wait indicates that the delay is likely not in the database server, but resides somewhere between the network stack on the server and the user's keyboard. It might also indicate that the user's computer is searching for some information, possibly a data file, DLL file, or unused temporary file name.

    While this session is delaying for 1.661116 seconds and 5.175611 seconds, other sessions which are attempting to perform the same task might be waiting to change the same data, or insert into the same database tables with the same primary key values used by the session waiting for the user. If you are able to also trace the other sessions, you may look at the P1, P2, and P3 values of the enqueue wait event (enq TX - row lock contention) to determine the table (or index), block, and possibly the row within the block which is the source of the contention.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

    Edited by: Charles Hooper on Feb 18, 2009 7:18 AM
    Small correction, the P1, P2, and P3 values of the enqueue do not give the detail down to the block and row level - you need to check the other session for that, or query V$SESSION for this session.

    TYPE=TX, LMODE=6, REQUEST=6 = session 2 is attempting to update a row that is locked by session 1's transaction (likely session 1 already updated the row)
    TYPE=TX, LMODE=6, REQUEST=4 = session 2 is attempting to insert a row with the same primary key value or unique index value as a row just inserted by session 1
    TYPE=TX, LMODE=6, REQUEST=4 = ITL Problem or bitmap index fragment problem

    RBS Seg # = trunc(p2/65536) = v$transaction.xidusn
    RBS Slot # = bitand( p2, 65535) = v$transaction.xidslot
    P3 = v$transaction.xidsqn
  • 14. Re: enq TX - row lock contention
    613179 Newbie
    Currently Being Moderated
    Thank you Charls,
    This is the whole contents of the trace file:

    +=====================+
    PARSING IN CURSOR #1 len=488 dep=1 uid=0 oct=42 lid=0 tim=1205949644948218 hv=2793206049 ad='dd9af9f8'
    ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '03:30' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT= 'DD-MON-RR HH.MI.SSXFF AM TZR'+
    END OF STMT
    PARSE #1:c=1000,e=1138,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1205949644948210
    EXEC #1:c=1000,e=203,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1205949644948767
    WAIT #0: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949644949153
    WAIT #0: nam='SQL*Net message from client' ela= 292 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949644949503
    WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949644949556
    WAIT #0: nam='SQL*Net message from client' ela= 211 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949644949789
    WAIT #1: nam='library cache pin' ela= 258 handle address=29487719968 pin address=28260285784 100*modenamespace=201 obj#=-1 tim=1205949644951245+
    WAIT #2: nam='library cache pin' ela= 104 handle address=29487717968 pin address=28260286192 100*modenamespace=201 obj#=-1 tim=1205949644952125+
    WAIT #3: nam='library cache pin' ela= 97 handle address=29487714512 pin address=28260285512 100*modenamespace=201 obj#=-1 tim=1205949644952585+
    +=====================+
    PARSING IN CURSOR #3 len=45 dep=2 uid=0 oct=3 lid=0 tim=1205949644953377 hv=3216441027 ad='dd9ad970'
    select inst_id,ksmsdnam,ksmsdval from x$ksmsd
    END OF STMT
    PARSE #3:c=0,e=1125,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1205949644953371
    +=====================+
    PARSING IN CURSOR #2 len=68 dep=1 uid=0 oct=3 lid=0 tim=1205949644954307 hv=752714909 ad='ded69db8'
    select  NAME , VALUE from GV$SGA where inst_id = USERENV('Instance')
    END OF STMT
    PARSE #2:c=2000,e=2781,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1205949644954303
    +=====================+
    PARSING IN CURSOR #1 len=281 dep=0 uid=0 oct=3 lid=0 tim=1205949644955553 hv=321840211 ad='dd9aee88'
    SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA,   SUM(VALUE), DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA    UNION ALL    SELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE,    DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA
    END OF STMT
    PARSE #1:c=4999,e=5715,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1205949644955547
    EXEC #1:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1205949644955746
    WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949644955799
    FETCH #1:c=0,e=101,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1205949644955949
    WAIT #1: nam='SQL*Net message from client' ela= 270 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949644956295
    WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949644956359
    FETCH #1:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=4,dep=0,og=1,tim=1205949644956389
    WAIT #1: nam='SQL*Net message from client' ela= 283 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949644956750
    STAT #1 id=1 cnt=5 pid=0 pos=1 obj=0 op='UNION-ALL  (cr=0 pr=0 pw=0 time=130 us)'
    STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=99 us)'
    STAT #1 id=3 cnt=4 pid=2 pos=1 obj=0 op='FIXED TABLE FULL X$KSMSD (cr=0 pr=0 pw=0 time=55 us)'
    STAT #1 id=4 cnt=4 pid=1 pos=2 obj=0 op='FIXED TABLE FULL X$KSMSD (cr=0 pr=0 pw=0 time=19 us)'
    WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949644956917
    WAIT #0: nam='SQL*Net message from client' ela= 1661116 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949646618059
    +=====================+
    PARSING IN CURSOR #3 len=38 dep=0 uid=0 oct=49 lid=0 tim=1205949646618671 hv=1669374295 ad='ded697d0'
    alter system set event='' scope=spfile
    END OF STMT
    PARSE #3:c=1000,e=499,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1205949646618666
    EXEC #3:c=18997,e=87823,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1205949646706555
    WAIT #3: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949646706736
    WAIT #3: nam='SQL*Net message from client' ela= 233 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949646707004
    WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949646707108
    WAIT #0: nam='SQL*Net message from client' ela= 5175611 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1205949651882744
    ======================================================================================
    ======================================================================================

    The application is a C program running on two Linux servers and reading some receiving files and extract and insert data into uload_trans_log table. So there is no human user, also the application is not changed for a long time and has been working for almost two years! It used to work properly during this time.
    As I mentioned before, after dropping two bitmap indexes, there is no row lock contention on this table anymore. There is a little row lock contention on another table (which is a delete statement, initiated via this application).
    Can you guide me with the next step please.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points