Getting current transaction ID
Answered: green (hollow confidence) — Andreas Legner and Art Kagel explain that the transaction ID is a per-session slot and is reused, and how to detect an open transaction via systrans flags; the asker accepted that no unique ID exists.
Advisory only.
Posted in 2025
Oyvind Gjerstad tried to detect new transactions from Hibernate/JBoss using a UDR on mi_get_transaction_id and got the same ID twice (14.10.FC10). Andreas Legner explained the ID indexes a per-session transaction structure allocated at connect, not a unique transaction; check systrans tx_flags for flag 2 for BEGIN WORK. Art Kagel showed a sysmaster query confirming reuse. No unique ID exists.
Topics: Stored Procedures & SPL, Connectivity: ODBC / JDBC / .NET, Java & JDBC Development
We are trying to debug an issue with transaction handling in Hibernate and JBoss. We want to see if a new transaction has been created in certain scenarios.
So I did some research if it was possible to get the current transaction ID in SQL, but found nothing.
But I did find the
mi_get_transaction_id()
internal function, so I then created a normal UDR on top of that:
create function get_transaction_id() returns int external name '(mi_get_transaction_id)' language C;However, this simple script returns the same transaction ID twice, which I didn't expect.
begin; execute function get_transaction_id(); commit; begin; execute function get_transaction_id(); commit;
So, it looks like the transaction IDs are reused? If I run this again in a new session, I get a new ID, but again the same ID twice.
Any tips on how to find out whether a new transaction has been created? Since I'm mentioning JBoss, the language obviously is java, JDBC 9.40.JC9, engine version is 14.10.FC10
Thanks in advance
------------------------------
Øyvind Gjerstad
Developer/Architect
PostNord AS
------------------------------
Hi
Øyvind,
in Informix, a transaction ID's purpose is NOT to identify a transaction in a series of transactions (within same or separate sessions). It rather identifies a transaction among concurrent transactions, with "transaction" here meaning a transaction structure that's being allocated to every session, regardless whether any SQL level transaction currently is underway or ever will be.
You can see these structures in onstat -x, and you can see that even a simple dbaccess session not doing anything would have one associated to it. Whatever such session would do would use this transaction structure for any transaction control.
Consider the transaction ID an index into an array of these structures. You'll find that your UDR will return an ID even without any BEGIN WORK.
I guess what you're after is whether a session, in whichever way, has issued a BEGIN WORK?
Once you've determined your transaction ID, query sysmaster:systrans for it and check tx_flags for containing flag 2.
HTH,
Andreas
------------------------------
Andreas Legner
Informix Dev
HCL Software
------------------------------
Hi Andreas! Inside the EJB container there is always (at least in our setup) a transaction started. But there is a possibility to configure if a class or a method should start its own transaction (putting the existing transaction "on hold" somehow), and this is where we want to investigate how (if) that mechanism works. I assume the session id will be the same for the new transaction started. I know, Informix doesn't support nested transaction, so I have always been curious how this actually is implemented. Maybe there is a new session, I don't know, that is probably the most obvious solution. This is what we would like to get to the bottom of. We are testing a newer version of JBoss now, with a new version of Hibernate, and Informix is no longer a officially supported database (only community supported), so we did have some challenges. I guess this concept of a unique (over time) transaction ID just does not exists, then. ------------------------------ Øyvind Gjerstad Developer/Architect PostNord AS ------------------------------
Øyvind:
You can map your session id to the current transaction in SQL with:
select txid, sid
from sysmaster:sysrstcb as r
join sysmaster:systxptab as t
on t.owner = r.address and r.sid = dbinfo('sessionid');However, as you have discovered, the transaction id for a given session is reused by that session and appears to be assigned to that session at connect time. Witness, no transaction started:
$ dbaccess art -Database selected.
> select txid, sid
from sysmaster:sysrstcb as r
join sysmaster:systxptab as t
on t.owner = r.address and r.sid = dbinfo('sessionid');> > >txid sid
42 90
1 row(s) retrieved.
>
begin work;
Started transaction.
> select txid, sid
from sysmaster:sysrstcb as r
join sysmaster:systxptab as t
on t.owner = r.address and r.sid = dbinfo('sessionid');> > >txid sid
42 90
1 row(s) retrieved.
>
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------