We are running on Percona-Server-5.5.53.
We’ve had a problem lately，there are some very simple update SQL statements take a long time.These statements are logged to slow-log.
When I execute these SQL manually in the database, the execution time is in milliseconds,But when the program executes SQL, the execution time is between 2 and 6 seconds。I executed a script that collected the “show engine innodb status” information every second,some of which was as follows:
--------show engine innodb status:
—TRANSACTION 284B7BC9, ACTIVE (PREPARED) 0 sec committing
mysql tables in use 1, locked 1
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 8572882, OS thread handle 0x7f6e31286700, query id 1231452122 172.26.0.74 user1 query end
update user_info SET recommend_votes_num = recommend_votes_num - 3 WHERE user_id = 7174307 AND recommend_votes_num >= 3
—TRANSACTION 284B8FC4, ACTIVE (PREPARED) 1 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 8572882, OS thread handle 0x7f6e31286700, query id 1231463252 172.26.0.74 user1 query end
update user_info SET recommend_votes_num = recommend_votes_num - 3 WHERE user_id = 2723238 AND recommend_votes_num >= 3
—TRANSACTION 284B8FAD, ACTIVE (PREPARED) 1 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 8573333, OS thread handle 0x7f7168ab6700, query id 1231463183 172.26.0.70 user1 query end
update user_info SET recommend_votes_num = recommend_votes_num - 4 WHERE user_id = 4133846 AND recommend_votes_num >= 4
My questions is :
1.Whether these transactions are XA transactions?
2.What determines whether a transaction is an xa transaction,
3.My program does not use XA transactions. If these transactions are XA transactions, why does percona-server5.5.53 recognize them as XA transactions
I hope someone can help me. Thank you very much!