Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  gTA cannot be killed

    Posted Wed May 13, 2020 11:45 AM
    Hallo,

    IBM Informix Dynamic Server Version 12.10.UC12W1

    SUSE Linux Enterprise Server 11 (x86_64)
    VERSION = 11
    PATCHLEVEL = 4

    My problem:
    onstat -x shows this row:
    548c29c8 -TB-G 0        12     152835:0x1581128  152835:0x1581768  DIRTY   00:00:00 0

    onstat - k shows:
    4729d818 0        (548c29c8) 8254df98     IX   400001   0           0
    47544ad8 0        (548c29c8) 826384d8 HDR+IX   2005f6   0           0
    47b74418 0        (548c29c8) 47eb4cd8 HDR+X    2005f6   11d03       0    I
    47ca4bb8 0        (548c29c8) 47b74418 HDR+X    2005f7   11d03    K- 1    I
    47eb4cd8 0        (548c29c8) 47544ad8 HDR+X    2005f6   3705        0
    47ec7ed8 0        (548c29c8) 0        HDR+IX   200522   0           0
    825410f8 0        (548c29c8) 4729d818 HDR+X    400001   112800      0
    8254df98 0        (548c29c8) 47ca4bb8 HDR+X    2005f8   11d03    K- 1    I
    826384d8 0        (548c29c8) 826bb098 HDR+B    200522   500         33
    826bb098 0        (548c29c8) 82835458 HDR+X    200522   510         0      D
    82835458 0        (548c29c8) 828abbf8 HDR+X    200522   100a        0     U
    828abbf8 0        (548c29c8) 47ec7ed8 HDR+X    200522   511         0

    onstat - G shows
    Global Transaction Identifiers
    address   flags  isol    timeout  fID        gtl  bql  data
    4e8e43d0  -L--G  COMMIT  15       131075     30   28   312D613134313436343A393762323A35653
    763383636613A316338333062613134313436343A393762323A35653763383636613A316338333133
    548c29c8  -T--G  DIRTY   30000    1096044365 30   13   3132372E302E312E312E746D31353839333
    73434303432323130303030373132372E302E312E312E746D31
    57950318  -T--G  DIRTY   30000    1096044365 30   13   3132372E302E312E312E746D31353839333
    73434313234333330303030383132372E302E312E312E746D32
    3 active, 640 total

    I tried to kill this transaction with:
    onmode -H 0x548c29c8
    Output:
    onmode -H may only be used to kill heuristically completed transactions

    onmode -Z 0x548c29c8
    onmode: Cannot kill transaction 0x548c29c8.
    Only I-STAR subordinates that are PREPARE'd or HEURISTICally ABORT'd
    may be heuristically completed.

    How can i end this transaction.
    No user thread attached to the transaction.The flag code for position 1 is not set.  (See 548c29c8 -TB-G 0)

    Thanks for any help in advance!
    Stay healthy!!

    Heinz


    ------------------------------
    Heinz Weitkamp
    ------------------------------

    #Informix


  • 2.  RE: gTA cannot be killed

    Posted Wed May 13, 2020 09:50 PM
    Hi Heinz,
    If the global transaction is an orphaned transaction, if the logical log switch occurs at the rate (or number) set in LTXHWM, it will be rolled back naturally as shown below.
    -- Example of online.log messages when global transaction rollback

23:42:09  Aborting Long Transaction: tx: 0x56656bd8 no user info due to XA or distributed (2-phase commit) transaction
23:42:09  Aborting Long Transaction: tx: 0x56656e28 no user info due to XA or distributed (2-phase commit) transaction
23:42:13  Checkpoint Completed:  duration was 4 seconds.
23:42:13  Thu Jul  1 - loguniq 306, logpos 0x3fff33b4, timestamp: 0xdedfaa19 Interval: 5616

23:42:13  Maximum server connections 15
23:42:13  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 1, Plog used 83432, Llog used 182271

23:42:13  Session completed abnormally. Rolling back tx id 24, flags 0x108463b
23:42:13  Session completed abnormally. Rolling back tx id 32, flags 0x108463b
23:42:13  Long Transaction 0x56656e28 Aborted. Rollback Duration: 0 Seconds
23:42:14  Long Transaction 0x56656bd8 Aborted. Rollback Duration: 0 Seconds​

    Alternatively, you can also force the log switch to fire with the onmode -l command directly.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


  • 3.  RE: gTA cannot be killed

    Posted Fri May 15, 2020 07:48 AM

    Hi SangGyu Jeong,

    thanks for the help and quick answer.

    Yes, the database then cleaned up the transaction. But I already had similar problems on development and test computers, whereby the database did not automatically abort the long transaction, even though LTXHWM was exceeded.

    Only a restart then fixed the problem. This solution is not optimal for the production server.

    So my question is whether there is a way to end such TA without restarting.

    Greeting

    Heinz



    ------------------------------
    Heinz Weitkamp
    ------------------------------

    Original Message


  • 4.  RE: gTA cannot be killed

    Posted Fri May 15, 2020 08:57 AM
    Get your hands on a copy of "IBM Informix Developer's Handbook" on IBM red books site ( http://www.redbooks.ibm.com/abstracts/sg247884.html?Open ) . Search the pdf for xa_tool and you will find an example esqlc program that can terminate orphaned XA transactions. 
    You can also use java to call the functions used on the program. And there is/was an RFE asking for adding an administrative command / SQL API for removing orphaned XA connections.

    ------------------------------
    Luis Marques
    ------------------------------

    Original Message


  • 5.  RE: gTA cannot be killed

    Posted Tue May 19, 2020 02:55 AM 
    Thank you very much for the good hint, Luis Marques.
Will use the program at the next opportunity.

    greeting
    Heinz


    ------------------------------
    Heinz Weitkamp
    ------------------------------

    Original Message


  • 6.  RE: gTA cannot be killed

    Posted Tue May 19, 2020 09:42 AM
    Edited by System Admin Fri January 20, 2023 04:10 PM
    Hi,
    You can vote for: https://ibm-data-and-ai.ideas.aha.io/ideas/INFX-I-231

    This is an "old problem" with onmode -Z.

    Best regards,

    ------------------------------
    Boycho Velkov
    ------------------------------

    Original Message


  • 7.  RE: gTA cannot be killed

    Posted Tue May 19, 2020 10:07 AM
    If I'm getting this right, onmode -Z is meant for I-Star 2-phase-commit distributed transactions, whereas onmode -H is for XA global transactions.
    The later (XA transactions) typically are not owned by Informix and should be taken care of by the XA transaction manager that initiated and oversees them;  in this sense onmode -H only serves as a workaround for cases where such XA transaction remainders keep lingering, potentially blocking the Informix server.

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

    Original Message


Related Content