How to get the current database transaction id using JDBC or Hibernate?

Question

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?

Answer 1

Oracle

When using Oracle, you have to execute the following SQL query:

SELECT RAWTOHEX(tx.xid)
FROM v$transaction tx
JOIN v$session s ON tx.ses_addr = s.saddr

The v$transaction view provides information about the currently running database transactions. However, there can be multiple transactions running in our system, and that's why we are joining the v$transaction with the v$session view.

The v$session view offers information about our current session or database connection. By matching the session address between the v$transaction and v$session views, we can find the current running transaction identifier given by the xid column in the v$transaction view.

Because the xid column is of type RAW, we are using RAWTOHEX to convert the transaction identifier binary value to its hexadecimal representation.

Oracle assigns a transaction identifier only if it needs to assign an undo segment, which implies that an INSERT, UPDATE or DELETE DML statement has been executed.

So, read-only transactions will not have a transaction identifier assigned.

SQL Server

When using SQL Server, you just have to execute the following SQL query:

SELECT CONVERT(VARCHAR, CURRENT_TRANSACTION_ID())

Because the CURRENT_TRANSACTION_ID function returns a BIGINT column value, we are using CONVERT to get its String representation.

PostgreSQL

When using PostgreSQL Server, you can execute the following SQL query to get the current transaction id:

SELECT CAST(txid_current() AS text)

Because the txid_current function returns a BIGINT column value, we are using CAST to get its String representation.

MySQL and MariaDB

When using MySQL or MariaDB, you can execute the following SQL query to get the current transaction id:

SELECT tx.trx_id
FROM information_schema.innodb_trx tx
WHERE tx.trx_mysql_thread_id = connection_id()

The innodb_trx view in the information_schema catalog provides information about the currently running database transactions. Since there can be multiple transactions running in our system, we need to filter the transaction rows by matching the session or database connection identifier with the currently running session.

Just like it was the case with Oracle, since MySQL 5.6, only read-write transactions will get a transaction identifier.

Because assigning a transaction id has a given overhead, read-only transactions skip this process. For more details, check out this article.

This read-only transaction optimization works the same way in MariaDB, meaning that a transaction id is only assigned for read-write transactions only.

HSQLDB

When using the HyperSQL database, you can execute the following SQL query to get the current transaction id:

VALUES (TRANSACTION_ID())

Logging the transaction id using MDC

The transaction id is useful for logging because it allows us to aggregate all actions that were executed in the context of a given database transaction.

Assuming we have encapsulated the SQL queries above in a transactionId method, we could extract the current transaction id and pass it to the Logger framework as an MDC variable.

So, for SLF4J, you can use the put method as illustrated by the following example:

MDC.put("txId", String.format(" TxId: [%s]", transactionId(entityManager)));

MDC (Mapped Diagnostic Context) is for logging what ThreadLocal is to Java threads. Basically, MDC allows you to register key/value pairs that are confined to the currently running thread and which you can reference when the logging framework builds log messages.

To print the "txId" log variable to the log, we need to include this variable in the log appender pattern:

<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
    <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
        <level>TRACE</level>
    </filter>
    <encoder>
        <Pattern>%-5p [%t]:%X{txId} %c{1} - %m%n</Pattern>
        <charset>UTF-8</charset>
    </encoder>
</appender>

The %X{txId} pattern is used to reference the txId log variable.