设置autocommit了，还是会报错

[ghx577]

java.lang.reflect.UndeclaredThrowableException at com.sun.proxy.$Proxy50.setAutoCommit(Unknown Source) at org.sagacity.sqltoy.utils.SqlUtil.executeSql(SqlUtil.java:1406) at org.sagacity.sqltoy.dialect.DialectFactory$2.doConnection(DialectFactory.java:304) at org.sagacity.sqltoy.utils.DataSourceUtils.processDataSource(DataSourceUtils.java:493) at org.sagacity.sqltoy.dialect.DialectFactory.executeSql(DialectFactory.java:292) at org.sagacity.sqltoy.support.SqlToyDaoSupport.executeSql(SqlToyDaoSupport.java:524) at org.sagacity.sqltoy.support.SqlToyDaoSupport.deleteByQuery(SqlToyDaoSupport.java:1068) at org.sagacity.sqltoy.dao.impl.SqlToyLazyDaoImpl.deleteByQuery(SqlToyLazyDaoImpl.java:945) at org.sagacity.sqltoy.dao.impl.SqlToyLazyDaoImpl$$FastClassBySpringCGLIB$$e709e869.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692) at org.sagacity.sqltoy.dao.impl.SqlToyLazyDaoImpl$$EnhancerBySpringCGLIB$$5b0cf665.deleteByQuery() at com.ild.de.config.service.impl.EdeControlServiceImpl.delete(EdeControlServiceImpl.java:44) at com.ild.de.config.handler.ViewEdeControlUnInterceptHanlerImpl.exec(ViewEdeControlUnInterceptHanlerImpl.java:57) at com.ild.de.config.handler.ViewEdeControlUnInterceptHanlerImpl.exec(ViewEdeControlUnInterceptHanlerImpl.java:27) at xyz.erupt.core.controller.EruptDataController.execEruptOperator(EruptDataController.java:156) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) at javax.servlet.http.HttpServlet.service(HttpServlet.java:652) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at xyz.erupt.security.interceptor.HttpServletRequestFilter.doFilter(HttpServletRequestFilter.java:43) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.ssssssss.magicapi.config.MagicCorsFilter.doFilter(MagicCorsFilter.java:42) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) Caused by: java.lang.reflect.InvocationTargetException at sun.reflect.GeneratedMethodAccessor189.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at cn.beecp.boot.datasource.sqltrace.ConnectionHandler.invoke(ConnectionHandler.java:44) ... 78 more Caused by: java.sql.SQLException: Execute 'commit' or 'rollback' before this operation at cn.beecp.pool.PoolStaticCenter.(PoolStaticCenter.java:61) at cn.beecp.boot.datasource.SpringBootDataSourceUtil.getConfigValue(SpringBootDataSourceUtil.java:111) at cn.beecp.boot.datasource.MultiDataSourceRegister.getIdList(MultiDataSourceRegister.java:101) at cn.beecp.boot.datasource.MultiDataSourceRegister.registerBeanDefinitions(MultiDataSourceRegister.java:78) at org.springframework.context.annotation.ImportBeanDefinitionRegistrar.registerBeanDefinitions(ImportBeanDefinitionRegistrar.java:86) at org.springframework.context.annotation.ConfigurationClassBeanDefinitionReader.lambda$loadBeanDefinitionsFromRegistrars$1(ConfigurationClassBeanDefinitionReader.java:396) at java.util.LinkedHashMap.forEach(LinkedHashMap.java:684) at org.springframework.context.annotation.ConfigurationClassBeanDefinitionReader.loadBeanDefinitionsFromRegistrars(ConfigurationClassBeanDefinitionReader.java:395) at org.springframework.context.annotation.ConfigurationClassBeanDefinitionReader.loadBeanDefinitionsForConfigurationClass(ConfigurationClassBeanDefinitionReader.java:157) at org.springframework.context.annotation.ConfigurationClassBeanDefinitionReader.loadBeanDefinitions(ConfigurationClassBeanDefinitionReader.java:129) at org.springframework.context.annotation.ConfigurationClassPostProcessor.processConfigBeanDefinitions(ConfigurationClassPostProcessor.java:342) at org.springframework.context.annotation.ConfigurationClassPostProcessor.postProcessBeanDefinitionRegistry(ConfigurationClassPostProcessor.java:246) at org.springframework.context.support.PostProcessorRegistrationDelegate.invokeBeanDefinitionRegistryPostProcessors(PostProcessorRegistrationDelegate.java:311) at org.springframework.context.support.PostProcessorRegistrationDelegate.invokeBeanFactoryPostProcessors(PostProcessorRegistrationDelegate.java:112) at org.springframework.context.support.AbstractApplicationContext.invokeBeanFactoryPostProcessors(AbstractApplicationContext.java:745) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:563) at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144) at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:767) at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759) at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426) at org.springframework.boot.SpringApplication.run(SpringApplication.java:326) at org.springframework.boot.SpringApplication.run(SpringApplication.java:1311) at org.springframework.boot.SpringApplication.run(SpringApplication.java:1300) at com.ild.de.config.ExampleApplication.main(ExampleApplication.java:30)

