weblogic 多线程用相同的数据库连接导致的错误

待我称王封你为后i 2022-08-09 12:42 155阅读 0赞

现场做性能测试，第三方厂家报问题，说有锁表的信息，如下图日志。

"\[ACTIVE\] ExecuteThread: '8' for queue: 'weblogic.kernel.Default (self-tuning)'" - Thread t@3460  
   java.lang.Thread.State: RUNNABLE  
at java.net.SocketInputStream.socketRead0(Native Method)  
at java.net.SocketInputStream.read(SocketInputStream.java:129)  
at oracle.net.ns.Packet.receive(Packet.java:293)  
at oracle.net.ns.DataPacket.receive(DataPacket.java:104)  
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:315)  
at oracle.net.ns.NetInputStream.read(NetInputStream.java:260)  
at oracle.net.ns.NetInputStream.read(NetInputStream.java:185)  
at oracle.net.ns.NetInputStream.read(NetInputStream.java:102)  
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:124)  
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:80)  
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1136)  
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1113)  
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:288)  
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)  
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)  
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)  
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1009)  
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1185)  
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)  
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)  
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)  
**- locked <6ddfb840> (a oracle.jdbc.driver.T4CConnection)**  
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)  
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:135)  
at com.coop.lam.common.dao.BaseDAO.executeQueryByDSName(BaseDAO.java:1449)

看到日志中的堆栈信息找到业务的方法，发现是一个查询，在Oracle中，查询是不会被堵塞的，那肯定是其他原因。找了一下资料，说是很多线程用到了相同的数据库链接。

下面来做一个实验重现，每个线程都会用一个链接，如果A线程在使用，B线程就会被堵塞，直到它获得链接为止，所以解决这个问题，要不加大中间件连接池的数量，要不加快业务逻辑处理速度：

import java.sql.Connection;
    import java.sql.ResultSet;
    import java.util.Properties;
    
    import oracle.jdbc.pool.OracleDataSource;
    
    class slowQuery implements Runnable {
        Thread t;
        
        static Connection conn;
        
        public static void main(String args[]) {
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                OracleDataSource ods = new OracleDataSource();
                ods.setURL("jdbc:oracle:thin:TEST/TEST@10.10.15.2:1521/test");
                Properties prop = new Properties();
                prop.setProperty("MinLimit", "1");
                ods.setConnectionCacheProperties(prop);
                conn = ods.getConnection();
                ods.setConnectionCachingEnabled(true);
                ods.setFastConnectionFailoverEnabled(true);
                for (int i = 1; i < 10; i++) {
                    slowQuery r = new slowQuery();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        slowQuery() {
            this.t = new Thread(this);
            t.start();
        }
        
        @Override
        public void run() {
            System.out.println(new java.util.Date());
            try {
                ResultSet rst = conn.createStatement().executeQuery("select slow_query(60) from dual");
                while (rst.next()) {
                    System.out.println("test " + rst.getInt(1));
                }
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

create or replace function slow_query(p_secs in number) return number is
    begin
      dbms_lock.sleep(p_secs);
      return p_secs;
    end;

**D:\\jdk1.7.13\_32\\bin>jstack  9304   --需要多运行几次，因为不是没次都能看到问题**  
2015-10-23 14:37:30  
Full thread dump Java HotSpot(TM) Client VM (23.7-b01 mixed mode, sharing):  
"DestroyJavaVM" prio=6 tid=0x0008b800 nid=0x2218 waiting on condition \[0x00000000\]  
   java.lang.Thread.State: RUNNABLE  
"Thread-9" prio=6 tid=0x0510f000 nid=0x2088 runnable \[0x05e7e000\]  
   java.lang.Thread.State: RUNNABLE  
        at java.net.SocketInputStream.socketRead0(Native Method)  
        at java.net.SocketInputStream.read(Unknown Source)  
        at java.net.SocketInputStream.read(Unknown Source)  
        at oracle.net.ns.Packet.receive(Packet.java:282)  
        at oracle.net.ns.DataPacket.receive(DataPacket.java:103)  
        at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:230)  
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:175)  
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:100)  
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:85)  
        at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122)  
        at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78)  
        at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179)  
        at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)  
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)  
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)  
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)  
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)  
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:853)  
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)  
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)  
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1469)  
        - locked <0x24c20448> (a oracle.jdbc.driver.T4CConnection)  
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:389)  
        at com.comtop.test.slowQuery.run(SlowQuery.java:47)  
        at java.lang.Thread.run(Unknown Source)  
  
"Thread-8" prio=6 tid=0x0510e800 nid=0x268c waiting for monitor entry \[0x05d2f000\]  
   java.lang.Thread.State: BLOCKED (on object monitor)  
        at oracle.jdbc.driver.PhysicalConnection.createStatement(PhysicalConnection.java:3169)  
        - waiting to lock <0x24c20448> (a oracle.jdbc.driver.T4CConnection)  
        at com.comtop.test.slowQuery.run(SlowQuery.java:47)  
        at java.lang.Thread.run(Unknown Source)