如何使用JDBC或Hibernate获取当前的数据库事务ID? [英] How to get the current database transaction id using JDBC or Hibernate?

问题描述

为此，我已经在Google上四处浏览，但找不到任何相关内容.基本上，我想掌握长期运行的事务.

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

现在，我浏览information_schema.INNODB_TRX或查看show engine innodb status的输出以找到trx_id，然后打开general_logs来查看所有查询正在运行.

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.

有没有办法使用jdbc或hibernate在代码中保留此transaction_id，以便可以将其记录在服务器日志中?

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?

推荐答案

由于这是非常常见的要求，因此我写了这篇文章进行了介绍这个主题非常详细.

Since this is a very common requirement, I wrote this article to cover this topic in great detail.

Oracle

使用Oracle时，必须执行以下SQL查询:

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

v$transaction视图提供有关当前正在运行的数据库事务的信息.但是，我们的系统中可能有多个事务在运行，这就是为什么我们要在v$session视图中加入v$transaction的原因.

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.

v$session视图提供有关当前会话或数据库连接的信息.通过匹配v$transaction和v$session视图之间的会话地址，我们可以找到v$transaction视图中xid列给出的当前正在运行的事务标识符.

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.

由于xid列的类型为RAW，因此我们使用RAWTOHEX将交易标识符二进制值转换为其十六进制表示形式.

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

Oracle仅在需要分配撤消段时才分配事务标识符，这意味着已执行INSERT，UPDATE或DELETE DML语句.

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.

SQL Server

使用SQL Server时，只需执行以下SQL查询:

SQL Server

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

SELECT CONVERT(VARCHAR, CURRENT_TRANSACTION_ID())

由于CURRENT_TRANSACTION_ID函数返回BIGINT列值，因此我们使用CONVERT来获取其String表示形式.

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

使用PostgreSQL服务器时，可以执行以下SQL查询以获取当前的事务ID:

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

SELECT CAST(txid_current() AS text)

由于txid_current函数返回BIGINT列值，因此我们使用CAST来获取其String表示形式.

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

使用MySQL或MariaDB时，您可以执行以下SQL查询以获取当前的交易ID:

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()

information_schema目录中的innodb_trx视图提供有关当前正在运行的数据库事务的信息.由于我们的系统中可以运行多个事务，因此我们需要通过将会话或数据库连接标识符与当前正在运行的会话进行匹配来过滤事务行.

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.

就像Oracle一样，从MySQL 5.6开始，只有读写事务才会获得事务标识符.

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

由于分配事务ID具有给定的开销，因此只读事务将跳过此过程.有关更多详细信息，请参见这篇文章.

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

此只读事务优化在MariaDB中的工作方式相同，这意味着仅将事务ID分配给读写事务.

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

使用HyperSQL数据库时，可以执行以下SQL查询以获取当前的事务ID:

HSQLDB

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

VALUES (TRANSACTION_ID())

使用MDC记录交易ID

事务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.

假设我们已将上述SQL查询封装在transactionId方法中，则可以提取当前事务ID并将其作为MDC变量传递给Logger框架.

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.

因此，对于SLF4J，可以使用put方法，如以下示例所示:

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(映射的诊断上下文)用于将ThreadLocal记录到Java线程中.基本上，MDC允许您注册局限于当前正在运行的线程的键/值对，并且在日志记录框架生成日志消息时可以引用这些键/值对.

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.

要将"txId"日志变量打印到日志，我们需要在日志附加程序模式中包括此变量:

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>

%X{txId}模式用于引用txId日志变量.

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

有关MDC日志记录的更多详细信息，您可以阅读这篇文章.

这篇关于如何使用JDBC或Hibernate获取当前的数据库事务ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！