Serious database deadlocks cause fault diagnosis when the middleware connection pool is full and the middleware pool is full

Serious database deadlocks cause fault diagnosis when the middleware connection pool is full and the middleware pool is full
1. Fault

The foreground system application cannot log on. The running status of the weblogic Server application is overload, and the thread connection pool is full.

2. Fault Cause Analysis

Based on the above fault phenomenon, the analysis can be determined that Weblogic has too many connections to the database, because the session remains unreleased, after the connection pool is full, as a result, new connections cannot be requested to the connection pool. The key here is to analyze why a large number of sessions occupy the connection pool and are not released.

3. In the problem analysis process, 3.1 sessions exceed 1000.

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	38194		1010	13.8
End Snap:	38196		1004	14.4
Elapsed:		119.65 (mins)
DB Time:		20,686.23 (mins)

More than 3.2 executions per second

	Per Second	Per Transaction
Redo size:	18,144,642.95	459,254.26
Logical reads:	199,155.23	5,040.77
Block changes:	100,209.94	2,536.39
Physical reads:	5,241.51	132.67
Physical writes:	2,182.48	55.24
User cballs:	16,134.45	408.37
Parses:	177.18	4.48
Hard parses:	69.02	1.75
Sorts:	106.97	2.71
Logons:	0.58	0.01
Executes:	15,955.55	403.85
Transactions:	39.51

 

3.3 94% of the wait time is spent in "enq: TX-row lock contention"

Event	Waits	Time (s)	Avg Wait (MS)	% Total Call Time	Wait Class
Enq: TX-row lock contention	2,419,702	1,174,344	485	94.6	Application
CPU time		28,795		2.3
Db file sequential read	2,507,054	13,011	5	1	User I/O
Db file scattered read	2,358,449	11,234	5	0.9	User I/O
Db file parallel write	475,780	7,760	16	0.6	System I/O

 

3.4 SQL statements that consume the longest time

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Text
744,943	51	226	3296.21	60.02	2d0msz9tv9z5c	Delete from PUB_XXLINE where u...
429,987	29	483	890.24	34.64	1yucn1_p2mjdk	Delete from PUB_XXLINE where s...

2d0msz9tv9z5cThe text is as follows:

Delete from PUB_XXLINE WHEREUSER_ID=: 1

1yucn1_p2mjdkThe text is as follows:

Delete from PUB_XXLINE WHERESESSION_ID=: 1

The preceding two SQL statements generate severe row contention and generate session-level deadlocks.

3.5 Database Authentication Session deadlock

(1) view the blocked sid: 800 which session is blocked

Select sid, serial #, SQL _id, status, blocking_session from v $ sessionwhere sid = 800;

Sid	Serial #	SQL _id	Status	Blocking_session
800	28777	2d0msz9tv9z5c	ACTIVE	347

As shown above, the ID of the SQL statement executed by sid: 800 is 2d0msz9tv9z5c, Which is blocked by session id 347.

(2) Check whether session id 347 is blocked

Select sid, serial #, SQL _id, status, blocking_session from v $ sessionwhere sid = 347;

Sid	Serial #	SQL _id	Status	Blocking_session
347	15367	2d0msz9tv9z5c	ACTIVE	800

The ID of the statement executed by Session 347 is 2d0msz9tv9z5c, Which is blocked by sid 800. A deadlock has been formed here.

3.6 large amount of deadlock information found in database logs

Wed Dec 10 09:55:05 2014 Global Enqueue Services Deadlock detected. More info in file /Oracle/admin/XXTH/bdump/xxth1_lmd0_151_70.trc. Wed Dec 10 09:55:36 2014 Thread 1 advanced to log sequence 493665 (LGWR switch) Current log #16 seq #493665 mem #0: + REDOGROUP/XXth/onlinelog/group_16.263.837169415 Wed Dec 10 09:56:04 2014 Thread 1 advanced to log sequence 493666 (LGWR switch) Current log #14 seq #493666 mem #0: + REDOGROUP/XXth/onlinelog/group_14.261.837169357 Wed Dec 10 09:56:05 2014 Global Enqueue Services Deadlock detected. More info in file /Oracle/admin/XXTH/bdump/xxth1_lmd0_151_70.trc.

 

4. troubleshooting Summary

Statement DELETEFROM PUB_XXLINE WHEREUSER_ID=: 1 The data rows in each operation have multiple rows. multiple sessions execute this statement at the same time to form a deadlock in the database, and the statement DELETEFROM PUB_XXLINE WHERESESSION_ID=: 1 so that the connection pool of Weblogic (middleware) is exhausted. The connection cannot be added.

 

 

Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work

Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244