IDS Forum

Re: Session "Waiting on a transaction"

Posted By: Frank Langelage
Date: Sunday, 23 August 2015, at 5:19 a.m.

In Response To: Re: Session "Waiting on a transaction" (Marcus Haarmann)

Hi Marcus,

no, that's not the case here. I had such a few times in the past.
But in this case the session with the global transaction appears during
deployment.
When deployment is cancelled by the WildFly instance after timeout of 5
minutes the transaction is cancelled.
The session is still there, with Y instead of T again:
1147f5268 Y--P--- 6298 mbi - 11ed60da8 0 2
0 12

Frank

On 13.08.15 21:58, Marcus Haarmann wrote:
> Hi,
>
> with XA, you might get a runaway transaction, which holds locks, but has no
> owner any more.
> We sometimes have the same situation, which mostly occurs when a JBoss/Wildfly
> server
> has to be killed because there is no reaction because of heap problems, or
> dies for some reason (JDK crash).
> It then leaves open transactions, which are never closed
> (at least not from the IDS server). Even bouncing the engine does not help.
> To avoid such situations, we have developed a script, which stops the JBoss
> server from accepting new requests
> and we shut down the server manually only when we are sure there is no running
> transaction any more.
> The latest Version 9.x has a builtin option to establish a graceful shutdown,
> I read it in the announcement.
> When you are using clustering, the availability of the Server in the cluster
> has to be purged, which results
> in no more requests arriving. In case of no clustering, an option would be to
> shut down the JNDI listener, but the problem
> is that you cannot stop the server without JNDI ...
> We are closing the Defaultpartition, which is base for Clustering, via JMX.
> (at least this is working on older versions).
>
> We have written a short java app which attaches to the runaway XA transaction
> and terminates it.
> (rather complex, internal stuff, which is rarely used in normal programming.
> But it works.).
> You can see those transactions normally by running onstat -x and you will see
> a line like:
> 268b0abe0 -T--G 0 20 ....
> The third column tells you that this transaction has no owner any more. The
> fourth column is the number of locks which
> the transaction is holding.
>
> If you are interested, I can share the code.
>
> Marcus Haarmann
>
> ----- Ursprüngliche Mail -----
>
> Von: david@smooth1.co.uk
> An: ids@iiug.org
> Gesendet: Dienstag, 11. August 2015 23:55:47
> Betreff: Re: Session "Waiting on a transaction" [35596]
>
> What does onstat -x give?
>
> Whaat does "onstat -g ses <sid>" for the session give?
>
> Use the tid from the "onstat -g ses" what does "onstat -g stk <tid>" give?
>
> Also
>
> onstat -g con
> onstat -g lmx
> onstat -s
>
> would be useful.
>
> Is the server with the issue the co-ordinator or a participant in the
> transaction?
>
> Regards,
> David.
>
>> On 11 August 2015 at 22:45 Art Kagel <art.kagel@gmail.com> wrote:
>>
>>
>> Have you tried this with native Informix two phase commit transactions (ie
>> no XA)? XA transactions carry a lot of overhead.
>>
>> Art
>>
>> Art S. Kagel, President and Principal Consultant
>> ASK Database Management
>> www.askdbmgt.com
>>
>> Blog: http://informix-myview.blogspot.com/
>>
>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>> and do not reflect on the IIUG, nor any other organization with which I am
>> associated either explicitly, implicitly, or by inference. Neither do
>> those opinions reflect those of other individuals affiliated with any
>> entity with which I am affiliated nor those of the entities themselves.
>>
>> On Tue, Aug 11, 2015 at 3:40 PM, Frank Langelage <frank@lafr.de> wrote:
>>
>>> Hello,
>>>
>>> I'm using Wildfly application server (JEE server) together with Informix
>>> 12.10.FC5 as the database on Oracle Solaris SPARC 10.
>>> It's using distributed (XA) transaction using 2 databases on the same
>>> instance in this case.
>>> On start up the server gets stuck at some point and then prints out a
>>> transaction timeout.
>>> On the database level I see the output shown below. Note the session
>>> 3018 with flags T--P---.
>>> According to http://www.oninit.com/onstat/index.php?id=u the first flag
>>> T means: Waiting on a transaction. Can anybody explain what this means
>>> exactly?
>>>
>>> If only the wildfly10 instance database is on this Informix instance and
>>> the application database on another Informix instance or on another DBMS
>>> like Oracle the problem does not appear.
>>> I already reduced isolation level to dirty read, but this did not help.
>>>
>>> Any hints?
>>>
>>> Regards, Frank
>>>
>>> ---------------------------------
>>>
>>> IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 27 days
>>> 23:35:43 -- 1540096 Kbytes
>>> Userthreads
>>> address flags sessid user tty wait tout
>>> locks nreads nwrites
>>> 1147e2028 ---P--D 1 informix - 0 0 0
>>> 46 500
>>> 1147e28e8 ---P--F 0 informix - 0 0 0
>>> 0 13078
>>> 1147e31a8 ---P--F 0 informix - 0 0 0
>>> 0 524
>>> 1147e3a68 ---P--F 0 informix - 0 0 0
>>> 0 4943
>>> 1147e4328 ---P--F 0 informix - 0 0 0
>>> 0 4
>>> 1147e4be8 ---P--F 0 informix - 0 0 0
>>> 0 10
>>> 1147e54a8 ---P--F 0 informix - 0 0 0
>>> 0 0
>>> 1147e5d68 ---P--F 0 informix - 0 0 0
>>> 0 0
>>> 1147e6628 ---P--F 0 informix - 0 0 0
>>> 0 0
>>> 1147e6ee8 ---P--- 9 informix - 0 0 0
>>> 0 0
>>> 1147e77a8 ---P--B 10 informix - 0 0 0
>>> 286591 576
>>> 1147e8068 Y--P--D 11 informix - 1159705d8 0 0
>>> 37637603 0
>>> 1147e8928 ---P--D 12 informix - 0 0 0
>>> 0 0
>>> 1147e91e8 Y--P--- 3012 mbi - 1181032f8 0 2
>>> 0 0
>>> 1147e9aa8 ---P--D 16 informix - 0 0 0
>>> 0 0
>>> 1147ea368 Y--P--- 3016 mbi - 1179ec6e8 0 2
>>> 0 0
>>> 1147eac28 ---P--D 17 informix - 0 0 0
>>> 2 0
>>> 1147eb4e8 ---P--D 18 informix - 0 0 0
>>> 0 0
>>> 1147ebda8 ---P--D 19 informix - 0 0 0
>>> 0 0
>>> 1147ec668 ---P--- 32 informix - 0 0 1
>>> 7037 39254
>>> 1147ecf28 Y--P--D 28 informix - 10a2ddc30 0 0
>>> 0 0
>>> 1147ed7e8 ---P--- 30 informix - 0 0 1
>>> 11 0
>>> 1147ee0a8 ---P--- 31 informix - 0 0 2
>>> 317 47207
>>> 1147ee968 ---P--- 33 informix - 0 0 1
>>> 2609 39140
>>> 1147ef228 Y--P--- 3017 mbi - 117ea8028 0 0
>>> 0 0
>>> 1147efae8 Y--P--- 2999 mbi - 1180f1348 0 1
>>> 0 0
>>> 1147f03a8 Y--P--- 3015 mbi - 11675e388 0 2
>>> 0 0
>>> 1147f0c68 Y--P--- 3014 mbi - 1162f96e8 0 2
>>> 0 0
>>> 1147f1528 Y--P--- 3019 mbi - 116fd06e8 0 2
>>> 0 0
>>> 1147f1de8 Y--P--- 2997 mbi - 116dc8778 0 1
>>> 0 0
>>> 1147f26a8 Y--P--- 2995 mbi - 1180f1978 0 1
>>> 0 0
>>> 1147f2f68 Y--P--- 3013 mbi - 1166cdd18 0 2
>>> 0 83
>>> 1147f3828 Y--P--- 3004 mbi - 117ea8e38 0 1
>>> 0 0
>>> 1147f40e8 T--P--- 3018 mbi - 114831500 0 0
>>> 0 0
>>> 34 active, 128 total, 36 maximum concurrent
>>>
>>> Sess SQL Current Iso Lock SQL ISAM F.E.
>>> Id Stmt type Database Lvl Mode ERR ERR Vers
>>> Explain
>>> 3019 - wildfly10 DRU Wait 10 0 0 9.28 Off
>>> 3018 - wildfly10 DRU Wait 10 0 0 9.28 Off
>>> 3017 SELECT neu2e_langfr DRU Wait 8 0 0 9.28 Off
>>> 3016 - neu2e_langfr DRU Wait 8 0 0 9.28 Off
>>> 3015 - wildfly10 DRU Wait 10 0 0 9.28 Off
>>> 3014 - neu2e_langfr DRU Wait 8 0 0 9.28 Off
>>> 3013 - wildfly10 DRU Wait 10 0 0 9.28 Off
>>> 3012 SELECT wildfly10 DRU Wait 10 0 0 9.28 Off
>>> 3004 - neu2e_langfr DR Wait 0 0 9.35 Off
>>> 2999 - neu2e_langfr DR Wait 0 0 9.35 Off
>>> 2997 SELECT neu2e_langfr DR Wait 0 0 9.35 Off
>>> 2995 - neu2e_langfr DR Wait 0 0 9.35 Off
>>> 33 sysadmin DR Wait 5 0 0 - Off
>>> 32 sysadmin DR Wait 5 0 0 - Off
>>> 31 sysadmin DR Wait 5 0 0 - Off
>>> 30 sysadmin CR Not Wait 0 0 - O
>>>
>>>
>>>
>>>
> *******************************************************************************
>>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>>
>>>
>> --001a113ebc3c8c5e93051d1003e7
>>
>>
>>
> *******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

