Lock wait timeout exceeded; try restarting transaction bug

[Y-sir]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.5.1

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy MySQL 5.7.44-log

Expected behavior

Execution Success

Actual behavior

Execution failed with error Usage question that isn't answered in docs or discussion

Reason analyze (If you can)

The situation is like this. When I connect to the proxy database and execute the update statement, an error is reported: Lock wait timeout exceeded; try restarting transaction
I did not find the wrong SQL because no errors were reported in the proxy library before the update statement was executed. However, I checked the proxied MySQL database, which is the source database, to see if there were any uncommitted transactions in the source database.

1、Use
SHOW ENGINE INNODB STATUS;
to check whether there are any uncommitted transactions, as shown below

It was found that two transactions of MySQL thread id 935711 and MySQL thread id 935688 were not committed.

2、Use
SELECT * FROM information_schema.innodb_trx WHERE trx_mysql_thread_id IN (SELECT trx_mysql_thread_id FROM information_schema.innodb_trx WHERE trx_id IN (SELECT trx_id FROM information_schema.innodb_trx));
to query the SQL statements of threads 935711 and 935688. The result is shown in the figure below. trx_query is null, which means that the executed SQL statement is not recorded, which confuses me.

Since there is no error in the program itself, I cannot provide steps to reproduce the problem. The "Lock wait timeout reached; try restarting transaction" error is reported only when executing an update statement. But since trx_query is null, I don't know which statement caused the lock. This confuses me. Has anyone encountered this situation?

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

[terrymanu]

Is the behavior the same in MySQL 8.x versions?

[github-actions]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.