[Oracle] enq: TX

According to the development feedback, the application will trigger an alarm at recently. The application log shows that the database connection pool is full and new connections are rejected.

First, I made an ASH report (Report range: 7: 25 ~ ), From the ASH wait event, we found that enq: TX-row lock contention was as high as 76.54%, as shown below:

Top User Events

Event	Event Class	% Event	Avg Active Sessions
Enq: TX-row lock contention	Application	76.54	0.81
CPU + Wait for CPU	CPU	12.76	0.14
Db file sequential read	User I/O	7.40	0.08

Enq: The TX-row lock contention wait event is a row wait event, that is, multiple sessions are requested to modify the same row at the same time.

The next step is to find the SQL statements that cause the wait event:

Top SQL with Top Events

SQL ID	Planhash	Sampled # of Executions	% Activity	Event	% Event	Top Row Source	% RwSrc	SQL Text
4rm17788qwxuy	1272661853	54	69.45	Enq: TX-row lock contention	69.45	UPDATE	69.45	Update shift_case set expertId...
1cqbcdr0ufyk6	1272661853	10	5.20	Enq: TX-row lock contention	5.20	UPDATE	5.20	Update shift_case set daySecti...
1anu5c146v8d7	1272661853	4	1.89	Enq: TX-row lock contention	1.89	UPDATE	1.89	Update shift_case set daySecti...
Gbw4zk8jv0n0u	2588599834	10	1.57	CPU + Wait for CPU	0.79	Table access-BY GLOBAL INDEX ROWID	0.47	Select SC. scId, SC. estId, ct. c...
Dvmk92c1umc97	905317021	9	1.42	CPU + Wait for CPU	1.42	Connect by-no filtering with start-	0.63	Select h. hospitaluuid id, h. pl...

From the above table, we can see that the SQL statement of SQL _ID = 4rm17788qwxuy is the culprit. The SQL statement to be modified is as follows:

4rm17788qwxuy

Update shift_case set expertId =: 1, shiftDate =: 2, daySection =: 3, rcLimit =: 4, orderingCount =: 5, shareRccount =: 6, clinicTypeUuid =: 7, dimensions =: 8, isTimeDivision =: 9, state =: 10, isopen =: 11, stateTime =: 12, updateTime = sysdate where scId =: 13

Scid is the primary key of shift_case. That is to say, there are many sessions requesting to update the same row at the same time.

Now that the problem has been located, it is easy to handle it. I will immediately ask the application developer. The truth is: The application needs to obtain data from the external system, to make the internal database and the external database as consistent as possible, the update statement is executed in the database every time the external system is queried.

The solution is also simple: because each Update overwrites the previous update (equivalent to the previous update is useless), there is no need to update each query, you only need to update the last query.