Informix

 View Only

  • 1.  Detect if we are inside a transaction

    Posted Fri November 05, 2021 04:38 AM

    We have a library function that needs to run inside a transaction, so we would like to check if we are inside a transaction, and if not, start a transaction, do the work and commit. If there is a transaction already, we don't do anything. 
    Currently we do a "begin work" and check if that gives -535, but I wonder if there is a more elegant solution.

    I searched and came across some old posts on the subject from the newsgroup, for instance one answer which I distilled into this:

    select sysmaster:bitval(txptab.flags,"0x2") 
from sysmaster:systxptab txptab, sysmaster:sysrstcb rstcb 
where rstcb.address = txptab.owner 
and sid=DBINFO("sessionid");


    But for the current session this always gives 1, I suspect that is because of the implicit transaction that is started for each statement. It works if I check other sessions, but that can't be used for my case.

    I'm on 14.10.FC5, if that makes a difference.



    ------------------------------
    Øyvind Gjerstad
    Developer/Architect
    PostNord AS
    ------------------------------

    #Informix


  • 2.  RE: Detect if we are inside a transaction

    IBM Champion
    Posted Fri November 05, 2021 06:06 AM
    Hi Øyvind,

    looks like what you're after isn't really exposed anywhere through SMI (nor onstat): the simple fact whether an explicit BEGIN WORK had been issued in a given session.

    What does exist is mi_transaction_state(), so following UDR created on top of that might do the trick:

    create function txstate() returns int external name '(mi_transaction_state)' language C;

    With this you'd get:

    for an implicit transaction:

    select txstate() txstate;

    txstate

          2​


    for an explicit transaction:

    begin work; select txstate() txstate;

    txstate

          1​

    and for 'not in transaction':

    execute function txstate();

(expression)

           0​


    HTH,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 3.  RE: Detect if we are inside a transaction

    Posted Fri November 05, 2021 08:45 AM
    Thank you, Andreas, that worked!

    ------------------------------
    Øyvind Gjerstad
    Developer/Architect
    PostNord AS
    ------------------------------

    Original Message


  • 4.  RE: Detect if we are inside a transaction

    IBM Champion
    Posted Mon November 08, 2021 02:24 AM
    Hi Andreas,

    nice and smart solution!

    Thank you very much.

    Henri

    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021
    ------------------------------

    Original Message


  • 5.  RE: Detect if we are inside a transaction

    IBM Champion
    Posted Fri November 05, 2021 06:46 AM
    The solution I use is for every app to use library functions to BEGIN WORK and to COMMIT or ROLLBACK WORK that check a pair of global variables to see if the database supports transactions and whether it is in ANSI mode or not (they are set after connecting to the database). That BEGIN function sets a global flag indicating that a transaction has begun. The COMMIT and ROLLBACK functions check it to see if a transaction is active. You can do the same.

    Another option is for the function to open or maintain a second connection, switch connections, use that second session to check the transaction status of the main session using the query you posted, then switch back to the main session to do what's needed. That will only work if at least one of the sessions is not a shared memory connection type (can't have two shared memory connections in the same process).

    Managing multiple connections, at least in ESQL/C, is not difficult code. Dostats, for example, uses two connections when you ask it to process all user databases, one to get the list of databases that also manages a second connection to whichever database it is currently processing to do the actual work. My dbcopy utility uses two connections, one to the source database and a second to the target database to save the overhead of connecting to the target through the source server as a remote connection. If the databases are in separate instances this is much faster.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message