The database encountered the enq: TX-row lock contention problem.

The database encountered the problem of enq: TX-row lock contention. This morning, we made an awr report and the database encountered a lot of waiting events for enq: TX-row lock contention, it feels strange. Query the wait event in gv $ session [SQL] scrac1/sys @ JSCN> select event, count (*) from gv $ session group by EVENT; www.2cto.com event COUNT (*) ---------------------------------------------------------------- ---------- SQL * Net message from client 275 Streams AQ: waiting for messages in the queue 2 ASM background timer 2 wait for unread message on broadcast channel 1 gcs remote message 12 ges remote message 2 jobq slav E wait 3 rdbms ipc message 34 smon timer 2 pmon timer 2 Streams AQ: qmn slave idle wait 3 class slave wait 2 PX Deq: reap credit 2 PX Deq: Execution Msg 1 Streams AQ: waiting for time management or cleanup tasks 2 Streams AQ: qmn coordinator idle wait 2 DIAG idle wait 2 17 rows selected. this wait EVENT does not appear in the result. Go to ash and check [SQL] scrac1/sys @ JSCN> select SAMPLE_TIME, SESSION_ID, USER_ID, SQL _ID, EVENT, CURRENT_OBJ #, C URRENT_FILE #, CURRENT_BLOCK # from GV _ $ ACTIVE_SESSION_HISTORY where event like 'enq: TX % 'and module = 'jdbc Thin client' and rownum <= 10; 2 SAMPLE_TIME SESSION_ID USER_ID SQL _ID EVENT CURRENT_OBJ # CURRENT_FILE # CURRENT_BLOCK # ------------------------------ ------------ ----------------- ------------ --------------- -------------- www. 2cto.com 02-AUG-12 03.46.26.854 PM 3120 59 a209wq32zy3jw enq: TX-row lock contention 58359 1024 510 02-AUG-12 03.46.26.854 PM 3139 59 then enq: TX-row lock contention 58359 1024 510 02-AUG-12 03.46.26.854 PM 3158 59 a209wq32zy3jw enq: TX-row lock contention 58359 1024 510 02-AUG-12 03.46.26.854 PM 3186 59 then enq: TX-row lock contention 58359 1024 510 02-AUG-12 03.46.26. 854 PM 3194 59 seconds enq: TX-row lock contention 58359 1024 510 02-AUG-12 03.46.26.854 PM 3197 59 minutes enq: TX-row lock contention 58359 1024 510 02-AUG-12 03.46.26.854 PM 3198 59 minutes enq: TX-row lock contention 58359 1024 510 02-AUG-12 03.46.26.854 PM 3201 59 a209wq32zy3jw enq: TX-row lock contention 58359 1024 510 www.2cto.com 02-AUG-12 03.46.26.854 PM 3212 A209wq32zy3jw enq: TX-row lock contention 58359 1024 510 02-AUG-12 03.46.26.854 PM 3213 59 a209wq32zy3jw enq: TX-row lock contention 58359 1024 510 results found many enq: the TX-row lock contention wait events are all from yesterday. What is the problem? We need to view the fields in the GV _ $ ACTIVE_SESSION_HISTORY table in detail. First, we are most likely to think of the SQL _ID field, query select * from V $ SQL where SQL _id = 'a209wq32zy3jw '; your sister did not, no, it was just yesterday's SQL. It should not be deleted from the resshare pool, I thought about whether rac will be connected to another node. [SQL] sele Ct * from GV _ $ SQL where SQL _id = 'a209wq32zy3jw '; scrac1/sys @ JSCN> select INST_ID, SQL _TEXT from GV _ $ SQL where SQL _id = 'a209wq32zy3jw '; INST_ID www.2cto.com ---------- SQL _TEXT certificate ----------------------------------------------------------------------- 1 update QUARTZ. TASK_LOG S ET TASK_ID =: 1, STATUS =: 2, RESULT_DESC =: 3, START_TIME =: 4, END_TIME =: 5 where LOG_ID =: 6 1 update QUARTZ. TASK_LOG SET TASK_ID =: 1, STATUS =: 2, RESULT_DESC =: 3, START_TIME =: 4, END_TIME =: 5 where LOG_ID =: 6 this table and SQL are very strange, we have not seen it before. In GV _ $ ACTIVE_SESSION_HISTORY, there is also a USER_ID = 59 [SQL] scrac1/sys @ JSCN> select USERNAME, USER_ID, CREATED from dba_users where USER_ID = '59'; www.2cto.com USERNAME USE R_ID CREATED ------------------------------ -------------- QUARTZ 59 25-JUN-12 check whether the table has a lock Wait [SQL] scrac1/sys @ JSCN> select * from dba_objects where object_id = '2016 '; OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIM timestamp status t g s ---------------------------------------------------------------------------------------------------------- -------- Scheduled ------------ ----------------- ---------QUARTZ TASK_LOG 58359 58359 TABLE 02-AUG-12 02-AUG-12 2012-08-02: 14: 33: 28 valid n think of it. They used it for scheduled tasks yesterday, testing is ongoing.. The cause has been identified. If this problem occurs in the session, the solution is: solution: 1: Find the user with BLOCK = 1 through the v $ session, and notify the user to submit the transaction 2: Find the pid through the sid, kill the process 3: Change the SQL statement, SELECT * FROM QRTZ_LOCKS WHERE LOCK_NAME =: 1 FOR UPDATE no wait. If there are a large number of similar problems in the current network, remove the human cause, check the application www.2cto.com and check whether the TX-row lock contention class is available on the official website. http://docs.oracle.com/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref76110.3.7.2.4 TX enqueue These are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK. waits for TX in mode 6: occurs when a session is waiting for a row level lock that is already held by another session. this occurs when one user is updating or deleting a row, which another session wishes to update or delete. 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. waits for TX in mode 4 can occur if the session is waiting for an ITL (interested transaction list) slot in a block. this happens when the session wants to lock a row in the block but one or more other sessions have rows locked in the same block, and there is no free ITL slot in the block. usually, Oracle dynamically adds another ITL slot. this may not be possible if there is insufficient free space in the block to add an ITL. if so, the session waits for a slot with a TX enqueue in mode 4. this type of TX enqueue wait corresponds to the wait event enq: TX-allocate ITL entry. the solution is to increase the number of ITLs available, either by changing the INITRANS or MAXTRANS for the table (either by using an ALTER statement, or by re-creating the table with the higher values ). 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 shoshould 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. www.2cto.com Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. bitmap indexes index key values and a range of ROWIDs. each 'entry 'in a bitmap index can cover tables rows in the actual table. if two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. this type of TX enqueue wait corresponds to the wait event enq: TX-row lock contention. waits for TX in Mode 4 can also occur waiting for a PREPARED transaction. waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. this type of TX enqueue wait corresponds to the wait event enq: TX-index contention. 10.3.7 enqueue (enq :) waits Enqueues are locks that coordinate access to database resources. this event indicates that the session is waiting for a lock that is held by another session. the name of the enqueue is encoded as part of the wait event name, in the form enq: enqueue_type-related_details. in some cases, the same enqueue type can be held for different purposes, such as the following related TX types: enq: TX-allocate ITL entry enq: TX-contention enq: TX-index contention enq: TX-row lock contention www.2cto.com The V $ EVENT_NAME view provides a complete list of all the enq: wait events. you can check the following V $ SESSION_WAIT parameter columns for additional information: P1-Lock TYPE (or name) and MODE P2-Resource identifier ID1 for the lock P3-Resource identifier ID2 for the lock author RuleV5