Get current database transaction id using jdbc/hib

问题:

I've looked around on Google for this but couldn't find anything relevant. Basically, I want to get hold of long running transactions.

For now, I go through information_schema.INNODB_TRX or have a look at the output of show engine innodb status to find the trx_id and then turn on general_logs to see what all queries are running.

Is there a way, I can get hold of this transaction_id in my code using jdbc or hibernate so that I can log it in my server logs?

回答1:

With PostgreSQL, you could simply run this native query to get the current transaction id:

Number transactionId = (Number) session
   .createSQLQuery("select txid_current()")
   .uniqueResult();

For MySQL, you need to run 5.7 or later:

Number transactionId = (Number) session
   .createSQLQuery(
            "SELECT GTID " +
            "FROM events_transactions_current e " +
            "JOIN performance_schema.threads t ON e.THREAD_ID = t.THREAD_ID " +
            "WHERE t.PROCESSLIST_ID = CONNECTION_ID()")
   .uniqueResult();

or for MySQL 5.5:

Number transactionId = (Number) session
   .createSQLQuery(
            "SELECT trx_id " +
            "FROM information_schema.innodb_trx " +
            "WHERE trx_mysql_thread_id = CONNECTION_ID()")
   .uniqueResult();