获取mysql id,使用MySQL获取当前交易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

Returns NULL if your only doing read only operations inside of a transaction.

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.