Getting current transaction ID

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 ------------------------------