使用MySQL获取当前交易ID [英] Getting the current transaction ID with MySQL

问题描述

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.

这篇关于使用MySQL获取当前交易ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！