使用 MySQL 获取当前事务 ID

2022-01-06 00:00:00 identifier mariadb transactions mysql innodb

是否可以使用 MySQL 5.5 获取当前事务 ID?像……

Is it possible with MySQL 5.5 to get the current transaction ID? Something like...

BEGIN;
SELECT CURRENT_XID(); -- foo
...
SELECT CURRENT_XID(); -- also foo
ROLLBACK;

SELECT CURRENT_XID(); -- NOT foo

实际值并不重要，只要我能得到一些唯一标识符，并且在同一事务中始终返回相同的值.

The actual value isn't important, as long as I can get some unique identifier that will always return the same value throughout the same transaction.

推荐答案

这是一个糟糕的解决方案:

Here is a bad solution:

CREATE FUNCTION CURRENT_XID() RETURNS VARCHAR(18)
BEGIN
    RETURN (SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX 
            WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID());
END

  1. 如果您只在事务内执行只读操作，则返回 NULL.
  2. 最重要的是 INNODB_TRX 不与事务同步.因此，如果您在结束交易后立即调用 CURRENT_XID() ，您将获得一个值，但稍后再运行它，您将获得 NULL.莉>
  1. Returns NULL if your only doing read only operations inside of a transaction.
  2. Most importantly the INNODB_TRX is not synchronised with the transactions. So if you call CURRENT_XID() immediately after ending the transaction you will get a value back, but then wait a second and run it against and you will get NULL.

相关文章