Clever Geek Handbook

Get current database transaction id using jdbc / hibernate?

I looked on google for this but couldn't find anything suitable. Basically, I want to get long-running transactions.

I am currently browsing information_schema.INNODB_TRX

or looking at the output show engine innodb status

to find trx_id

and then turn general_logs

it on to see that all requests are running.

Is there a way I can get this transaction_id

in my code using jdbc

or hibernate

so that I can log it to the server logs?

+3


source to share


1 answer


With PostgreSQL, you can simply run this custom 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 newer :



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();
+2


source






More articles:


All Articles