How to get the current database transaction id
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
In this article, I’m going to show you how to get the current database transaction id. The transaction id is very useful for logging, especially if you want to correlate multiple log entries that are executed within the context of the same database transaction.
How to get the current database transaction id @vlad_mihalcea https://t.co/DcCfWujDjB pic.twitter.com/r09PMC4bOz— Java (@java) January 17, 2019
As I explained in this article, in a relational database, transactions are mandatory. Even if you don’t declare a database transaction, you are still going to use one.
The only thing you configure is the transaction scope. So, if you don’t declare the transaction boundaries explicitly, every SQL statement is going to be executed in its own database transaction, meaning that you are going to use the auto-commit mode.
However, most often, a business use case requires executing multiple SQL statements, in which case the auto-commit mode will prevent the database from being able to roll back all the statements executed in the context of the current running business use case.
For this reason, service layer methods are annotated with the
So, when executing a
@Transactional method, all the SQL statements executed in the context of the current business use case are going to share the same database transaction.
Getting the transaction id
You can get the database transaction identifier from the database using a database-specific SQL query.
When using Oracle, transaction identifiers are assigned only if the current transaction executed an INSERT, UPDATE, or DELETE statement.
So, for read-write transactions, if you want to get the transaction identifier, you can execute the following SQL query:
SELECT RAWTOHEX(tx.xid) FROM v$transaction tx JOIN v$session s ON tx.addr=s.taddr
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 offers information about our current session or database connection. By matching the session address between the
v$session views, we can find the current running transaction identifier given by the
xid column in 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. For more details about the undo log, check out this article.
When using SQL Server, you just have to execute the following SQL query:
SELECT CONVERT(VARCHAR, CURRENT_TRANSACTION_ID())
CURRENT_TRANSACTION_ID function returns a
BIGINT column value, we are using
CONVERT to get its String representation.
When using PostgreSQL, transaction identifiers are assigned only when a write operation is executed (e.g., INSERT, UPDATE, DELETE). You can execute the following SQL query to get the current transaction id:
SELECT CAST(pg_current_xact_id_if_assigned() AS text)
pg_current_xact_id_if_assigned 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()
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.
When using the HyperSQL database, you can execute the following SQL query to get the current transaction id:
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Getting the current database transaction id comes in handy, especially if you want to log this info for every executed SQL statement.