[GitHub] [shardingsphere] burgerWhoo commented on issue #16894: insert values Lock wait timeout exceeded

[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