检查数据库锁和程序没有释放数据库连接

最新推荐文章于 2024-01-24 15:38:15 发布

qiao000_000

最新推荐文章于 2024-01-24 15:38:15 发布

阅读量1w

点赞数

分类专栏： Oracle 文章标签：数据库数据库连接池 import stylesheet null servlet

Oracle 专栏收录该内容

26 篇文章

订阅专栏

本文介绍如何使用Oracle数据库动态性能视图排查死锁，并利用Proxool连接池监控和管理Java应用中的数据库连接。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT /*+ rule*/
B.INST_ID,
A.EVENT,
C.SID,
B.SPID,
C.MACHINE,
C.SECONDS_IN_WAIT,
'kill -9 ' || B.SPID,
'alter system kill session '''||C.SID||','||C.SERIAL#||''';',
E.OBJECT_NAME,
dbms_rowid.ROWID_CREATE(1,C.ROW_WAIT_OBJ#,C.ROW_WAIT_FILE#,C.ROW_WAIT_BLOCK#,C.ROW_WAIT_ROW#) RWID,
D.SQL_TEXT
FROM GV$SESSION_WAIT A,
       GV$PROCESS      B,
       GV$SESSION      C,
       GV$SQLAREA      D,
       DBA_OBJECTS     E
WHERE A.EVENT = 'enq: TX - row lock contention'
   AND A.SID = C.SID
   AND C.PADDR = B.ADDR
   AND A.INST_ID = C.INST_ID
   AND A.INST_ID = B.INST_ID
   AND A.INST_ID = D.INST_ID
   AND C.SQL_ID = D.SQL_ID(+)
   AND C.ROW_WAIT_OBJ# = E.OBJECT_ID
-- AND A.INST_ID = 2
ORDER BY INST_ID, SECONDS_IN_WAIT DESC;

select s.SID,s.MACHINE,s.USERNAME,s.STATUS,s.LOGON_TIME,s2.SQL_TEXT from v$session s,v$sqlarea s2
where s.SQL_ID = s2.SQL_ID
order by s.LOGON_TIME desc

----------------以下转帖备份--------------
1.利用oracle数据库的动态性能视图v$open_cursor，即通过游标来查找未释放的数据库连接

select * from v$open_cursor where user_name='SCOTT';其中SCOTT为登录的用户名

如果java数据库的连接没有关闭通过上面的sql语句就可以看到被查询的sql语句 select * from dept

在被打开的游标里面出现多次。如下所示：

123 3997DA58 315 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
120 39976A20 309 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept
124 3996F9E8 303 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept

然后根据sql语句去查找，写java代码是那个sql语句没有关闭。

2.通过proxool数据库连接池来查找

首先建立一个web project工程testProxool，创建一个cfg包创建LoadDbXml.java类内容如下：

package cfg;

import java.util.*;
import java.io.*;

import org.jdom.*;
import org.jdom.input.SAXBuilder;
import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.ProxoolFacade;

public class LoadDbXml {

private Element rootElement = null;
public static HashMap sqlEle = new HashMap();
//初始化构造方法
public LoadDbXml(){
   String proxool = "D://MyEclipse//workspace//testCharacter//WebRoot//WEB-INF//proxool.xml";
   getRootElement(proxool);
   initDBConn(proxool);
}
//获得根节点
public void getRootElement(String m_xmlFilePath)
{
   Document doc = null;
   try{
    File file = null;
    file = new File(m_xmlFilePath);
    SAXBuilder sb = new SAXBuilder();
    doc = sb.build(file);
    rootElement = doc.getRootElement();
   }catch(Exception ex){
    ex.printStackTrace();
   }
}
//初始化数据库参数
public void initDBConn(String m_xmlFilePath)
{
   Element ele = rootElement.getChild("database");
   try{
    Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
    Properties info = new Properties();
    info.setProperty("proxool.maximum-connection-count", ele.getChildText("DBPoolMaxConnection"));
    info.setProperty("proxool.minimum-connection-count", ele.getChildText("DBPoolMinConnection"));
    info.setProperty("proxool.maximum-active-time", ele.getChildText("DBPoolTimeoutValue"));
    info.setProperty("proxool.maximum-connection-lifetime", ele.getChildText("DBPoolTimeoutValue"));
    info.setProperty("proxool.prototype-count", ele.getChildText("DBPoolPrototypeCount"));
    info.setProperty("proxool.trace", "true");
    info.setProperty("user", ele.getChildText("DBUser"));
    info.setProperty("password", ele.getChildText("DBPassword"));

    String url = "proxool." + ele.getChildText("DBAlias") + ":" + ele.getChildText("DBDrivername") + ":" + ele.getChildText("DBDriverurl");
    ProxoolFacade.registerConnectionPool(url, info);
   }catch (ProxoolException e) {
    e.printStackTrace();
   }catch (ClassNotFoundException e) {
    e.printStackTrace();
   }
}
}

创建一个servlet类，用于启动web工程时初始化数据库InitDbXml

package cfg;

import javax.servlet.http.HttpServlet;
import javax.servlet.ServletConfig;

public class InitDbXml extends HttpServlet{

/**
* 初始化servlet，调用初始化数据库类
*/
private static final long serialVersionUID = 1L;

public void init(ServletConfig config){
   new LoadDbXml();
}
}
在WEB-INF下创建一个proxool.xml文件内容如下：

<?xml version="1.0" encoding="utf-8"?>
<dbconfig>
<database>
   <DBSMType>ORACLE</DBSMType>
   <DBAlias>eomsdb</DBAlias>
   <DBDrivername>oracle.jdbc.driver.OracleDriver</DBDrivername>
   <DBDriverurl>jdbc:oracle:thin:@192.168.20.92:1521:chshsid</DBDriverurl>
   <DBUser>scott</DBUser>
   <DBPassword>tiger</DBPassword>
   <DBPoolName>orpool</DBPoolName>
   <DBPoolMinConnection>1</DBPoolMinConnection>
   <DBPoolPrototypeCount>1</DBPoolPrototypeCount>
   <DBPoolMaxConnection>3</DBPoolMaxConnection>
   <DBPoolTimeoutValue>20000</DBPoolTimeoutValue>
</database>
</dbconfig>

在web.xml文件里面加入以下内容：

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">



   <servlet>
     <servlet-name>proxooladmin</servlet-name>
     <servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>
   </servlet>
   <servlet-mapping>
     <servlet-name>proxooladmin</servlet-name>
     <url-pattern>/dbadmin</url-pattern>
   </servlet-mapping>


<servlet>
   <servlet-name>initDbXml</servlet-name>
   <servlet-class>cfg.InitDbXml</servlet-class>
   <load-on-startup>1</load-on-startup>
</servlet>


</web-app>
在/WebRoot/admin下新建两个jsp文件closeConn.jsp内容如下：

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="cfg.LoadDbXml"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">
    <title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">

</head>
<body> 
<%
      PreparedStatement ps = null;
      Connection conn = null;
      ResultSet rs = null;
   try {
    conn = DriverManager.getConnection("proxool.eomsdb");
    ps = conn.prepareStatement(" select * from emp ");
    rs = ps.executeQuery();
    out.println("<table bgcolor='green' border='1' width='400' height='500'>");
    while(rs.next()){
     out.println("<tr><td>" + rs.getString(1)+"</td><td>" + rs.getString(2) + "</td></tr>");
    }
    out.println("</table>");
   } catch (SQLException e){
    e.printStackTrace();
   }finally{
    if(rs!=null)
     rs.close();
    if(ps!=null)
     ps.close();
    if(conn!=null)
     conn.close();
   }
   %>
</body>
</html>
创建没有关闭数据库连接的jsp文件noCloseConn.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="cfg.LoadDbXml"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">
    <title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">

</head>
<body> 
<%
      PreparedStatement ps = null;
      Connection conn = null;
      ResultSet rs = null;
   try {
    conn = DriverManager.getConnection("proxool.eomsdb");
    ps = conn.prepareStatement(" select * from dept ");
    rs = ps.executeQuery();
    out.println("<table bgcolor='green' border='1' width='400' height='500'>");
    while(rs.next()){
     out.println("<tr><td>" + rs.getString(1)+"</td><td>" + rs.getString(2) + "</td></tr>");
    }
    out.println("</table>");
   } catch (SQLException e) {
    e.printStackTrace();
   }
   //数据库没有进行连接
   %>
</body>
</html>

我在proxool.xml文件设置了最大连接数为3

如果http://localhost:8080/testProxool/admin/noCloseConn.jsp该页面连续被刷新四次，后台就会报出错误，
java.sql.SQLException: Couldn't get connection because we are at maximum connection count (3/3) and there are none available
at org.logicalcobwebs.proxool.Prototyper.quickRefuse(Prototyper.java:309)
at org.logicalcobwebs.proxool.ConnectionPool.getConnection(ConnectionPool.java:152)
at org.logicalcobwebs.proxool.ProxoolDriver.connect(ProxoolDriver.java:89)
at java.sql.DriverManager.getConnection(DriverManager.java:525)

我们访问http://localhost:8080/testProxool/dbadmin连接就会发现在

Proxool 0.9.1 (23-Aug-2008 11:10)
Definition
Snapshot
alias: eomsdb
Start date: 2010-06-15 18:55:01
Snapshot: 19:14:59
Connections: 3 (active), 0 (available), 3 (max)

Served: 38
Refused: 73
Details:
(click ID to drill down) # born last
start lap
(ms) thread
41 19:14:56 19:14:56 3093   http-8080-Processor25
40 19:14:56 19:14:56 3312   http-8080-Processor23
39 19:14:53 19:14:56 3859   http-8080-Processor23

Connection #41 sql = select * from dept ;proxy = bdb6aedelegate = 12478a9url = jdbc:oracle:thin:@192.168.20.92:1521:chshsid

访问select * from dept ; sql语句没有断开数据库连接，可以根据sql语句找到相应的java类。

问题便可以轻易的解决，可以不用自己手工写数据库连接池，来用proxool连接池，这个被公认的比较好的数据库连接池。