[Chris2018998]

BeeCP处理事务遵循一个原则：事务一旦开启后，避免将脏数据带入数据库，要么回滚(Rollback)，要么提交(Commit)，中途不允切换。 从上面的错误信息来看，可以用如下代码模拟一下。

Connection con =ds.getConenction();

con.setAutoCommit(false);  //<----开始事务

Statement st= con.createStatement();

st.execute("xxxxxxx");

con.setAutoCommit(false/true);  //<----异常跑出点，这个违背了BeeCP事务原则。

可以将上述代码结构的调整如下

Connection con =ds.getConenction();

con.setAutoCommit(false);  //<----开始事务

Statement st= con.createStatement();

st.execute("xxxxxxx");

con.commit();   //<------提交一次，终结事务

con.setAutoCommit(false/true)；

。。。。。。。。。。。。。。

[Chris2018998]

脏数据写入数据库测试（写入就是有问题）

public class TransactionDirtyTest {
    public static void main(String[] ags) throws Exception {
        testHikari();
        //testBeeCP();
    }

    private static void testHikari() throws Exception {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost/test");
        config.setDriverClassName("com.mysql.jdbc.Driver");
        config.setUsername("root");
        config.setPassword("");
        config.setMinimumIdle(1);
        config.setMaximumPoolSize(1);
        config.setConnectionTimeout(8000);
        config.setConnectionTestQuery("select 1 from dual");
        config.setAutoCommit(false);
        HikariDataSource dataSource = new HikariDataSource(config);

        Connection conn = dataSource.getConnection();
        testDirtyData(conn, "Hikari");
        conn.setAutoCommit(true);
        conn.close();
    }

    private static void testBeeCP() throws Exception {
        BeeDataSourceConfig config = new BeeDataSourceConfig();
        config.setDriverClassName("com.mysql.jdbc.Driver");
        config.setJdbcUrl("jdbc:mysql://localhost/test");
        config.setUsername("root");
        config.setPassword("");
        config.setMaxActive(1);
        config.setInitialSize(1);
        config.setMaxWait(8000);
        config.setDefaultAutoCommit(false);
        BeeDataSource dataSource = new BeeDataSource(config);

        Connection conn = dataSource.getConnection();
        testDirtyData(conn, "BeeCP");
        conn.setAutoCommit(true);
        conn.close();
    }

    public static void testDirtyData(Connection conn, String dsName) throws SQLException {
        Statement ptmt = null;
        try {
            ptmt = conn.createStatement();
            ptmt.execute("insert into SAVE_POINT(DS_NAME) values('" + dsName + "_0')");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (ptmt != null)
                ptmt.close();
        }
    }
}

测试截图

[Chris2018998]

https://github.com/Chris2018998/BeeCP/blob/master/doc/temp/beecp-3.2.7_custom1.jar

[Chris2018998]

下一版本，打算取消事务脏标记检查，那么就不会再报这个异常了。

[Chris2018998]

试试最新版本看看