Rogue user session
Hi,
Informix Dynamic Server Version 7.31.UD7, solaris 8
I've got a rogue user session connected to my instance that I need to
remove, if possible without bouncing the engine.
snippet frm onstat -u
4ef13618 T--P--- 32885750 userxyz console 539f5f5c 0 0 0
0
4ef1d618 T--P--- 32908171 userxyz console 539f5f5c 0 0 0
0
512f3318 T--P--- 32902685 userxyz console 539f5f5c 0 0 0
0
539c9818 Y--P--- 32885158 userxyz 29 52ccd890 0 3 202
129
basically, the user was running an SQL script to setup a database, the
script hung, so he killed (-9?) the process, this left the session
32885158, an onmode -z generated the session 32885750, trying it again
just created another session and an onmode -Z created another.
What can I do, short of bouncing the engine, to clear these sessions.
thanks
Hi Nog,
The sessions 32885750, 32908171 and 32902685 are all waiting for
transaction 539f5f5c and session 32885158 is waiting for condition
52ccd890 so post the output from
onstat -x|egrep -i"539f5f5c|4ef13618|4ef1d618|512f3318|512f3318"
onstat -
onstat -g con |grep -i 512f3318
onstat -g ses 32885158
to have a bit more information regarding what's going on. Also check
the online.log for Long Transactions.
RoB
Nog wrote:
> Hi,
>
> Informix Dynamic Server Version 7.31.UD7, solaris 8
>
> I've got a rogue user session connected to my instance that I need to
> remove, if possible without bouncing the engine.
>
> snippet frm onstat -u
>
> 4ef13618 T--P--- 32885750 userxyz console 539f5f5c 0 0 0
> 0
> 4ef1d618 T--P--- 32908171 userxyz console 539f5f5c 0 0 0
> 0
> 512f3318 T--P--- 32902685 userxyz console 539f5f5c 0 0 0
> 0
> 539c9818 Y--P--- 32885158 userxyz 29 52ccd890 0 3 202
> 129
>
> basically, the user was running an SQL script to setup a database, the
> script hung, so he killed (-9?) the process, this left the session
> 32885158, an onmode -z generated the session 32885750, trying it again
> just created another session and an onmode -Z created another.
>
> What can I do, short of bouncing the engine, to clear these sessions.
>
> thanks
RoB wrote:
> Hi Nog,
>
> The sessions 32885750, 32908171 and 32902685 are all waiting for
> transaction 539f5f5c and session 32885158 is waiting for condition
> 52ccd890 so post the output from
>
> onstat -x|egrep -i> "539f5f5c|4ef13618|4ef1d618|512f3318|512f3318"
> onstat -
> onstat -g con |grep -i 512f3318
> onstat -g ses 32885158>
> to have a bit more information regarding what's going on. Also check
> the online.log for Long Transactions.
>
> RoB
>
>
> Nog wrote:
> > Hi,
> >
> > Informix Dynamic Server Version 7.31.UD7, solaris 8
> >
> > I've got a rogue user session connected to my instance that I need to
> > remove, if possible without bouncing the engine.
> >
> > snippet frm onstat -u
> >
> > 4ef13618 T--P--- 32885750 userxyz console 539f5f5c 0 0 0
> > 0
> > 4ef1d618 T--P--- 32908171 userxyz console 539f5f5c 0 0 0
> > 0
> > 512f3318 T--P--- 32902685 userxyz console 539f5f5c 0 0 0
> > 0
> > 539c9818 Y--P--- 32885158 userxyz 29 52ccd890 0 3 202
> > 129
> >
> > basically, the user was running an SQL script to setup a database, the
> > script hung, so he killed (-9?) the process, this left the session
> > 32885158, an onmode -z generated the session 32885750, trying it again
> > just created another session and an onmode -Z created another.
> >
> > What can I do, short of bouncing the engine, to clear these sessions.
> >
> > thanks
okay, this is what I get,
onstat -x|egrep -i "539f5f5c|4ef13618|4ef1d618|512f3318|512f3318"
539f5f5c A---- 539c9818 3 0 COMMIT 0
onstat -
Informix Dynamic Server Version 7.31.UD7 -- On-Line --
onstat -g con |grep -i 512f3318
nothing
onstat -g ses 32885158
Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up .....
session #RSAM total used
id user tty pid hostname threads memory memory
32885158 userxyz 29 25185 aserver 1 344064 145456
tid name rstcb flags curstk status
33073102 sqlexec 539c9818 Y--P--- 6232 cond wait(netnorm)
Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
32885158 V 4f6e8018 344064 198608 820 40
name free used name free used
overhead 0 120 scb 0 96
opentable 0 5320 filetable 0 1376
ru 0 224 log 0 2152
temprec 0 6984 keys 0 504
ralloc 0 82872 gentcb 0 8568
ostcb 0 2496 sqscb 0
28424
rdahead 0 1600 hashfiletab 0 280
osenv 0 2288 sqtcb 0 1720
fragman 0 432
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
32885158 CREATE VIEW newdatabase CR Not Wait 0 0 7.31
Current SQL statement :
create view ....
there's nothing in the log file about long transactions and the onstat
-l is normal.
thanks again for having look
Nog wrote:
> RoB wrote:
> > Hi Nog,
> >
> > The sessions 32885750, 32908171 and 32902685 are all waiting for
> > transaction 539f5f5c and session 32885158 is waiting for condition
> > 52ccd890 so post the output from
> >
> > onstat -x|egrep -i> > "539f5f5c|4ef13618|4ef1d618|512f3318|512f3318"
> > onstat -
> > onstat -g con |grep -i 512f3318
> > onstat -g ses 32885158> >
> > to have a bit more information regarding what's going on. Also check
> > the online.log for Long Transactions.
> >
> > RoB
> >
> >
> > Nog wrote:
> > > Hi,
> > >
> > > Informix Dynamic Server Version 7.31.UD7, solaris 8
> > >
> > > I've got a rogue user session connected to my instance that I need to
> > > remove, if possible without bouncing the engine.
> > >
> > > snippet frm onstat -u
> > >
> > > 4ef13618 T--P--- 32885750 userxyz console 539f5f5c 0 0 0
> > > 0
> > > 4ef1d618 T--P--- 32908171 userxyz console 539f5f5c 0 0 0
> > > 0
> > > 512f3318 T--P--- 32902685 userxyz console 539f5f5c 0 0 0
> > > 0
> > > 539c9818 Y--P--- 32885158 userxyz 29 52ccd890 0 3 202
> > > 129
> > >
> > > basically, the user was running an SQL script to setup a database, the
> > > script hung, so he killed (-9?) the process, this left the session
> > > 32885158, an onmode -z generated the session 32885750, trying it again
> > > just created another session and an onmode -Z created another.
> > >
> > > What can I do, short of bouncing the engine, to clear these sessions.
> > >
> > > thanks
>
> okay, this is what I get,
>
> onstat -x|egrep -i "539f5f5c|4ef13618|4ef1d618|512f3318|512f3318">
> 539f5f5c A---- 539c9818 3 0 COMMIT 0
>
> onstat -
>
> Informix Dynamic Server Version 7.31.UD7 -- On-Line --
>
> onstat -g con |grep -i 512f3318>
> nothing
>
> onstat -g ses 32885158>
> Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up .....
>
> session #RSAM total used
>
> id user tty pid hostname threads memory memory
>
> 32885158 userxyz 29 25185 aserver 1 344064 145456
>
>
> tid name rstcb flags curstk status
> 33073102 sqlexec 539c9818 Y--P--- 6232 cond wait(netnorm)
>
> Memory pools count 1
> name class addr totalsize freesize #allocfrag #freefrag
> 32885158 V 4f6e8018 344064 198608 820 40
>
> name free used name free used
>
> overhead 0 120 scb 0 96
>
> opentable 0 5320 filetable 0 1376
>
> ru 0 224 log 0 2152
>
> temprec 0 6984 keys 0 504
>
> ralloc 0 82872 gentcb 0 8568
>
> ostcb 0 2496 sqscb 0
> 28424
> rdahead 0 1600 hashfiletab 0 280
>
> osenv 0 2288 sqtcb 0 1720
>
> fragman 0 432
>
> Sess SQL Current Iso Lock SQL ISAM F.E.
> Id Stmt type Database Lvl Mode ERR ERR Vers
> 32885158 CREATE VIEW newdatabase CR Not Wait 0 0 7.31
>
> Current SQL statement :
> create view ....
>
> there's nothing in the log file about long transactions and the onstat
> -l is normal.
>
> thanks again for having look
bump
>539c9818 Y--P--- 32885158 userxyz 29 52ccd890 0 3 202 129
The session is holding three locks, see what they are:
onstat -s|grep -i 52ccd890
onstat -k|grep -i 52ccd890
Check to see if any of the locks returned are being held by other
sessions. Compare any values of the wtlist column from onstat -k with
other user threads from onstat -u.
>onstat -g ses 32885158
>...>tid name rstcb flags curstk status
>33073102 sqlexec 539c9818 Y--P--- 6232 cond wait(netnorm)
>...
According to this output the session is still waiting for the client
application/user to send further instructions.
You can check for how long it has been waiting by issuing the command
"onstat -g con|grep 33073102"
a few times and the waittime column (the last one) will show you the
amount of seconds in this state.
If there are no other sessions holding any locks then I could only
recommend that you bounce the instance to get rid of the sessions.
It doesn't seem like normal behaviour for a session to get stuck while
creating a view, it should just get rolled back after being killed.
This looks like a product defect to me and you could take it up with
Tech Support but the quickest solution would most likelly be to bounce
the instance to clear out the sessions.
RoB
Nog wrote:
> RoB wrote:
> > Hi Nog,
> >
> > The sessions 32885750, 32908171 and 32902685 are all waiting for
> > transaction 539f5f5c and session 32885158 is waiting for condition
> > 52ccd890 so post the output from
> >
> > onstat -x|egrep -i> > "539f5f5c|4ef13618|4ef1d618|512f3318|512f3318"
> > onstat -
> > onstat -g con |grep -i 512f3318
> > onstat -g ses 32885158> >
> > to have a bit more information regarding what's going on. Also check
> > the online.log for Long Transactions.
> >
> > RoB
> >
> >
> > Nog wrote:
> > > Hi,
> > >
> > > Informix Dynamic Server Version 7.31.UD7, solaris 8
> > >
> > > I've got a rogue user session connected to my instance that I need to
> > > remove, if possible without bouncing the engine.
> > >
> > > snippet frm onstat -u
> > >
> > > 4ef13618 T--P--- 32885750 userxyz console 539f5f5c 0 0 0
> > > 0
> > > 4ef1d618 T--P--- 32908171 userxyz console 539f5f5c 0 0 0
> > > 0
> > > 512f3318 T--P--- 32902685 userxyz console 539f5f5c 0 0 0
> > > 0
> > > 539c9818 Y--P--- 32885158 userxyz 29 52ccd890 0 3 202
> > > 129
> > >
> > > basically, the user was running an SQL script to setup a database, the
> > > script hung, so he killed (-9?) the process, this left the session
> > > 32885158, an onmode -z generated the session 32885750, trying it again
> > > just created another session and an onmode -Z created another.
> > >
> > > What can I do, short of bouncing the engine, to clear these sessions.
> > >
> > > thanks
>
> okay, this is what I get,
>
> onstat -x|egrep -i "539f5f5c|4ef13618|4ef1d618|512f3318|512f3318">
> 539f5f5c A---- 539c9818 3 0 COMMIT 0
>
> onstat -
>
> Informix Dynamic Server Version 7.31.UD7 -- On-Line --
>
> onstat -g con |grep -i 512f3318>
> nothing
>
> onstat -g ses 32885158>
> Informix Dynamic Server Version 7.31.UD7 -- On-Line -- Up .....
>
> session #RSAM total used
>
> id user tty pid hostname threads memory memory
>
> 32885158 userxyz 29 25185 aserver 1 344064 145456
>
>
> tid name rstcb flags curstk status
> 33073102 sqlexec 539c9818 Y--P--- 6232 cond wait(netnorm)
>
> Memory pools count 1
> name class addr totalsize freesize #allocfrag #freefrag
> 32885158 V 4f6e8018 344064 198608 820 40
>
> name free used name free used
>
> overhead 0 120 scb 0 96
>
> opentable 0 5320 filetable 0 1376
>
> ru 0 224 log 0 2152
>
> temprec 0 6984 keys 0 504
>
> ralloc 0 82872 gentcb 0 8568
>
> ostcb 0 2496 sqscb 0
> 28424
> rdahead 0 1600 hashfiletab 0 280
>
> osenv 0 2288 sqtcb 0 1720
>
> fragman 0 432
>
> Sess SQL Current Iso Lock SQL ISAM F.E.
> Id Stmt type Database Lvl Mode ERR ERR Vers
> 32885158 CREATE VIEW newdatabase CR Not Wait 0 0 7.31
>
> Current SQL statement :
> create view ....
>
> there's nothing in the log file about long transactions and the onstat
> -l is normal.
>
> thanks again for having look