Messages In This Thread

• Session "Waiting on a transaction"
  Frank Langelage -- Tuesday, 11 August 2015, at 4:40 p.m.
  • Re: Session "Waiting on a transaction"
    Art Kagel -- Tuesday, 11 August 2015, at 5:45 p.m.
    • Re: Session "Waiting on a transaction"
      david@smooth1.co.uk -- Tuesday, 11 August 2015, at 5:55 p.m.
      • Re: Session "Waiting on a transaction"
        Marcus Haarmann -- Thursday, 13 August 2015, at 3:58 p.m.
        • Re: Session "Waiting on a transaction"
          Frank Langelage -- Sunday, 23 August 2015, at 5:19 a.m.
      • Re: Session "Waiting on a transaction"
        Frank Langelage -- Sunday, 23 August 2015, at 5:16 a.m.
        • Re: Session "Waiting on a transaction"
          Frank Langelage -- Sunday, 23 August 2015, at 5:29 a.m.
    • Re: Session "Waiting on a transaction"
      Frank Langelage -- Sunday, 23 August 2015, at 4:44 a.m.
      • Re: Session "Waiting on a transaction"
        Art Kagel -- Sunday, 23 August 2015, at 5:11 a.m.
        • Re: Session "Waiting on a transaction"
          Frank Langelage -- Sunday, 23 August 2015, at 7:36 a.m.
          • Re: Session "Waiting on a transaction"
            Art Kagel -- Sunday, 23 August 2015, at 9:34 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.