首页 新闻 搜索 专区 学院

关于 jdbc executeUpdate, 应用hang

0
[待解决问题]

各位大牛们好,我有个问题纠结我2个月了,请大家帮忙分析。

问题:
客户现场有2个table, 一个叫stage, 里面有1亿条记录,现在需要insert到另一个表Pattern, 原表没有primary 和foreign key, 也没Index,目的表有primary 和foreign key, 和Index,这2表都只有2列。目前用的merge into 语句,这个功能是定期做merge,把不在pattern的但是在stage的merge, 如果是delta数据,现在没有问题,如果做了clean(就是把pattern truncate)后应用会hang住。

从现场的stacktrace, 应用hang在executeUpdate:

static void doUpdateSql(Connection conn, String sqlStr) throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;

    try {
        stmt = conn.prepareStatement(sqlStr);
        stmt.executeUpdate(sqlStr);
    } finally {
        JDBCUtils.close( stmt, rs );
        logger.debug( "smtm closed");
    }
}

堆栈为:
"Timer-8" daemon prio=10 tid=0x00007f6798395800 nid=0x3972 runnable [0x00007f67ea8e6000]
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: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.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1814)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1779)
- locked <0x00007f83b3b03080> (a oracle.jdbc.driver.T4CConnection)
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:277)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:1044)
at com.cache.OracleSqlldr.doUpdateSql(OracleSqlldr.java:276)

用的c3p0连接池,配置为:
2020-06-26 11:01:00,038 INFO [impl.AbstractPoolBackedDataSource]
Initializing c3p0 pool...
com.mchange.v2.c3p0.ComboPooledDataSource [
acquireIncrement -> 4,
acquireRetryAttempts -> 30,
acquireRetryDelay -> 1000,
autoCommitOnClose -> true,
automaticTestTable -> null,
breakAfterAcquireFailure -> false,
checkoutTimeout -> 0,
connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester,
dataSourceName -> 2t2vcfaa1ujvobpl05hg7|30e3c624,
debugUnreturnedConnectionStackTraces -> false,
description -> null,
driverClass -> oracle.jdbc.OracleDriver,
factoryClassLocation -> null,
forceIgnoreUnresolvedTransactions -> false,
idleConnectionTestPeriod -> 1200,
initialPoolSize -> 3,
lastAcquisitionFailureDefaultUser -> null,
maxAdministrativeTaskTime -> 0,
maxConnectionAge -> 0,
maxIdleTime -> 0,
maxIdleTimeExcessConnections -> 0,
maxPoolSize -> 128,
maxStatements -> 0,
maxStatementsPerConnection -> 0,
minPoolSize -> 4,
numHelperThreads -> 3,
numThreadsAwaitingCheckoutDefaultUser -> 0,
propertyCycle -> 0,
testConnectionOnCheckin -> false,
testConnectionOnCheckout -> true,
unreturnedConnectionTimeout -> 0,
usesTraditionalReflectiveProxies -> false ]

本地一直没法重现,本地1亿数据,由于关闭了constraints和index, 大概1小时能merge 完,现场有constaint 和index, 需要2个半小时才看到pattern table有数据,但是应用这边一直都是等待。
查过oracle alert log,里面只有些下面:
Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 11-DEC-2019 12:11:41
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 110
nt OS err code: 0

大牛们看看这个我该怎么解决呢。到底是数据库有问题还是应用问题还是网络问题还是防火墙问问题?

还有个问题,也一直没想明白,本地测试时,用executeUpdate 发出merge into 命令后,由于这个merge into要执行1个小时,这是kill 程序,oracle那边一直还是在执行,session也是active 状态,这个对不对,想模拟现场看看是不是防火墙把长连接段了,我在oracle 机器上(solaris)设置了 tcp keepalive 为10分钟,但是没能生效,不过我没重启机器,不知道要不要重启。
ndd -set /dev/tcp tcp_keepalive_interval 600000
这又是什么原因呢

Sunshine106的主页 Sunshine106 | 菜鸟二级 | 园豆:202
提问于:2020-08-31 16:43
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册