4 Replies Latest reply: May 7, 2008 2:24 PM by Charles Hooper RSS

    v$system_event, timeouts and row lock contention

    plhtyuk
      Hello everyone,

      What is the meaning of the total_timeouts column in the v$system_event view if it is related to the event "enq: TX - row lock contention"?

      How can we have a timeout since "query timeout" is a non existent concept on Oracle?

      Maybe I am wrong.

      Regards.

      Carl
        • 1. Re: v$system_event, timeouts and row lock contention
          608334
          Please refer link:

          http://www.oracle.com/technology/oramag/oracle/04-jul/o44tech_perf.html
          • 2. Re: v$system_event, timeouts and row lock contention
            Charles Hooper
            Hello everyone,

            What is the meaning of the total_timeouts column in
            the v$system_event view if it is related to the event
            "enq: TX - row lock contention"?

            How can we have a timeout since "query timeout" is a
            non existent concept on Oracle?

            Maybe I am wrong.

            Regards.

            Carl
            The value for timeouts, in this case, indicates the number of times one of the sessions had to wait a full 3 second time slice while waiting for a second session to either commit or roll back, in anticipation of a potential primary key violation. If the second session commits, the first session will receive an error indicating a primary key violation.

            Test setup:
            In session 2:
            CREATE TABLE T1 (C1 VARCHAR2(20) PRIMARY KEY);

            INSERT INTO T1 VALUES ('ORACLE');

            In session 1:
            INSERT INTO T1 VALUES ('ORACLE');

            After executing tha above, allow 60 seconds to elapse after the insert in session 1, and then check the delta values in either V$SYSTEM_EVENT or V$SESSION_EVENT:
            20 waits on ENQ: TX - ROW LOCK CONTENTION, 19 timeouts, 59.99 seconds, with an average wait of 2.9995 seconds.

            Charles Hooper
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 3. Re: v$system_event, timeouts and row lock contention
              plhtyuk
              Thanks a lot Charles.

              Where did you find this information?

              Best regards.

              Carl
              • 4. Re: v$system_event, timeouts and row lock contention
                Charles Hooper
                Thanks a lot Charles.

                Where did you find this information?

                Best regards.

                Carl
                I saw a post by Jonathan Lewis a month or so ago, maybe on his blog, related to this wait event and the duration of the timeouts. The meaning of the wait event is described in the Oracle manuals, but it is hard to find in the 10g manuals. The description from the 11g Performance Tuning Guide:
                http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/instance_tune.htm#i16341
                "Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates in 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. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

                The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK."

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