 |
IDS Forum
Re: Session "Waiting on a transaction"
Posted By: Frank Langelage
Date: Sunday, 23 August 2015, at 5:29 a.m.
In Response To: Re: Session "Waiting on a transaction" (Frank Langelage)
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 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: 0
> Number of mutexes on VP 8 free list: 0
> Number of mutexes on VP 9 free list: 0
> Number of mutexes on VP 10 free list: 0
>
> Total number of mutexes on VP free lists: 296
>
> IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 2 days
> 10:31:46 -- 1802240 Kbytes
>
> session effective #RSAM total used dynamic
> id user user tty pid hostname threads memory memory explain
> 6298 mbi - - 0 sb2000 1 110592 80992 off
>
> Program :
> /content/ifxjdbc_all.jarorg.jboss.threads/JBossThread
>
> tid name rstcb flags curstk status
> 11189 sqlexec 1147f5268 T--P--- 7519 sleeping forever-
>
> Memory pools count 2
> name class addr totalsize freesize #allocfrag #freefrag
> 6298 V 11813c040 106496 28832 108 15
> 6298*O0 V 118743040 4096 768 1 1
>
> name free used name free used
> overhead 0 6656 scb 0 144
> opentable 0 7032 filetable 0 1128
> log 0 16536 temprec 0 2208
> keys 0 208 ralloc 0 6984
> gentcb 0 1592 ostcb 0 2928
> sqscb 0 22416 sql 0 72
> hashfiletab 0 552 osenv 0 2264
> sqtcb 0 9416 fragman 0 624
> udr 0 232
>
> sqscb info
> scb sqscb optofc pdqpriority optcompind directives
> 1153c21c0 11ec6b028 0 0 2 1
>
> Sess SQL Current Iso Lock SQL ISAM F.E.
> Id Stmt type Database Lvl Mode ERR ERR Vers Explain
> 6298 - wildfly10 LCU Wait 10 0 0 9.28 Off
>
> Last parsed SQL statement :
> select dbinfo('version','full') from table (set{1});
>
> IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 2 days
> 10:31:55 -- 1802240 Kbytes
>
> Stack for thread: 11189 sqlexec
> base: 0x000000011e027000
> len: 69632
>
> pc: 0x000000010123d89c
> tos: 0x000000011e0362a1
> state: sleeping
>
> vp: 1
>
> 0x10123d89c oninit :: yield_processor_svp + 0x4f0 sp=0x11e036aa0(0x117fbd538,
> 0x101afc868, 0x101af5460, 0x101b05598, 0x101afa400, 0x114268028)
> 0x100b6998c oninit :: tx_txwait + 0x364 sp=0x11e036bd0
> delta_sp=304(0x1147f5268, 0x11482f9c0, 0x0, 0x101af5, 0x0, 0x101b05790)
> 0x100b68428 oninit :: isxattach_uid + 0x834 sp=0x11e036ca0
> delta_sp=208(0x11e037070, 0x1, 0x10a24d800, 0x1147f52bc, 0x101af5f80,
> 0x10a24d800)
> 0x100b67758 oninit :: xa_start + 0x668 sp=0x11e036d80
> delta_sp=224(0x11e037070, 0x8200000, 0xfffffffffffffffa, 0x11810c978,
> 0x101af5f80, 0x200000)
> 0x100713380 oninit :: xstartsub + 0x84 sp=0x11e036e50
> delta_sp=208(0x11e037070, 0x1, 0x200000, 0x23, 0x24, 0xfffffffffffffff7)
> 0x100715330 oninit :: ifx_common_xstart + 0xd0 sp=0x11e036f00
> delta_sp=176(0x11e037070, 0x1, 0x200000, 0x11635b4f8, 0x1, 0x200000)
> 0x1007132e8 oninit :: sq_xstart + 0x44 sp=0x11e036fb0
> delta_sp=176(0x11810c348, 0x101af5000, 0x1, 0x200000, 0x1, 0x11e037070)
> 0x10078a490 oninit :: sqmain + 0x930 sp=0x11e037100 delta_sp=336(0x48, 0x0,
> 0x10181cef0, 0x1, 0x20000, 0x101af5fa0)
> 0x1013c37e4 oninit :: listen_verify + 0x48c sp=0x11e0371d0
> delta_sp=208(0x1152be380, 0x101800, 0x10181cef0, 0x1153c21c0, 0x12c, 0x0)
> 0x1013c2fac oninit :: spawn_thread + 0x15a4 sp=0x11e037670
> delta_sp=1184(0x1149645b8, 0x0, 0x101800, 0x114965028, 0x0, 0x880d80)
> 0x10129b950 oninit :: th_init_initgls + 0x170 sp=0x11e037d10
> delta_sp=1696(0x10181c, 0x101800, 0x1013c1a08, 0x101b05000, 0x117fbd538,
> 0x11810c0c0)
> 0x10125cca4 oninit :: startup + 0x1d0 sp=0x11e037e50 delta_sp=320(0xa,
> 0x101afb560, 0x101b05598, 0x7, 0x101af5460, 0x101b05598)
>
> IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 2 days
> 10:31:56 -- 1802240 Kbytes
>
> Latches with lock or userthread set
> name address lock wait userthread
>
> IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 2 days
> 10:31:46 -- 1802240 Kbytes
>
> Transactions
>
> est.
> address flags userthread locks begin_logpos current logpos isol rb_time retrys
> coord
> 114829028 A---- 1147e2028 0 - - COMMIT - 0
> 114829390 A---- 1147e28e8 0 - - COMMIT - 0
> 1148296f8 A---- 1147e31a8 0 - - COMMIT - 0
> 114829a60 A---- 1147e3a68 0 - - COMMIT - 0
> 114829dc8 A---- 1147e4328 0 - - COMMIT - 0
> 11482a130 A---- 1147e4be8 0 - - COMMIT - 0
> 11482a498 A---- 1147e54a8 0 - - COMMIT - 0
> 11482a800 A---- 1147e5d68 0 - - COMMIT - 0
> 11482ab68 A---- 1147e6628 0 - - COMMIT - 0
> 11482aed0 A---- 1147e6ee8 0 - - COMMIT - 0
> 11482b238 A---- 1147e77a8 0 - - COMMIT - 0
> 11482b5a0 A---- 1147e8068 0 - - DIRTY - 0
> 11482b908 A---- 1147e8928 0 - - COMMIT - 0
> 11482bfd8 A---- 1147f03a8 0 - - NOTRANS - 0
> 11482c340 A---- 1147ee968 1 - - DIRTY - 0
> 11482d7b0 A---- 1147f03a8 0 - - COMMIT - 0
> 11482db18 A---- 1147efae8 1 - - DIRTY - 0
> 11482de80 A---- 1147ebda8 0 - - COMMIT - 0
> 11482e1e8 A---- 1147f0c68 2 - - DIRTY - 0
> 11482e8b8 A---- 1147eb4e8 0 - - COMMIT - 0
> 11482ec20 A---- 1147e9aa8 0 - - COMMIT - 0
> 11482f2f0 A---- 1147ee0a8 2 - - LC - 0
> 11482f9c0 AL--G 1147ec668 0 - - LC - 0
> 11482fd28 A---- 1147ee968 0 - - NOTRANS - 0
> 114830090 A---- 1147ed7e8 1 - - COMMIT - 0
> 1148303f8 A---- 1147eac28 0 - - COMMIT - 0
> 114830ac8 A---- 1147ea368 0 - - NOTRANS - 0
> 114830e30 A---- 1147f1528 2 - - LC - 0
> 114831198 A---- 1147efae8 0 - - NOTRANS - 0
> 1148322a0 A---- 1147f49a8 1 - - DIRTY - 0
> 114832608 A---- 1147ea368 2 - - LC - 0
> 114832970 A---- 1147f40e8 1 - - DIRTY - 0
> 114833040 A---- 1147f03a8 1 - - DIRTY - 0
> 114834b80 A---- 1147ef228 2 - - DIRTY - 0
> 114834ee8 A---- 1147f5268 2 - - LC - 0
> 1148355b8 A---- 1147ef228 0 - - NOTRANS - 0
> 114835920 A---- 1147ec668 2 - - LC - 0
> 114835ff0 A---- 1147ecf28 0 - - COMMIT - 0
> 114836358 A---- 1147e91e8 2 - - LC - 0
> 39 active, 128 total, 63 maximum concurrent
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
