现场做性能测试,第三方厂家报问题,说有锁表的信息,如下图日志。
"[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;
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)