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

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

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

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