1 2 Previous Next 15 Replies Latest reply: Feb 18, 2009 6:34 AM by Charles Hooper RSS

    enq TX - row lock contention

    613179
      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
          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
            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
              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...
                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
                  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
                    Charles Hooper
                    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
                      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
                        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...
                          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
                            Charles Hooper
                            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
                              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
                                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
                                  Charles Hooper
                                  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
                                    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