Session "Waiting on a transaction"
Frank Langelage reported that a WildFly app server using XA distributed transactions across two databases in the same Informix 12.10.FC5 instance (Solaris SPARC) hung at startup with a transaction timeout, showing a session flagged "T" (waiting on a transaction) in onstat -u; the problem vanished if the second database lived on another instance/DBMS, and lowering isolation to dirty read didn't help. Art Kagel suggested avoiding XA and letting Informix handle cross-database work natively via BEGIN/COMMIT WORK with database@server references; David asked for onstat -x, -g ses/stk/con/lmx and -s output; Marcus Haarmann described orphaned/runaway XA transactions left by killed JBoss/WildFly instances (visible in onstat -x with no owner) and offered Java code to terminate them. No confirmed resolution from the original poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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 KbytesUserthreads
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
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
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.
>
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 -
Art,
could you explain more about this or reference a documentation?
I never heard of it.
But I'm bound to what application server supports.
Frank
On 11.08.15 23:45, Art Kagel 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.
>
>
Frank:
If you just use the SQL "BEGIN WORK" and "COMMIT WORK" Informix can manage
the transaction across multiple databases and servers without having to
use XA transactions. Just process inserts, updates, and deletes to both
databases through a single connection as remote references. Example:
connect to database1@servername;
begin work;
update local_table1 set col1 = value1 where key1 = 123456;update database2@servername:remote_table1 set col3 = value3 where key =
54326;
commit work;
Since the second database resides in the same server as the connected
database the reference to "database2@servername" doesn't strictly require
the "@servername" reference, but I include it for completeness. Informix
will handle updating the tables in both database (whether in the same
server instance or different instances) including managing the possibility
that it will lose connectivity with the "remote" database's server using
two-phase commit protocols. This is stated in the Administrator's Guide on
page 1-8 and here is a Wiki reference describing the protocol:
https://en.wikipedia.org/wiki/Two-phase_commit_protocol
I hope this helps.
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 Sun, Aug 23, 2015 at 3:44 AM, Frank Langelage <frank@lafr.de> wrote:
> Art,
>
> could you explain more about this or reference a documentation?
> I never heard of it.
> But I'm bound to what application server supports.
>
> Frank
>
> On 11.08.15 23:45, Art Kagel 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
I created the output, attached as files.
The server is both, it's the only one involved in this case.
Connections to 2 databases on the same Informix instance.
On 11.08.15 23:55, david@smooth1.co.uk wrote:
> 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.
>
>
IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 2 days
10:31:56 -- 1802240 Kbytes
Conditions with waiters:
cid addr name waiter waittime
267 1158d95d8 ReadAhead 25 2186
2421 10a2ddc30 bp_cond 76 117617
11961 1193e5ad8 netnorm 11187 21
14275 1198ef658 netnorm 11056 124
15138 119ac04a8 defunct 3407 152204
18641 118a41268 netnorm 11014 36
18805 118a80028 netnorm 11188 24
19299 11fffe7d8 netnorm 11055 124
21163 11ecd2bf8 netnorm 10993 124
21299 11ec5e9b8 netnorm 11016 26
21356 11ec28a48 netnorm 10994 124
IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 2 days
10:31:56 -- 1802240 Kbytes
Locked mutexes:
mid addr name holder lkcnt waiter waittime
Number of mutexes on VP 1 free list: 296
Number of mutexes on VP 2 free list: 0
Number of mutexes on VP 3 free list: 0
Number of mutexes on VP 4 free list: 0
Number of mutexes on VP 5 free list: 0
Number of mutexes on VP 6 free list: 0
Number of mutexes on VP 7 free list
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
Okay, the attached files where included into the mail text.
They are also available from http://langfr.homeunix.net/tmp/...
-rw-r--r-- 1 www root 883 Aug 23 11:22 onstat_g_con.txt
-rw-r--r-- 1 www root 657 Aug 23 11:22 onstat_g_lmx.txt
-rw-r--r-- 1 www root 2097 Aug 23 11:22 onstat_g_ses.txt
-rw-r--r-- 1 www root 1850 Aug 23 11:22 onstat_g_stk.txt
-rw-r--r-- 1 www root 193 Aug 23 11:22 onstat_s.txt
-rw-r--r-- 1 www root 4585 Aug 23 11:22 onstat_x.txt
On 23.08.15 11:16, Frank Langelage wrote:
> I created the output, attached as files.
>
> The server is both, it's the only one involved in this case.
> Connections to 2 databases on the same Informix instance.
>
> On 11.08.15 23:55, david@smooth1.co.uk wrote:
>> 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.
>>
>>
> IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 2 days
> 10:31:56 -- 1802240 Kbytes>
> Conditions with waiters:
> cid addr name waiter waittime
> 267 1158d95d8 ReadAhead 25 2186
> 2421 10a2ddc30 bp_cond 76 117617
> 11961 1193e5ad8 netnorm 11187 21
> 14275 1198ef658 netnorm 11056 124
> 15138 119ac04a
Thanks Art.
But as I am using an Java Enterprise Edition application server I do not
have this level of control over the statements and the connections used.
The aplication server handles the connections and transactions.
Different database, different connection.
Frank
On 23.08.15 11:11, Art Kagel wrote:
> Frank:
>
> If you just use the SQL "BEGIN WORK" and "COMMIT WORK" Informix can manage
> the transaction across multiple databases and servers without having to
> use XA transactions. Just process inserts, updates, and deletes to both
> databases through a single connection as remote references. Example:
>
> connect to database1@servername;
> begin work;
> update local_table1 set col1 = value1 where key1 = 123456;> update database2@servername:remote_table1 set col3 = value3 where key =
> 54326;
> commit work;
>
> Since the second database resides in the same server as the connected
> database the reference to "database2@servername" doesn't strictly require
> the "@servername" reference, but I include it for completeness. Informix
> will handle updating the tables in both database (whether in the same
> server instance or different instances) including managing the possibility
> that it will lose connectivity with the "remote" database's server using
> two-phase commit protocols. This is stated in the Administrator's Guide on
> page 1-8 and here is a Wiki reference describing the protocol:
> https://en.wikipedia.org/wiki/Two-phase_commit_protocol
>
> I hope this helps.
>
> 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 Sun, Aug 23, 2015 at 3:44 AM, Frank Langelage <frank@lafr.de> wrote:
>
>> Art,
>>
>> could you explain more about this or reference a documentation?
>> I never heard of it.
>> But I'm bound to what application server supports.
>>
>> Frank
>>
>> On 11.08.15 23:45, Art Kagel 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
>>>>
Then create local synonyms for each of the tables you need to modify in the
other database so you can use local SQL in a single database. There's more
than one way to skin a cat.
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 Sun, Aug 23, 2015 at 7:36 AM, Frank Langelage <frank@lafr.de> wrote:
> Thanks Art.
> But as I am using an Java Enterprise Edition application server I do not
> have this level of control over the statements and the connections used.
> The aplication server handles the connections and transactions.
> Different database, different connection.
>
> Frank
>
> On 23.08.15 11:11, Art Kagel wrote:
> > Frank:
> >
> > If you just use the SQL "BEGIN WORK" and "COMMIT WORK" Informix can
> manage
> > the transaction across multiple databases and servers without having to
> > use XA transactions. Just process inserts, updates, and deletes to both
> > databases through a single connection as remote references. Example:
> >
> > connect to database1@servername;
> > begin work;
> > update local_table1 set col1 = value1 where key1 = 123456;> > update database2@servername:remote_table1 set col3 = value3 where key =
> > 54326;
> > commit work;
> >
> > Since the second database resides in the same server as the connected
> > database the reference to "database2@servername" doesn't strictly
> require
> > the "@servername" reference, but I include it for completeness. Informix
> > will handle updating the tables in both database (whether in the same
> > server instance or different instances) including managing the
> possibility
> > that it will lose connectivity with the "remote" database's server using
> > two-phase commit protocols. This is stated in the Administrator's Guide
> on
> > page 1-8 and here is a Wiki reference describing the protocol:
> > https://en.wikipedia.org/wiki/Two-phase_commit_protocol
> >
> > I hope this helps.
> >
> > 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 Sun, Aug 23, 2015 at 3:44 AM, Frank Langelage <frank@lafr.de> wrote:
> >
> >> Art,
> >>
> >> could you explain more about this or reference a documentation?
> >> I never heard of it.
> >> But I'm bound to what application server supports.
> >>
> >> Frank
> >>
> >> On 11.08.15 23:45, Art Kagel 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