Spring @Transactional on suspending function returning Long type never commit tx,and database session show sleep status.

Comment From: lujijiang

Occurs in high concurrency scenarios,but transactionalOperator is perfect ok.

Comment From: lujijiang

Sorry for not being able to provide a sample, this is a long transaction with a lot of business logic

Comment From: lujijiang

SELECT 
  p.ID AS conn_id,
  P.USER AS login_user,
  P.HOST AS login_host,
  p.DB AS database_name,
  P.TIME AS trx_sleep_seconds,
  TIME_TO_SEC(TIMEDIFF(NOW(), T.trx_started)) AS trx_open_seconds,
  T.trx_started,
  T.trx_isolation_level,
  T.trx_tables_locked,
  T.trx_rows_locked,
  t.trx_state,
  p.COMMAND AS process_state 
FROM
  `information_schema`.`INNODB_TRX` t 
  INNER JOIN `information_schema`.`PROCESSLIST` p 
    ON t.trx_mysql_thread_id = p.id 
WHERE t.trx_state = 'RUNNING' 
  AND p.COMMAND = 'Sleep' 
  AND P.TIME > 1 
ORDER BY T.trx_started ASC;

You can use this SQL to query the database session in sleep state with mysql.

Comment From: lujijiang

Affects: v5.3.2

Comment From: snicoll

@lujijiang I am sorry but there's not much we can do based on the information you've provided. If you can share a small sample that we can run ourselves, we can have another look.