[Oracle] Enq:tx-row lock contention optimization case

According to development feedback, the recent daily 7:30 app will alert, the app's log shows that the database connection pool is full, and the new connection is rejected.

First, I did the ash report (Reporting interval: 7:25 ~ 7:35), and the wait event from Ash found Enq:tx-row lock contention incredibly up to 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

The Enq:tx-row lock contention wait event is a row waiting event, meaning that there are multiple session requests at the same time to modify the same row.

The next step is to find out which SQL is the main cause of 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-with	0.63	Select H.hospitaluuid ID, h.pl ...

From the above table, we can conclude that the SQL statement of Sql_id=4rm17788qwxuy is the culprit, and the SQL statements are as follows:

4rm17788qwxuy

Update shift_case Set Expertid =: 1, shiftdate =: 2, Daysection =: 3, Rclimit =: 4, Orderingcount =: 5, Sharerccount =: 6, Clinictypeuuid =: 7, fee =: 8, istimedivision =: 9, state =: ten, isopen=:11, Statetime =:, UpdateTime = sy Sdate where ScId =:13

SCID is the primary key for Shift_case, which means that there are very many sessions at the same time that request the same row.

Well, since the problem has been positioned to do, immediately put the application developers to ask, the truth: the original application needs to get data from the external system, in order to keep the internal database and external as far as possible consistent, each time the External system query, the database will execute the UPDATE statement.

The

Workaround is also simple: Because each update will overwrite the previous update (equal to the previous update does not work hard), so there is no need to update every time, as long as the last query to do update.