{"action":"created","issue":{"url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/906","repository_url":"https://api.github.com/repos/sharding-sphere/sharding-sphere","labels_url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/906/labels{/name}","comments_url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/906/comments","events_url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/906/events","html_url":"https://github.com/sharding-sphere/sharding-sphere/issues/906","id":329701622,"node_id":"MDU6SXNzdWUzMjk3MDE2MjI=","number":906,"title":"shardingjdbc for update 之后update事务死锁问题","user":{"login":"afredlyj","id":4151455,"node_id":"MDQ6VXNlcjQxNTE0NTU=","avatar_url":"https://avatars2.githubusercontent.com/u/4151455?v=4","gravatar_id":"","url":"https://api.github.com/users/afredlyj","html_url":"https://github.com/afredlyj","followers_url":"https://api.github.com/users/afredlyj/followers","following_url":"https://api.github.com/users/afredlyj/following{/other_user}","gists_url":"https://api.github.com/users/afredlyj/gists{/gist_id}","starred_url":"https://api.github.com/users/afredlyj/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/afredlyj/subscriptions","organizations_url":"https://api.github.com/users/afredlyj/orgs","repos_url":"https://api.github.com/users/afredlyj/repos","events_url":"https://api.github.com/users/afredlyj/events{/privacy}","received_events_url":"https://api.github.com/users/afredlyj/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-06-06T03:55:12Z","updated_at":"2018-06-06T12:15:38Z","closed_at":null,"author_association":"NONE","body":"Please answer these questions before submitting your issue. Thanks!\r\n开源不易，我们希望将精力放在完成新功能和解决有价值的问题上，为了让大家的配合更具有效率，请填写以下列出的全部问题\r\n\r\n### Which version of Sharding-Sphere do you using?（您使用的Sharding-Sphere版本为？）\r\nshardingjdbc 1.5.4.1 和2.0.0.M3\r\n下面的测试结果是基于 1.5.4.1。\r\n\r\n### Expected behavior （您预期的结果是）\r\n事务执行成功返回\r\n\r\n### Actual behavior （实际运行的结果是）\r\n同一个事务下，`select ... for update`成功返回之后，`update` 语句获取锁超时回滚\r\n\r\n### Steps to reproduce the behavior （可重现问题的操作步骤）\r\n\r\n业务代码:\r\n```java\r\n\r\npublic class App \r\n{\r\n    public static void main( String[] args )\r\n    {\r\n        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext(\"sharding.xml\");\r\n        TransactionalManager manager = context.getBean(TransactionalManager.class);\r\n        manager.test();\r\n\r\n        System.out.println(\"运行结束\");\r\n    }\r\n}\r\n\r\n  package afred.javademo;\r\n\r\nimport afred.javademo.dao.domain.StudentDO;\r\nimport afred.javademo.dao.domain.StudentDOExample;\r\nimport afred.javademo.dao.mapper.StudentDOMapper;\r\nimport org.springframework.beans.factory.annotation.Autowired;\r\nimport org.springframework.transaction.annotation.Transactional;\r\n\r\npublic class TransactionalManager {\r\n\r\n    @Autowired\r\n    private StudentDOMapper mapper;\r\n\r\n    @Transactional(rollbackFor = Exception.class)\r\n    public void test() {\r\n        StudentDOExample studentDOExample = new StudentDOExample();\r\n        studentDOExample.createCriteria().andIdEqualTo(1);\r\n        int i = mapper.countByExample(studentDOExample);\r\n        System.out.println(\"查询结果 : \" + i);\r\n\r\n        StudentDO student = new StudentDO();\r\n        student.setId(1);\r\n        student.setAge(18);\r\n        student.setName(\"afreddd\");\r\n//        mapper.insert(student);\r\n\r\n        StudentDOExample example = new StudentDOExample();\r\n        example.createCriteria().andIdEqualTo(1);\r\n        System.out.println(\"查询结果 : \" + mapper.selectForUpdateByExample(example));\r\n\r\n        student.setName(\"hello\");\r\n        mapper.updateByExample(student, example);\r\n    }\r\n}\r\n\r\n```\r\n\r\nmapper配置\r\n```java\r\n  <select id=\"selectForUpdateByExample\" resultMap=\"BaseResultMap\" parameterType=\"afred.javademo.dao.domain.StudentDOExample\" >\r\n    select\r\n    <if test=\"distinct\" >\r\n      distinct\r\n    </if>\r\n    'true' as QUERYID,\r\n    <include refid=\"Base_Column_List\" />\r\n    from student\r\n    <if test=\"_parameter != null\" >\r\n      <include refid=\"Example_Where_Clause\" />\r\n    </if>\r\n    <if test=\"orderByClause != null\" >\r\n      order by ${orderByClause}\r\n    </if>\r\n    for update\r\n  </select>\r\n```\r\n\r\nSpring事务配置\r\n```java\r\n<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n<beans xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns=\"http://www.springframework.org/schema/beans\" xmlns:context=\"http://www.springframework.org/schema/context\" xmlns:mybatis=\"http://mybatis.org/schema/mybatis-spring\"\r\n  xmlns:tx=\"http://www.springframework.org/schema/tx\" xmlns:aop=\"http://www.springframework.org/schema/aop\" xmlns:rdb=\"http://www.dangdang.com/schema/ddframe/rdb\"\r\n  xsi:schemaLocation=\"http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd\r\n\thttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd\r\n\thttp://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd\r\n\thttp://www.springframework.org/schema/tx  http://www.springframework.org/schema/tx/spring-tx.xsd\r\n    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd\r\n    http://www.dangdang.com/schema/ddframe/rdb http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd\">\r\n\r\n    <tx:annotation-driven proxy-target-class=\"true\"\r\n                          transaction-manager=\"mysqltransactionManager\" />\r\n\r\n    <bean id=\"mysqltransactionManager\"\r\n          class=\"org.springframework.jdbc.datasource.DataSourceTransactionManager\">\r\n        <property name=\"dataSource\" ref=\"shardingDataSource\" />\r\n    </bean>\r\n\r\n    <bean id=\"db\" class=\"com.alibaba.druid.pool.DruidDataSource\" init-method=\"init\" destroy-method=\"close\">\r\n        <property name=\"driverClassName\" value=\"com.mysql.jdbc.Driver\" />\r\n        <property name=\"url\" value=\"jdbc:mysql://xxxxxx:33066/test\" />\r\n        <property name=\"username\" value=\"xxxxxxx\" />\r\n        <property name=\"password\" value=\"xxxxx\" />\r\n        <property name=\"initialSize\" value=\"10\" />\r\n        <property name=\"minIdle\" value=\"10\" />\r\n        <property name=\"maxActive\" value=\"10\" />\r\n        <property name=\"maxWait\" value=\"10000\" />\r\n        <property name=\"validationQuery\" value=\"SELECT 1\" />\r\n        <property name=\"testOnBorrow\" value=\"true\" />\r\n    </bean>\r\n\r\n    <!-- 配置主从读写分离 -->\r\n    <rdb:master-slave-data-source id=\"db_credits_0\" master-data-source-ref=\"db\" slave-data-sources-ref=\"db\" />\r\n\r\n\t<!-- 虚拟数据源 -->\r\n\t<rdb:data-source id=\"shardingDataSource\">\r\n\t\t<rdb:sharding-rule data-sources=\"db_credits_0\" default-data-source=\"db_credits_0\">\r\n\t\t\t<!-- 定义逻辑表和路由策略 -->\r\n\t\t\t<rdb:table-rules>\r\n\t\t\t\t<rdb:table-rule logic-table=\"student\"/>\r\n            </rdb:table-rules>\r\n\t\t\t<!-- 分库路由 -->\r\n\t\t\t<rdb:default-database-strategy sharding-columns=\"none\" algorithm-class=\"com.dangdang.ddframe.rdb.sharding.api.strategy.database.NoneDatabaseShardingAlgorithm\" />\r\n\t\t</rdb:sharding-rule>\r\n\t</rdb:data-source>\r\n\r\n    <bean id=\"sqlSessionFactory\" class=\"org.mybatis.spring.SqlSessionFactoryBean\">\r\n        <property name=\"dataSource\" ref=\"shardingDataSource\" />\r\n        <property name=\"mapperLocations\">\r\n            <list>\r\n                <value>classpath:META-INFO/mybatis/mapper/*Mapper.xml</value>\r\n            </list>\r\n        </property>\r\n    </bean>\r\n\r\n    <bean class=\"org.mybatis.spring.mapper.MapperScannerConfigurer\">\r\n        <property name=\"basePackage\" value=\"afred.javademo.dao\" />\r\n        <property name=\"sqlSessionFactoryBeanName\" value=\"sqlSessionFactory\"></property>\r\n    </bean>\r\n\r\n  <bean id=\"manager\" class=\"afred.javademo.TransactionalManager\"/>\r\n\r\n\r\n</beans>\r\n```\r\n\r\n程序日志:\r\n\r\n\r\n```java\r\n### The error may involve afred.javademo.dao.mapper.StudentDOMapper.selectForUpdateByExample-Inline\r\n### The error occurred while setting parameters\r\n### SQL: select           'true' as QUERYID,           id, name, age         from student                    WHERE (  id = ? )                      for update\r\n### Cause: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction\r\n\tat org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)\r\n\tat org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:107)\r\n\tat org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:601)\r\n\tat org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)\r\n\t... 18 more\r\nCaused by: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction\r\n\tat com.dangdang.ddframe.rdb.sharding.executor.ExecutorExceptionHandler.handleException(ExecutorExceptionHandler.java:61)\r\n\tat com.dangdang.ddframe.rdb.sharding.executor.PreparedStatementExecutor.executeInternal(PreparedStatementExecutor.java:195)\r\n\tat com.dangdang.ddframe.rdb.sharding.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:169)\r\n\tat com.dangdang.ddframe.rdb.sharding.jdbc.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:115)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:601)\r\n\tat org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)\r\n\tat $Proxy11.execute(Unknown Source)\r\n\tat org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:56)\r\n\tat org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)\r\n\tat org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)\r\n\tat org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)\r\n\tat org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)\r\n\tat org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)\r\n\tat org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)\r\n\tat org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)\r\n\t... 24 more\r\nCaused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction\r\n\tat com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)\r\n\tat com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)\r\n\tat com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)\r\n\tat com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)\r\n\tat com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)\r\n\tat com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2535)\r\n\tat com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1911)\r\n\tat com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1203)\r\n\tat com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)\r\n\tat com.dangdang.ddframe.rdb.sharding.executor.PreparedStatementExecutor.executeInternal(PreparedStatementExecutor.java:192)\r\n\t... 40 more\r\n```\r\n\r\n### Please provide the reproduce example codes (such as github link)，otherwise we will label the issue as Invalid and close it.（为了节省复现问题的时间，请务必提供可重现的代码，否则我们会将issue直接标记为invalid并关闭）\r\n\r\n\r\nCode should based on https://github.com/sharding-sphere/sharding-sphere-example\r\n（代码请基于 https://github.com/sharding-sphere/sharding-sphere-example）\r\n"},"comment":{"url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/comments/395047591","html_url":"https://github.com/sharding-sphere/sharding-sphere/issues/906#issuecomment-395047591","issue_url":"https://api.github.com/repos/sharding-sphere/sharding-sphere/issues/906","id":395047591,"node_id":"MDEyOklzc3VlQ29tbWVudDM5NTA0NzU5MQ==","user":{"login":"afredlyj","id":4151455,"node_id":"MDQ6VXNlcjQxNTE0NTU=","avatar_url":"https://avatars2.githubusercontent.com/u/4151455?v=4","gravatar_id":"","url":"https://api.github.com/users/afredlyj","html_url":"https://github.com/afredlyj","followers_url":"https://api.github.com/users/afredlyj/followers","following_url":"https://api.github.com/users/afredlyj/following{/other_user}","gists_url":"https://api.github.com/users/afredlyj/gists{/gist_id}","starred_url":"https://api.github.com/users/afredlyj/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/afredlyj/subscriptions","organizations_url":"https://api.github.com/users/afredlyj/orgs","repos_url":"https://api.github.com/users/afredlyj/repos","events_url":"https://api.github.com/users/afredlyj/events{/privacy}","received_events_url":"https://api.github.com/users/afredlyj/received_events","type":"User","site_admin":false},"created_at":"2018-06-06T12:15:38Z","updated_at":"2018-06-06T12:15:38Z","author_association":"NONE","body":"原因和`https://github.com/sharding-sphere/sharding-sphere/issues/899`一样，配置了读写分离，for update 路由到从库，导致后续的update获取锁超时，暂时用`HintManager`强制走主库。"}}
{"actor":{"display_login":"afredlyj"}}