This is a very simple question. I have summarized this question. First, it was because it was not as simple as I thought. During the process, I encountered some bumps and bumps, and even made some detours, second, I think about troubleshooting. June 19, around five o'clock P.M., a large number of enq: TX appeared in the database
This is a very simple question. I have summarized this question. First, it was because it was not as simple as I thought. During the process, I encountered some bumps and bumps, and even made some detours, second, I think about troubleshooting. A large number of enq: TX-row lock contention wait events occurred in the database at around five o'clock P.M. on January 1, June 19,
This is a very simple question. I have summarized this question. First, it was because it was not as simple as I thought. During the process, I encountered some bumps and bumps, and even made some detours, second, I think about troubleshooting.
At around five o'clock P.M. on July 15, June 19, a large number of enq: TX-row lock contention wait events occurred in the database. According to past experience, such waits are generally related to business logic and what DBAs can do, generally, it refers to the connection information waiting for the lock, the SQL statement waiting for the lock, or even the specific data row waiting for, and the connection information of the lock holder, the SQL statements that cause lock wait and other basic information are submitted to the developer to modify the business logic.
Note that in many cases, modifying the business logic is not something that can be solved immediately at half past one. Sometimes, if the lock hold or lock wait business is not particularly important, you can negotiate with the application maintenance personnel, stop this part of the business first, so that other businesses can run normally. In many cases, the database has only one lock holder, and the connection held by this lock is not active. You can negotiate with the application maintenance personnel, try to kill the lock owner to see if the subsequent lock wait can be automatically solved. This situation is generally caused by the freezing of a separate connection. After the owner is killed, the problem will naturally be solved. Another situation is that after going online the night before yesterday, we usually run a good business, no lock wait occurs either. One day, this kind of problem occurs, and after the lock owner is killed, the problem cannot be solved (the business logic problem will happen in the future after the lock is killed ), this is generally caused by the launch last night. I thought it was very simple to provide information at the database layer. The results are a little different from what I imagined. Let's take a look at the specific process (1) to query the lock information, as shown below:
| SESS |
LMODE |
LMODE |
REQUEST |
TYPE |
EVENT |
SQL _TEXT |
| Holder: 4266 |
Exclusive |
6 |
0 |
TX |
SQL * Net message from client |
|
| Waiter: 3136 |
None |
0 |
4 |
TX |
Enq: TX-row lock contention |
Insert into xxxxx (ID, xxx, xxxx, xxx ,....) Values (seq_xxx.nextval,: "SYS_ B _0",: "SYS_ B _1",: "SYS_ B _2",: "SYS_ B _3",: "SYS_ B _4",: "SYS_ B _5 ") |
| Holder: 2276 |
Exclusive |
6 |
0 |
TX |
SQL * Net message from client |
|
| Waiter: 1716 |
None |
0 |
4 |
TX |
Enq: TX-row lock contention |
Insert into xxxxx (ID, xxx, xxxx, xxx ,....) Values (seq_xxx.nextval,: "SYS_ B _0",: "SYS_ B _1",: "SYS_ B _2",: "SYS_ B _3",: "SYS_ B _4",: "SYS_ B _5 ") |
| Holder: 1288 |
Exclusive |
6 |
0 |
TX |
SQL * Net message from client |
|
| Waiter: 1565 |
None |
0 |
4 |
TX |
Enq: TX-row lock contention |
Insert into xxxxx (ID, xxx, xxxx, xxx ,....) Values (seq_xxx.nextval,: "SYS_ B _0",: "SYS_ B _1",: "SYS_ B _2",: "SYS_ B _3",: "SYS_ B _4",: "SYS_ B _5 ") |
| Holder: 1000 |
Exclusive |
6 |
0 |
TX |
SQL * Net message from client |
|
| Waiter: 1147 |
None |
0 |
4 |
TX |
Enq: TX-row lock contention |
Insert into xxxxx (ID, xxx, xxxx, xxx ,....) Values (seq_xxx.nextval,: "SYS_ B _0",: "SYS_ B _1",: "SYS_ B _2",: "SYS_ B _3",: "SYS_ B _4",: "SYS_ B _5 ") |
| Holder: 2989 |
Exclusive |
6 |
0 |
TX |
SQL * Net message from client |
|
| Waiter: 862 |
None |
0 |
4 |
TX |
Enq: TX-row lock contention |
Insert into xxxxx (ID, xxx, xxxx, xxx ,....) Values (seq_xxx.nextval,: "SYS_ B _0",: "SYS_ B _1",: "SYS_ B _2",: "SYS_ B _3",: "SYS_ B _4",: "SYS_ B _5 ") |
Note: after the table name and column name are blurred, the lock wait statement is waiting for Insert records (2) view the lock owner and executed statements, to determine whether the statement causes the lock wait. The query statement is as follows: select B. SQL _text,. * from v $ open_cursor a, v $ SQL B where. SQL _id = B. SQL _id and. sid = 4266 and upper (B. SQL _text) like '% xxxxx %'; (3) an insert statement is blocked based on experience, it is generally caused by primary key constraints (the same statement is inserted in another connection or a statement is deleted but not submitted). However, when I use the preceding statement to query, I found that I could not find any dml of the table executed by the lock owner. I also asked the developers that they did not perform DML operations on the table, where open_cursor is 1000, the number of records in v $ open_cursor is far from the same. Only 100 records are missing. Session_cached_cursors is set to 200. It is unreasonable that the statement cursor executed by this connection has been refreshed. (4) no similar problem has been encountered and cannot be found. Now I changed my mind. Aside from those experiences, I was wondering, is there a possibility that I would not perform any DML operations on the Insert statement, which would also cause an Insert statement to be locked ?? I have considered whether the dependent objects of this table will cause type waits, such as triggers and foreign key references. After careful consideration, the database should be able to locate the specific statement to discover the trigger and audit, rather than the insert statement itself (even recursive statements can be captured by Oracle ),
Therefore, what I suspect most is the foreign key reference.The following query is used to determine whether the table references other objects through the foreign key: select. table_name,. owner,. constraint_name,. constraint_type,. r_owner,. r_constraint_name, -- Name of the constraint referenced by the foreign key B. table_name -- Name of the table referenced by the foreign key from dba_constraints a, dba_constraints B where. constraint_type = 'r' and. r_constraint_name = B. constraint_name and. r_owner = B. owner and B. table_name = 'xxxxx' and B. owner = ''; the query found that there is indeed a table that references the table to be inserted. At this moment, the hope is very high. (5) through a simple test, I verified my speculation that the following create table t3 (id number primary key, name varchar2 (20), product_id number ); create table t2 (id number primary key, name varchar2 (20); alter table t3 add constraint FK_PRODUCTSTAT_PRODUCTID foreign key (PRODUCT_id) references t2 (ID ); SQL> insert into t2 values (1, 'dh '); 1 row insertedSQL> insert into t2 values (2, 'cc '); 1 row insertedSQL> insert into t2 values (3, 'cc'); 1 row insertedSQL> commit; Commit complete
Session 1 performs the following operations:SQL> select * from t2; ID NAME ---------- ------------------ 1 dh 2 cc 3 ccSQL> select * from t3; ID NAME PRODUCT_ID ---------- ------------------ ---------- -- you can see, at this time, the t3 table has any record SQL> insert into t2 values (4, 'cc'); -- execute one insert row inserted,
Run the following insert operation in Table session2 t2:Insert into t3 values (1, 'TT', 4); surprisingly, a lock wait occurs, exactly the same as the lock wait type we encounter. (6) check whether the lock owner has performed DML operations on the parent table of the lock wait table, as shown in select B. SQL _text,. * from v $ open_cursor a, v $ SQL B where. SQL _id = B. SQL _id and. sid = 4266 and upper (B. SQL _text) like '% xxxxx_ref %'; check results are consistent with our expectation. there are indeed many insert operations on the master table! (7) Basically, we have determined what statements cause lock congestion and submitted the statements to developers. After the code is modified, the problem is solved!
Problem SummaryIn fact, this issue is not difficult in itself. It is worth thinking about why such a simple problem cannot be found immediately. In the end, many times we are bound by experience. When encountering such problems, we need to let go of the existing experience and discover the root cause through the principles of databases. Therefore, the theoretical knowledge cannot be emphasized. It is really important to understand the principle so that you can draw inferences from each other, rather than fighting against any problems you have never seen!
