set lock mode to wait 5
A user reported ~3-minute logins in a Java/Hibernate/Spring app against Informix; SQL tracing showed the pair "set lock mode to wait 5" plus "select site from systables where tabname='GL_COLLATE'" repeated about 40 times before the app's own queries ran. Art Kagel explained the GL_COLLATE select is normal connection/collation processing and the lock-mode statement comes from the app or a sysdbopen(), suggesting the app opens a 40-connection pool at login; he also noted TCP connections are dynamic so NETTYPE wasn't the limit. Marcus Haarmann pointed to a datasource "new/check connection SQL" setting in the Hibernate/container config. The poster said he would check these, but no confirmed resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello guys!!
Again I have questions about informix.
I have a application java and uses database in informix. Users login in the
app across query table in database but the loggin is very slow (Maybe 3
minutes). I enabled sqltrace informix and i verified that there are queries
that appears in my java code but there are about 40 statements following:
set lock mode to wait 5
select site from informix.systables where tabname ='GL_COLLATE'
After that, other time appear my queries. Then, login success.
I think that "set lock mode to wait 5" x 40 = 200 seconds cause waiting.
Do you have any idea about this?
Do you think that the problem is informix or my app?
Thank you!!!
The "set lock mode" statement is either from your application, from this
user's sysdbopen() function, or from the generic sysdbopen() function (ie
owned by user informix). It sounds like you are saying that these two
statements are repeated 40 times. Is that right? If so, it may be that
your Java app is always starting a connection pool of 40 connections before
returning from the connect() method. Check that out.
The set lock mode is a good thing, BTW, but not automatic. The "select site
..." is part of a login processing to determine the collating language of
the database so that can be compared to the session's language for
character set mapping.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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, Jan 21, 2014 at 10:01 PM, LICU GARCIA <licu99@gmail.com> wrote:
> Hello guys!!
>
> Again I have questions about informix.
>
> I have a application java and uses database in informix. Users login in the
> app across query table in database but the loggin is very slow (Maybe 3
> minutes). I enabled sqltrace informix and i verified that there are queries
> that appears in my java code but there are about 40 statements following:
>
> set lock mode to wait 5
> select site from informix.systables where tabname ='GL_COLLATE'>
> After that, other time appear my queries. Then, login success.
>
> I think that "set lock mode to wait 5" x 40 = 200 seconds cause waiting.
>
> Do you have any idea about this?
> Do you think that the problem is informix or my app?
>
> Thank you!!!
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7b3a887289ce3704f0867849
1- which platform is running the database
2- Does your application use some kind of framework like Hibernate?
3- In which isolation level does your application use?
4- If you take the client IP address (let's assume it's 192.168.1.1) how
long does this command take to run on the db server?:
nslookup 192.168.1.1
Regards
Em 22/01/2014 03:02, "LICU GARCIA" <licu99@gmail.com> escreveu:
> Hello guys!!
>
> Again I have questions about informix.
>
> I have a application java and uses database in informix. Users login in the
> app across query table in database but the loggin is very slow (Maybe 3
> minutes). I enabled sqltrace informix and i verified that there are queries
> that appears in my java code but there are about 40 statements following:
>
> set lock mode to wait 5
> select site from informix.systables where tabname ='GL_COLLATE'>
> After that, other time appear my queries. Then, login success.
>
> I think that "set lock mode to wait 5" x 40 = 200 seconds cause waiting.
>
> Do you have any idea about this?
> Do you think that the problem is informix or my app?
>
> Thank you!!!
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c2dcb23651bd04f086a318
Thank you very much!!! 1- which platform is running the database Red Hat EL 6.1 2- Does your application use some kind of framework like Hibernate? Yes, Hibernate, Spring 3- In which isolation level does your application use? I don't know this for now. 4- If you take the client IP address (let's assume it's 192.168.1.1) how long does this command take to run on the db server?: Immediate
If we supposing that my application opens 40 connections to db and if i have 10 users, then I need 400 connections. if my configuration is NETTYPE soctcp,2,150,NET This isn't enough to cover connections for 10 users. This is correct? Thank you.
TCP connection types are dynamic, so the engine will expand the data structures and handle it. Each listener/poll thread can handle between 350 and 400 connections, so with 2 poll threads configured you should be good. Art Art S. Kagel, Principal Consultant ASK Database Management 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, Jan 21, 2014 at 11:14 PM, LICU GARCIA <licu99@gmail.com> wrote: > If we supposing that my application opens 40 connections to db and if i > have > 10 users, then I need 400 connections. > > if my configuration is NETTYPE soctcp,2,150,NET > > This isn't enough to cover connections for 10 users. > > This is correct? > > Thank you. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1137fb1a3d201404f0885a59
Hi, If you use Hibernate, then probably you have a datasource setup (XML, depending on your Application container, or in the hibernate configuration file), which has a "check connection string" or a "new connection sql string" defined. This one will be executed always. Probably either your framework or the application itself might do this special query before using a connection. We are also using Informix in such an environment, but the queries you have posted are not in any standard I have seen before, so I assume they are in the setup. Hope this helps. Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "LICU GARCIA" <licu99@gmail.com> An: ids@iiug.org Gesendet: Mittwoch, 22. Januar 2014 05:02:47 Betreff: Re: set lock mode to wait 5 [32283] Thank you very much!!! 1- which platform is running the database Red Hat EL 6.1 2- Does your application use some kind of framework like Hibernate? Yes, Hibernate, Spring 3- In which isolation level does your application use? I don't know this for now. 4- If you take the client IP address (let's assume it's 192.168.1.1) how long does this command take to run on the db server?: Immediate ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you!!! I'll try with this.
I'll check this, maybe the problem is in this configuration. Thank you!!!