使用proxool连接sybase时处理事务的问题
问题描述
今天花了一天时间解决一个问题，好久没有这样钻研解决这样的问题了。
http://topic.csdn.net/t/20050608/16/4068615.html
问题跟这个帖子相同，就是使用proxool+sybase处理事务时，会报错。
先看看以下代码
public static void test2() throws Exception {// Connection conn = getConnectionFromPool();Connection conn = getConnection();conn.setAutoCommit(false);Statement stmt = conn.createStatement();String sql = "select 1";stmt.executeQuery(sql);conn.commit();try {conn.setAutoCommit(true);} catch (Exception e) {e.printStackTrace();}stmt.close();conn.close();}public static Connection getConnection() throws Exception {Class.forName("com.sybase.jdbc3.jdbc.SybDriver");String username = "sa";String pwd = "";String url = "jdbc:sybase:Tds:192.168.0.100:5000/test";return DriverManager.getConnection(url, username, pwd);}static public Connection getConnectionFromPool() throws Exception {Connection connection = null;String alias = "test";Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");if (connection == null) {Properties info = new Properties();info.setProperty("proxool.maximum-connection-count", "20");info.setProperty("user", "sa");info.setProperty("password", "");info.put("proxool.maximum-active-time", "60000");String driverClass = "com.sybase.jdbc3.jdbc.SybDriver";String driverUrl = "jdbc:sybase:Tds:192.168.0.100:5000/test";String url = "proxool." + alias + ":" + driverClass + ":"+ driverUrl;connection = DriverManager.getConnection(url, info);System.out.println(connection);}return connection;}在运行test2()函数时没有出现异常正常，这里是没有使用proxool，用原始的数据库连接方式。
将test2()第一行的注释去掉，将第二行注释掉，运行时就会报错
Exception in thread "main" com.sybase.jdbc3.jdbc.SybSQLException: SET CHAINED command not allowed within multi-statement transaction.at com.sybase.jdbc3.tds.Tds.processEed(Tds.java:2942)at com.sybase.jdbc3.tds.Tds.nextResult(Tds.java:2246)at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(ResultGetter.java:69)at com.sybase.jdbc3.jdbc.SybStatement.nextResult(SybStatement.java:220)at com.sybase.jdbc3.jdbc.SybStatement.nextResult(SybStatement.java:203)at com.sybase.jdbc3.jdbc.SybStatement.updateLoop(SybStatement.java:1804)at com.sybase.jdbc3.jdbc.SybStatement.executeUpdate(SybStatement.java:1787)at com.sybase.jdbc3.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:116)at com.sybase.jdbc3.tds.Tds.setOption(Tds.java:1278)at com.sybase.jdbc3.jdbc.SybConnection.setAutoCommit(SybConnection.java:1011)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)at java.lang.reflect.Method.invoke(Method.java:585)at org.logicalcobwebs.proxool.WrappedConnection.invoke(WrappedConnection.java:208)at org.logicalcobwebs.proxool.WrappedConnection.intercept(WrappedConnection.java:98)at com.sybase.jdbcx.SybConnection$$EnhancerByProxool$$8df5facc.setAutoCommit(<generated>)at TestConnectionTran.test(TestConnectionTran.java:34)at TestConnectionTran.main(TestConnectionTran.java:13)
问题原因
s_phoenix大侠的解决办法是：
SET CHAINED command not allowed within multi-statement transaction.到google，baidu里面随便搜下，就有一堆。简单的一句话：处于事务环境下，调用 set chained 是会发生异常的，这一点手册上也非常明确的指出了。使用sybase的jconn驱动，调用setAutoCommit(false)事务开始，也就是 set chained on，然后执行完相应的操作后commit或者rollback结束事务，这时需要setAutoCommit(true)“完壁归赵”，以便连接池回收。但是在proxool中后面setAutoCommit(true)会报错，以上的错误。这个错误明显是事务没有结束又调用setAutoCommit方法，但是明明事务已经结束了，原因何在？
??? 查看源代码发现了问题，先看源代码org.logicalcobwebs.proxool.WrappedConnection：
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { Object result = null; int argCount = args != null ? args.length : 0; Method concreteMethod = method; if (proxyConnection != null && proxyConnection.getConnection() != null) { concreteMethod = InvokerFacade.getConcreteMethod(proxyConnection.getConnection().getClass(), method); } try { if (proxyConnection != null && proxyConnection.isReallyClosed()) { // The user is trying to do something to this connection and it's been closed.．．．．．．．．．．．．．．．． if (concreteMethod.getName().equals(IS_CLOSED_METHOD)) { return result; }?这块代码是动态代理执行Connection的所有方式时被调用的方法,可以看出,每次都会调用proxyConnection.isReallyClosed()方法,这个方法里面调用的是Connection.isClose()方法.使用连接池调用test2()函数,可以类比于以下方式:
public static void test1() throws Exception {Connection conn = getConnection();conn.isClosed();conn.setAutoCommit(false);conn.isClosed();conn.commit();conn.isClosed();conn.setAutoCommit(true);}?运行test1报错根test2一样.问题就出现在这里.可以猜到conn.isClosed();又开启了一个新的事务,该事务没有结束就conn.setAutoCommit(true);当然会报错.事实上,我通过抓报工具,发现conn.isClosed();会发送命令sp_mda到数据库中,这个过程就可以看作是在数据库中执行以下命令:
set chained ongoselect 1gocommit gosp_mda 0,3,2set chained offgo
问题解决
问题原因找到了,怎么解决呢? s_phoenix大侠给了我提示,提前拦截setAutoCommit方法.
public Object invoke(Object proxy, Method method, Object[] args)throws Throwable {Object result = null;int argCount = args != null ? args.length : 0;Method concreteMethod = method;if (proxyConnection != null && proxyConnection.getConnection() != null) {concreteMethod = InvokerFacade.getConcreteMethod(proxyConnection.getConnection().getClass(), method);}try {if (concreteMethod.getName().equals("setAutoCommit")) {result = concreteMethod.invoke(proxyConnection.getConnection(),args);return result;}} catch (Exception e) {LOG.error("setAutoCommit error", e);throw e;}try {if (proxyConnection != null && proxyConnection.isReallyClosed()) {// The user is trying to do something to this connection and// it's been closed.if (concreteMethod.getName().equals(IS_CLOSED_METHOD)) {// That's cool. No problem checking as many times as you// like.简单的测试了下,没有问题.会不会对其他地方产生影响呢?我不知道,可能会发生很奇妙的结果,哈哈...还是得严格测试下.
?
修改后,还要注意一点,在commit后面要紧跟setAutoCommit(true),如果中间又调用connection其他方法,还是会报错的.
?
?
?
?