lock / syssequences
Hi,
IFX 11.50 FC8 / AIX 6.1
4GL 7.32 FC2
Today appear on the error log of our application a lock error over
syssequences table.
We use a lot of sequences here... from 4GL system and web system (java)
See bellow...
code 4gL:
SELECT my_sq.nextval INTO p_my.seq FROM DUAL
# dual is a table just like dual from oracle or sysmaster:sysdual
4gl Error log:
Date: 24/08/2011 Time: 10:15:40
Program error at "xyz.4gl", line number 528.
SQL statement error number -211.
Cannot read system catalog (syssequences).
SYSTEM error number -107.
ISAM error: record is locked.
I don't expected never see lock errors over the syssequences and start afraid
that this goes to occur frequently.
Anyone have experience with this behave?
We don't treat problem of lock concurrency over sequence on our code, if this
behave become be frequently, will be a big problem...
Regards
Cesar
Hi,
is there an update statistics running in parallel ?
We use sequences (from java mainly) by selecting select seq_xxx.nextval
from systables where tabid = 1.
Never had any problems with that.
How is this dual table defined in your environment ? Any foreign keys to
other tables ?
Best regards,
Marcus
-----Original Message-----
From: Cesar Inacio Martins [mailto:cesar_inacio_martins@yahoo.com.br]
Sent: Wednesday, August 24, 2011 3:41 PM
To: ids@iiug.org
Subject: lock / syssequences [24692]
Hi,
IFX 11.50 FC8 / AIX 6.1
4GL 7.32 FC2
Today appear on the error log of our application a lock error over
syssequences table.
We use a lot of sequences here... from 4GL system and web system (java)
See bellow...
code 4gL:
SELECT my_sq.nextval INTO p_my.seq FROM DUAL
# dual is a table just like dual from oracle or sysmaster:sysdual
4gl Error log:
Date: 24/08/2011 Time: 10:15:40
Program error at "xyz.4gl", line number 528.
SQL statement error number -211.
Cannot read system catalog (syssequences).
SYSTEM error number -107.
ISAM error: record is locked.
I don't expected never see lock errors over the syssequences and start
afraid that this goes to occur frequently.
Anyone have experience with this behave?
We don't treat problem of lock concurrency over sequence on our code, if
this behave become be frequently, will be a big problem...
Regards
Cesar
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Marcus,
no update statistics running.. we run it only at sundays
(AUS is deactived)
this dual is a dummy table , very simple :
{ TABLE "informix".dual row size = 1 number of columns = 1 index size = 0 }
create table "informix".dual
(
dummy char(1)
);
with 1 row... "fixed" ...
no FK...
________________________________
De: Marcus Haarmann <marcus.haarmann@midoco.de>
Para: ids@iiug.org
Enviadas: Quarta-feira, 24 de Agosto de 2011 10:53
Assunto: RE: lock / syssequences [24693]
Hi,
is there an update statistics running in parallel ?
We use sequences (from java mainly) by selecting select seq_xxx.nextval
from systables where tabid = 1.
Never had any problems with that.
How is this dual table defined in your environment ? Any foreign keys to
other tables ?
Best regards,
Marcus
-----Original Message-----
From: Cesar Inacio Martins [mailto:cesar_inacio_martins@yahoo.com.br]
Sent: Wednesday, August 24, 2011 3:41 PM
To: ids@iiug.org
Subject: lock / syssequences [24692]
Hi,
IFX 11.50 FC8 / AIX 6.1
4GL 7.32 FC2
Today appear on the error log of our application a lock error over
syssequences table.
We use a lot of sequences here... from 4GL system and web system (java)
See bellow...
code 4gL:
SELECT my_sq.nextval INTO p_my.seq FROM DUAL
# dual is a table just like dual from oracle or sysmaster:sysdual
4gl Error log:
Date: 24/08/2011 Time: 10:15:40
Program error at "xyz.4gl", line number 528.
SQL statement error number -211.
Cannot read system catalog (syssequences).
SYSTEM error number -107.
ISAM error: record is locked.
I don't expected never see lock errors over the syssequences and start
afraid that this goes to occur frequently.
Anyone have experience with this behave?
We don't treat problem of lock concurrency over sequence on our code, if
this behave become be frequently, will be a big problem...
Regards
Cesar
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
In order to improve performance I would suggestion you drop this table and
create the following synonym. selecting from this table will always avoid
locking
and is a memory resident table.
CREATE SYNONYM dual FOR sysmaster:sysdual;
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 08/24/2011 07:49:49 AM:
> From: "Cesar Inacio Martins" <cesar_inacio_martins@yahoo.com.br>
> To: ids@iiug.org
> Date: 08/24/2011 07:53 AM
> Subject: Re: lock / syssequences [24696]
> Sent by: ids-bounces@iiug.org
>
> Hi Marcus,
>
> no update statistics running.. we run it only at sundays
>
> (AUS is deactived)
>
> this dual is a dummy table , very simple :
> { TABLE "informix".dual row size = 1 number of columns = 1 index size =
0 }
> create table "informix".dual
> (
>
> dummy char(1)
> );
>
> with 1 row... "fixed" ...
>
> no FK...
>
> ________________________________
> De: Marcus Haarmann <marcus.haarmann@midoco.de>
> Para: ids@iiug.org
> Enviadas: Quarta-feira, 24 de Agosto de 2011 10:53
> Assunto: RE: lock / syssequences [24693]
>
> Hi,
>
> is there an update statistics running in parallel ?
> We use sequences (from java mainly) by selecting select seq_xxx.nextval
> from systables where tabid = 1.
> Never had any problems with that.
> How is this dual table defined in your environment ? Any foreign keys to
> other tables ?
>
> Best regards,
>
> Marcus
>
> -----Original Message-----
> From: Cesar Inacio Martins [mailto:cesar_inacio_martins@yahoo.com.br]
> Sent: Wednesday, August 24, 2011 3:41 PM
> To: ids@iiug.org
> Subject: lock / syssequences [24692]
>
> Hi,
>
> IFX 11.50 FC8 / AIX 6.1
> 4GL 7.32 FC2
>
> Today appear on the error log of our application a lock error over
> syssequences table.
> We use a lot of sequences here... from 4GL system and web system (java)
>
> See bellow...
>
> code 4gL:
> SELECT my_sq.nextval INTO p_my.seq FROM DUAL
>
> # dual is a table just like dual from oracle or sysmaster:sysdual
>
> 4gl Error log:
> Date: 24/08/2011 Time: 10:15:40
> Program error at "xyz.4gl", line number 528.
> SQL statement error number -211.
> Cannot read system catalog (syssequences).
> SYSTEM error number -107.
> ISAM error: record is locked.>
> I don't expected never see lock errors over the syssequences and start
> afraid that this goes to occur frequently.
>
> Anyone have experience with this behave?
> We don't treat problem of lock concurrency over sequence on our code, if
> this behave become be frequently, will be a big problem...
>
> Regards
> Cesar
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi John,
Thanks for your answer.
So, what you saying is , because of the "slowness" of the access over
the "dual" physical table, some lock contention could be happen into the
syssequences , since have a nextval on the select?
Just for curiosity, you have any suggestion what you consider best way
get the value of the sequence where we can avoid any risk of lock
contention? (4gl code and java code)
4gl: using sql/end sql block?
Regards
Cesar
On 24/8/2011 16:14, John Miller iii wrote:
> In order to improve performance I would suggestion you drop this table and
> create the following synonym. selecting from this table will always avoid
> locking
> and is a memory resident table.
>
> CREATE SYNONYM dual FOR sysmaster:sysdual;>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 08/24/2011 07:49:49 AM:
>
>> From: "Cesar Inacio Martins"<cesar_inacio_martins@yahoo.com.br>
>> To: ids@iiug.org
>> Date: 08/24/2011 07:53 AM
>> Subject: Re: lock / syssequences [24696]
>> Sent by: ids-bounces@iiug.org
>>
>> Hi Marcus,
>>
>> no update statistics running.. we run it only at sundays
>>
>> (AUS is deactived)
>>
>> this dual is a dummy table , very simple :
>> { TABLE "informix".dual row size = 1 number of columns = 1 index size =
> 0 }
>> create table "informix".dual
>> (
>>
>> dummy char(1)
>> );
>>
>> with 1 row... "fixed" ...
>>
>> no FK...
>>
>> ________________________________
>> De: Marcus Haarmann<marcus.haarmann@midoco.de>
>> Para: ids@iiug.org
>> Enviadas: Quarta-feira, 24 de Agosto de 2011 10:53
>> Assunto: RE: lock / syssequences [24693]
>>
>> Hi,
>>
>> is there an update statistics running in parallel ?
>> We use sequences (from java mainly) by selecting select seq_xxx.nextval
>> from systables where tabid = 1.
>> Never had any problems with that.
>> How is this dual table defined in your environment ? Any foreign keys to
>> other tables ?
>>
>> Best regards,
>>
>> Marcus
>>
>> -----Original Message-----
>> From: Cesar Inacio Martins [mailto:cesar_inacio_martins@yahoo.com.br]
>> Sent: Wednesday, August 24, 2011 3:41 PM
>> To: ids@iiug.org
>> Subject: lock / syssequences [24692]
>>
>> Hi,
>>
>> IFX 11.50 FC8 / AIX 6.1
>> 4GL 7.32 FC2
>>
>> Today appear on the error log of our application a lock error over
>> syssequences table.
>> We use a lot of sequences here... from 4GL system and web system (java)
>>
>> See bellow...
>>
>> code 4gL:
>> SELECT my_sq.nextval INTO p_my.seq FROM DUAL
>>
>> # dual is a table just like dual from oracle or sysmaster:sysdual
>>
>> 4gl Error log:
>> Date: 24/08/2011 Time: 10:15:40
>> Program error at "xyz.4gl", line number 528.
>> SQL statement error number -211.
>> Cannot read system catalog (syssequences).
>> SYSTEM error number -107.
>> ISAM error: record is locked.>>
>> I don't expected never see lock errors over the syssequences and start
>> afraid that this goes to occur frequently.
>>
>> Anyone have experience with this behave?
>> We don't treat problem of lock concurrency over sequence on our code, if
>> this behave become be frequently, will be a big problem...
>>
>> Regards
>> Cesar
>>
>> ************************************************************************
>> *******
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>>
>
*******************************************************************************
>
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>>
>
*******************************************************************************
>
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>