Database deadlock seriously triggers middleware connection pool full fault diagnosis

1, fault phenomenon

The foreground system application cannot log in, the running state of the WebLogic server application is displayed as overload and the thread connection pool is full.

2, the cause of failure analysis

Based on the above symptom, the analysis base can be determined that the WebLogic has too many connections to the database because the session remains free and the connection pool fills up, causing the new connection to fail to request to the connection pool. The key here is to analyze why there are a large number of sessions that are full of connection pooling without releasing.

3, the problem analysis process 3.1 session number more than 1000

	Snap Id	Snap time	Sessions	Cursors/session
Begin Snap:	38194	2014/12/10 8:00	1010	13.8
End Snap:	38196	2014/12/10 10:00	1004	14.4
Elapsed:		119.65 (mins)
DB Time:		20,686.23 (mins)

3.2 Execution times per second more than 15,000 times

	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 Calls:	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 waiting time is spent in "Enq:tx-row lock contention"

1,174,344

TD valign= "Top" >

event	waits	time (s)	avg Wait (ms)	% Total Call time	wait Class
enq:tx-row lock contention	2,419,702	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 The longest-consuming SQL statement

elapsed time (s)	cpu time (S	executions	elap Per Exec (s)	% Total DB time	sql Id	Span style= "Color:white" >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	1yucnh7p2mjdk	DELETE from Pub_xxline WHERE S ...

The 2d0msz9tv9z5c text is as follows:

DELETE from Pub_xxline WHERE user_id=:1

The 1yucnh7p2mjdk text is as follows:

DELETE from Pub_xxline WHERE session_id=:1

The above two SQL statements generate severe row contention and generate deadlocks at the session level

3.5 in-Database authentication session deadlock

(1) View which session the blocked sid:800 session is blocked by

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

From above, the ID of the SQL statement executed by sid:800 is 2d0msz9tv9z5c, which is blocked by session ID 347

(2) See if 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 also 2d0msz9tv9z5c, which is blocked by Sid 800, where deadlocks have been formed.

3.6 Large number of deadlock information found in the database log

Wed Dec 10 09:55:05 2014 Global Enqueue Services Deadlock detected. More info in file /oracle/admin/xxth/bdump/xxth1_lmd0_1541770.trc. Wed Dec 10 09:55:36 2014 Thread 1 advanced to log sequence 493665 (LGWR switch) Current log# 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# 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_1541770.trc.

4, the problem causes diagnosis summary

Statement Deletefrom Pub_xxline where user_id=:1 Each operation of the data row has more than one row, multiple sessions executing the statement at the same time, forming a deadlock within the database, and a single time to delete a row of data statements Deletefrom Pub_ Xxline WHERE session_id=:1 so that the connection pool of the WebLogic (middleware) is exhausted. The connection cannot be added.

This article Li Junjie (Network Name: casing), engaged in "system architecture, operating systems, storage devices, databases, middleware, applications" six levels of systematic performance optimization work

Welcome to the System performance Optimization Professional group, to discuss performance optimization technology together. Group number: 258187244

Database deadlock seriously triggers middleware connection pool full fault diagnosis