You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/04/18 08:41:08 UTC
[GitHub] [shardingsphere] xsong123 opened a new issue, #16894: insert values Lock wait timeout exceeded
xsong123 opened a new issue, #16894:
URL: https://github.com/apache/shardingsphere/issues/16894
shardingsphere version 5.0.0
mysql version 5.7
**rule as belows:**
# 筛选结果临时表
filter_import_result:
actualDataNodes: ds_${0..0}.filter_import_result_${0..15}
databaseStrategy:
standard:
shardingColumn: asset_code
# 算法名称
shardingAlgorithmName: filter_import_result_db_hash_mod
tableStrategy: &table001
standard:
shardingColumn: asset_code
# 算法名称
shardingAlgorithmName: filter_import_result_table_hash_mod
shardingAlgorithms:
filter_import_result_table_hash_mod:
type: HASH_MOD
props:
sharding-count: 16
**sql as belows:**
INSERT INTO FILTER_IMPORT_RESULT ( ASSET_CODE, USER_NAME, IS_REPEAT, IS_FAIL ) VALUES ( 'TEST-PCL-40001259' , 'admin' , 'N' , 'N' ) , ( 'TEST-PCL-40000004' , 'admin' , 'N' , 'N' ) , ( 'TEST-PCL-40000050' , 'admin' , 'N' , 'N' );
**error as belows:**
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_291]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_291]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_291]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_291]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.48.jar:5.1.48]
at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.48.jar:5.1.48]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952) ~[mysql-connector-java-5.1.48.jar:5.1.48]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933) ~[mysql-connector-java-5.1.48.jar:5.1.48]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869) ~[mysql-connector-java-5.1.48.jar:5.1.48]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524) ~[mysql-connector-java-5.1.48.jar:5.1.48]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675) ~[mysql-connector-java-5.1.48.jar:5.1.48]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465) ~[mysql-connector-java-5.1.48.jar:5.1.48]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1912) ~[mysql-connector-java-5.1.48.jar:5.1.48]
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1251) ~[mysql-connector-java-5.1.48.jar:5.1.48]
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497) ~[druid-1.2.6.jar:1.2.6]
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:322) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement$2.executeSQL(ShardingSpherePreparedStatement.java:318) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85) ~[shardingsphere-infra-executor-5.0.0-tis-220212.jar:5.0.0]
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64) ~[shardingsphere-infra-executor-5.0.0-tis-220212.jar:5.0.0]
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101) ~[shardingsphere-infra-executor-5.0.0-tis-220212.jar:5.0.0]
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:89) ~[shardingsphere-infra-executor-5.0.0-tis-220212.jar:5.0.0]
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:81) ~[shardingsphere-infra-executor-5.0.0-tis-220212.jar:5.0.0]
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65) ~[shardingsphere-infra-executor-5.0.0-tis-220212.jar:5.0.0]
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49) ~[shardingsphere-infra-executor-5.0.0-tis-220212.jar:5.0.0]
at org.apache.shardingsphere.driver.executor.JDBCLockEngine.doExecute(JDBCLockEngine.java:116) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.driver.executor.JDBCLockEngine.execute(JDBCLockEngine.java:93) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.execute(DriverJDBCExecutor.java:127) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:298) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_291]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_291]
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.5.3.jar:3.5.3]
at com.sun.proxy.$Proxy340.execute(Unknown Source) ~[?:?]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.3.jar:3.5.3]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.3.jar:3.5.3]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.3.jar:3.5.3]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.3.jar:3.5.3]
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.3.jar:3.5.3]
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] burgerWhoo commented on issue #16894: insert values Lock wait timeout exceeded
Posted by GitBox <gi...@apache.org>.
burgerWhoo commented on issue #16894:
URL: https://github.com/apache/shardingsphere/issues/16894#issuecomment-1133530590
> Can you provide a reproduce demo?
Hello, I also encountered the same problem, the version is shardingsphere-proxy 5.1.1.
Here are the reproduce steps:
- open two terminal
- In the first terminal,
- set autocommit = 0;
- commit;
- set autocommit = 1;
- select * from t_order where order_id = 2 for update;
- In the second terminal,
- select * from t_order where order_id = 2 for update; (**This sql will be blocked**)
I noticed that this code was submitted at #13813 in `org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask`
```
if (!backendConnection.isAutoCommit() && !backendConnection.getTransactionStatus().isInTransaction()) {
BackendTransactionManager transactionManager = new BackendTransactionManager(backendConnection);
transactionManager.begin();
}
```
When executing the COMMIT statement, the transaction will be closed, but if set autocommit to true later (this is a very common operation), the transaction will be reopened. Then all the subsequent statement will be in the transaction, and the statements that should be committed immediately holds the lock for a long time, which trigger the lock waiting.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] littlePoBoy commented on issue #16894: insert values Lock wait timeout exceeded
Posted by GitBox <gi...@apache.org>.
littlePoBoy commented on issue #16894:
URL: https://github.com/apache/shardingsphere/issues/16894#issuecomment-1113126245
I also encountered this problem，shardingsphere-proxy version 5.1.0
mysql version 5.7
update table Lock wait timeout exceeded
the amount of concurrency is 1。
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] xsong123 commented on issue #16894: insert values Lock wait timeout exceeded
Posted by GitBox <gi...@apache.org>.
xsong123 commented on issue #16894:
URL: https://github.com/apache/shardingsphere/issues/16894#issuecomment-1126679444
> What is the amount of concurrency？
any ideas?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lcxywfe commented on issue #16894: insert values Lock wait timeout exceeded
Posted by GitBox <gi...@apache.org>.
lcxywfe commented on issue #16894:
URL: https://github.com/apache/shardingsphere/issues/16894#issuecomment-1137083289
set autocommit=0;
set autocommit=1;
update xxx;
Same issue, this update will not committed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] xsong123 commented on issue #16894: insert values Lock wait timeout exceeded
Posted by GitBox <gi...@apache.org>.
xsong123 commented on issue #16894:
URL: https://github.com/apache/shardingsphere/issues/16894#issuecomment-1112791316
the amount of concurrency is 15.
------------------ 原始邮件 ------------------
发件人: "apache/shardingsphere" ***@***.***>;
发送时间: 2022年4月26日(星期二) 下午5:57
***@***.***>;
***@***.******@***.***>;
主题: Re: [apache/shardingsphere] insert values Lock wait timeout exceeded (Issue #16894)
What is the amount of concurrency？
—
Reply to this email directly, view it on GitHub, or unsubscribe.
You are receiving this because you authored the thread.Message ID: ***@***.***>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #16894: insert values Lock wait timeout exceeded
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #16894:
URL: https://github.com/apache/shardingsphere/issues/16894#issuecomment-1109596935
What is the amount of concurrency？
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #16894: insert values Lock wait timeout exceeded
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #16894:
URL: https://github.com/apache/shardingsphere/issues/16894#issuecomment-1126725934
Can provide a reproduce demo?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #16894: insert values Lock wait timeout exceeded
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #16894: insert values Lock wait timeout exceeded
URL: https://github.com/apache/shardingsphere/issues/16894
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org