SELECT using ISOLATION DIRTY READ some records dis
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
I found a strange behavior on IDS 10.00.FC4, or something I can't understand.
Sometimes with no explanation, using isolation "DIRTY READ", some records
simply disappear, to workaround I unload all data, drop the table, create the
table and load data again and the problem is solved. after some days or weeks,
the problem come up again.
It's been happening for three times in a table called connectdirect and it's
happening at this time.
for example:
set isolation committed read;
select count(*) from connectdirect;
select count(*) from connectdirect where 1 = 1;
set isolation dirty read;
select count(*) from connectdirect where 1 = 1;
(count(*))
3054484 (isolation committed read, but it doesn't matter)
(count(*))
3054484 (isolation committed read)
(count(*))
2859951 (isolation dirty read)
in this case 3,054,484 - 2,859,951 = 194,535 records disappeared (6.37%
records)
if setting Isolation dirty read, the engine works as some records really
doesn't exist. If you read an specific record (in the range of that 194,535 ),
the Informix returns " No rows found." setting isolation committed read the
engine shows the record. setting isolation dirty read again the record
disappear again.
until June 12, 2007 it happened only on this table connectdirect, at least we
could point it out, but in Jun 12 we found this problem in another 2 tables in
another database but in the same instance.
--------------------------------------------------------------------------------
---------------------
the table t_retornodet
select count(*) from t_retornodet where 1 = 1;
set isolation committed read: 392392037
set isolation dirty read: 390127664difference 2,264,373 records
It has been fixed using the workaround I told before
--------------------------------------------------------------------------------
---------------------
the table t_retdetcrc
select count(*) from t_retdetcrc where 1 = 1;
set isolation committed read: 136712462
set isolation dirty read: 136441694difference 270,768 records
It has been fixed using the workaround I told before
anybody has seen this before? any ideas?
thanks
Celso Coimbra
On 13/06/07, Celso Cabral Coimbra <ccoimbra@cleartech.com.br> wrote:
> Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office"
> />
>
> I found a strange behavior on IDS 10.00.FC4, or something I can't understand.
>
> Sometimes with no explanation, using isolation "DIRTY READ", some records
> simply disappear, to workaround I unload all data, drop the table, create the
> table and load data again and the problem is solved. after some days or
weeks,
> the problem come up again.
>
> It's been happening for three times in a table called connectdirect and it's
> happening at this time.
>
> for example:
>
> set isolation committed read;>
> select count(*) from connectdirect;
> select count(*) from connectdirect where 1 = 1;
> set isolation dirty read;
> select count(*) from connectdirect where 1 = 1;>
> (count(*))
>
> 3054484 (isolation committed read, but it doesn't matter)
>
> (count(*))
>
> 3054484 (isolation committed read)
>
> (count(*))
>
> 2859951 (isolation dirty read)
>
> in this case 3,054,484 - 2,859,951 = 194,535 records disappeared (6.37%
> records)
>
> if setting Isolation dirty read, the engine works as some records really
> doesn't exist. If you read an specific record (in the range of that 194,535
),
> the Informix returns " No rows found." setting isolation committed read the
> engine shows the record. setting isolation dirty read again the record
> disappear again.
>
> until June 12, 2007 it happened only on this table connectdirect, at least we
> could point it out, but in Jun 12 we found this problem in another 2 tables
in
> another database but in the same instance.
>
>
>
--------------------------------------------------------------------------------
---------------------
>
> the table t_retornodet
>
> select count(*) from t_retornodet where 1 = 1;>
> set isolation committed read: 392392037>
> set isolation dirty read: 390127664> difference 2,264,373 records
>
> It has been fixed using the workaround I told before
>
>
>
--------------------------------------------------------------------------------
---------------------
>
> the table t_retdetcrc
>
> select count(*) from t_retdetcrc where 1 = 1;>
> set isolation committed read: 136712462>
> set isolation dirty read: 136441694> difference 270,768 records
>
> It has been fixed using the workaround I told before
>
> anybody has seen this before? any ideas?
>
> thanks
>
> Celso Coimbra
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Celso
The SET ISOLATION isn't 'loosing' records, the query just can't access
them because they are locked by another process and you have told the
engine to only access unlocked records. If you SET LOCK MODE TO WAIT
you will get an accurate count, however why do you want to force a
serial read of one of your indexes just to get a record count (by
using 1 = 1) when a straight COUNT(*) FROM will give same answer every
time regardless of lock mode.
No issue here and save time and effort and risk by leaving your data
in the engine where it belongs.
Keith
Keith,
you didn't understand.
The example I did using where 1=1 was just to read effectively the whole table
counting all records, without using "where 1=1" the engine pick the amount of
records in memory and the number will be always the same.
If I find out the records that don't show in isolation dirty read, reading it
in isolation dirty read as if it doesn't exist, trying again in committed
read, you can see it exists.
If any records was locked, the problem would happen in commited read, instead
in isolation dirty read. using isolation dirty read the record that was
locked, would be showed as it was (dirty).
In my example, there are some records which don't exist in the table, i never
can find those records with isolation dirty read, even if I lock the table in
exclusive mode and try to read.
Thanks,
Celso Coimbra
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de Keith
Simmons
Enviada em: quarta-feira, 13 de junho de 2007 12:59
Para: ids@iiug.org
Assunto: Re: SELECT using ISOLATION DIRTY READ some rec.... [9352]
On 13/06/07, Celso Cabral Coimbra <ccoimbra@cleartech.com.br> wrote:
> Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office"
> />
>
> I found a strange behavior on IDS 10.00.FC4, or something I can't
understand.
>
> Sometimes with no explanation, using isolation "DIRTY READ", some records
> simply disappear, to workaround I unload all data, drop the table, create
the
> table and load data again and the problem is solved. after some days or
weeks,
> the problem come up again.
>
> It's been happening for three times in a table called connectdirect and it's
> happening at this time.
>
> for example:
>
> set isolation committed read;>
> select count(*) from connectdirect;
> select count(*) from connectdirect where 1 = 1;
> set isolation dirty read;
> select count(*) from connectdirect where 1 = 1;>
> (count(*))
>
> 3054484 (isolation committed read, but it doesn't matter)
>
> (count(*))
>
> 3054484 (isolation committed read)
>
> (count(*))
>
> 2859951 (isolation dirty read)
>
> in this case 3,054,484 - 2,859,951 = 194,535 records disappeared (6.37%
> records)
>
> if setting Isolation dirty read, the engine works as some records really
> doesn't exist. If you read an specific record (in the range of that 194,535
),
> the Informix returns " No rows found." setting isolation committed read the
> engine shows the record. setting isolation dirty read again the record
> disappear again.
>
> until June 12, 2007 it happened only on this table connectdirect, at least
we
> could point it out, but in Jun 12 we found this problem in another 2 tables
in
> another database but in the same instance.
>
>
>
--------------------------------------------------------------------------------
---------------------
>
> the table t_retornodet
>
> select count(*) from t_retornodet where 1 = 1;>
> set isolation committed read: 392392037>
> set isolation dirty read: 390127664> difference 2,264,373 records
>
> It has been fixed using the workaround I told before
>
>
>
--------------------------------------------------------------------------------
---------------------
>
> the table t_retdetcrc
>
> select count(*) from t_retdetcrc where 1 = 1;>
> set isolation committed read: 136712462>
> set isolation dirty read: 136441694> difference 270,768 records
>
> It has been fixed using the workaround I told before
>
> anybody has seen this before? any ideas?
>
> thanks
>
> Celso Coimbra
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Celso
The SET ISOLATION isn't 'loosing' records, the query just can't access
them because they are locked by another process and you have told the
engine to only access unlocked records. If you SET LOCK MODE TO WAIT
you will get an accurate count, however why do you want to force a
serial read of one of your indexes just to get a record count (by
using 1 = 1) when a straight COUNT(*) FROM will give same answer every
time regardless of lock mode.
No issue here and save time and effort and risk by leaving your data
in the engine where it belongs.
Keith
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.