False non-exclusive access when select a table
A table on IDS 11.70.FC8W1 (HP-UX) returned -242/-106 'non-exclusive access' on every SELECT and on oncheck, yet could still be renamed, and no locks or open cursors showed up (onstat -k, onstat -g opn on the table's and indexes' partnums found nothing; no triggers). Suggestions pointed to a known defect where rolling back a transaction containing several ALTER TABLE statements leaves bad state in memory (APARs IC78035, and IT03643 which matches this version). Restarting the instance cleared the problem, which was the only workaround offered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
My environment : Informix 11.70.FC8W1 64 bits on HP-UX 11.31 Itanium.
I have a table with no access :
select * from transfbod;
242: Could not open database table (xsic.transfbod).
106: ISAM error: non-exclusive access.
However, it's possible rename the table:
rename table transfbod to newtable;
Table renamed.
After the table is renamed:
206: The specified table (transfbod) is not in the database.
111: ISAM error: no record found.
select * from newtable;
242: Could not open database table (xsic.newtable).
106: ISAM error: non-exclusive access.
The oncheck command shows the same error:
oncheck -ci sifco:xsic.transfbod
Validating indexes for sifco:xsic.transfbod...
oncheck failure: openrel()
ISAM error: non-exclusive access.
I can't find the culprit session. onstat -k doesn't show any lock.
It is the unique case, the select on the other tables works fine.
Anybody knows the reason ? Is there any known bug ?
Thanks in advance.
onstat -g opn is your friend. Search this for your table's partnum=20(in lower case hex, without 0x) and you'll find the culprit thread address =
which you can map to a session ID using onstat -u.
Probably a cursor still open on your table, in dirty read isolation.
HTH,
Andreas
From: "ROGER VILCA" <rvilca@luzdelsur.com.pe>
To: ids@iiug.org
Date: 26.04.2016 23:32
Subject: False non-exclusive access when select a table [37041]
Sent by: ids-bounces@iiug.org
Hi,=20
My environment : Informix 11.70.FC8W1 64 bits on HP-UX 11.31 Itanium.=20
I have a table with no access :=20
select * from transfbod;=20
242: Could not open database table (xsic.transfbod).=20
106: ISAM error: non-exclusive access.=20
However, it's possible rename the table:=20
rename table transfbod to newtable;=20
Table renamed.=20
After the table is renamed:=20
206: The specified table (transfbod) is not in the database.=20
111: ISAM error: no record found.=20
select * from newtable;=20
242: Could not open database table (xsic.newtable).=20
106: ISAM error: non-exclusive access.=20
The oncheck command shows the same error:=20
oncheck -ci sifco:xsic.transfbod=20
Validating indexes for sifco:xsic.transfbod...=20
oncheck failure: openrel()=20
ISAM error: non-exclusive access.=20
I can't find the culprit session. onstat -k doesn't show any lock.=20
It is the unique case, the select on the other tables works fine.=20
Anybody knows the reason ? Is there any known bug ?=20
Thanks in advance.=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Hi,
The table is not opened by any session.
>echo "select hex(partnum) from systables where tabname = 'transfbod'" |
dbaccess base
Database selected.
(expression)
0x00400720
1 row(s) retrieved.
Database closed.
>onstat -g opn | grep -i 400720
doesn't show nothing.
Does the table have any SELECT trigger? If yes, what is it doing?
On Tue, Apr 26, 2016 at 10:50 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>
wrote:
> Hi,
> The table is not opened by any session.
>
> >echo "select hex(partnum) from systables where tabname = 'transfbod'" |
> dbaccess base
>
> Database selected.
>
> (expression)
>
> 0x00400720
>
> 1 row(s) retrieved.
>
> Database closed.
>
> >onstat -g opn | grep -i 400720>
> doesn't show nothing.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--94eb2c055f68e9033a05316a576e
No, there is no any trigger.
This is the dbschema of the table:
{ TABLE "xsic".transfbod row size = 417 number of columns = 41 index size = 86
}
create table "xsic".transfbod
(
empresa char(10)
default '3',
folio char(6) not null constraint "xsic".n11988_41214,
fecha char(8) not null constraint "xsic".n11988_41215,
bodega char(3) not null ,
bodega_dirigida char(3) not null ,
tipo char(1),
estado char(1) not null constraint "xsic".n11988_41218,
nomrol char(45),
fecha_apr char(8),
estado_contab char(1),
ultima_mod char(16),
tran_estado char(1),
tran_fec_con date,
doc_tip_com char(10),
doc_nro_com decimal(10,0),
rol char(20),
despacho char(6),
fecha_des char(8),
tipo_comprobante char(10),
fecont char(8),
correlativo char(10),
cr_responsable char(4) not null constraint "xsic".n11988_41219,
cr_destino char(4) not null constraint "xsic".n11988_41220,
emp_externa char(10),
cr_responsable_pro char(4),
cr_destino_pro char(4),
emp_externa_pro char(10),
aud_ing_rol char(10),
aud_ing_fec datetime year to second,
aud_apr_rol char(10),
aud_apr_fec datetime year to second,
aud_des_rol char(10),
aud_des_fec datetime year to second,
aud_anu_rol char(10),
aud_anu_fec datetime year to second,
ind_exacto char(1),
sol_tipo char(1),
sol_folio char(6),
sol_fecha char(8),
fecha_aprobacion datetime year to second,
observaciones char(100)
);
revoke all on "xsic".transfbod from "public" as "xsic";
grant select on "xsic".transfbod to "avb2009" as "xsic";
grant update on "xsic".transfbod to "avb2009" as "xsic";
grant insert on "xsic".transfbod to "avb2009" as "xsic";
grant delete on "xsic".transfbod to "avb2009" as "xsic";
grant select on "xsic".transfbod to "luz" as "xsic";
grant update on "xsic".transfbod to "luz" as "xsic";
grant insert on "xsic".transfbod to "luz" as "xsic";
grant delete on "xsic".transfbod to "luz" as "xsic";
grant select on "xsic".transfbod to "sistec" as "xsic";
grant select on "xsic".transfbod to "sistec1a" as "xsic";
grant update on "xsic".transfbod to "sistec1a" as "xsic";
grant insert on "xsic".transfbod to "sistec1a" as "xsic";
grant delete on "xsic".transfbod to "sistec1a" as "xsic";
grant select on "xsic".transfbod to "sistec1p" as "xsic";
grant select on "xsic".transfbod to "sisteca" as "xsic";
grant update on "xsic".transfbod to "sisteca" as "xsic";
grant insert on "xsic".transfbod to "sisteca" as "xsic";
grant delete on "xsic".transfbod to "sisteca" as "xsic";
grant select on "xsic".transfbod to "xsica" as "xsic";
grant update on "xsic".transfbod to "xsica" as "xsic";
grant insert on "xsic".transfbod to "xsica" as "xsic";
grant delete on "xsic".transfbod to "xsica" as "xsic";
grant select on "xsic".transfbod to "xsiccon" as "xsic";
grant update on "xsic".transfbod to "xsiccon" as "xsic";
grant insert on "xsic".transfbod to "xsiccon" as "xsic";
grant delete on "xsic".transfbod to "xsiccon" as "xsic";
grant select on "xsic".transfbod to "xsiccona" as "xsic";
grant update on "xsic".transfbod to "xsiccona" as "xsic";
grant insert on "xsic".transfbod to "xsiccona" as "xsic";
grant delete on "xsic".transfbod to "xsiccona" as "xsic";
grant select on "xsic".transfbod to "xsicconp" as "xsic";
grant select on "xsic".transfbod to "xsicp" as "xsic";
grant select on "xsic".transfbod to "xsictec" as "xsic";
grant update on "xsic".transfbod to "xsictec" as "xsic";
grant insert on "xsic".transfbod to "xsictec" as "xsic";
grant delete on "xsic".transfbod to "xsictec" as "xsic";
grant select on "xsic".transfbod to "xsictecp" as "xsic";
create index "xsic".idx_transf on "xsic".transfbod (sol_folio,
sol_fecha,sol_tipo) using btree ;
create unique index "xsic".transfbod_idx on "xsic".transfbod (folio,
fecha,empresa) using btree ;
create index "xsic".transfbod_idx1 on "xsic".transfbod (fecha_apr,
folio,fecha,empresa) using btree ;
It may be some lock "hold" in memory ?
What if you expand the 'onstat -g opn' search by the partnums of the=20
table's indices?
From: "ROGER VILCA" <rvilca@luzdelsur.com.pe>
To: ids@iiug.org
Date: 27.04.2016 00:05
Subject: Re: False non-exclusive access when select a table [37045]
Sent by: ids-bounces@iiug.org
No, there is no any trigger.=20
This is the dbschema of the table:=20
{ TABLE "xsic".transfbod row size =3D 417 number of columns =3D 41 index si=
ze=20
=3D 86=20
}=20
create table "xsic".transfbod=20
(=20
empresa char(10)=20
default '3',=20
folio char(6) not null constraint "xsic".n11988=5F41214,=20
fecha char(8) not null constraint "xsic".n11988=5F41215,=20
bodega char(3) not null ,=20
bodega=5Fdirigida char(3) not null ,=20
tipo char(1),=20
estado char(1) not null constraint "xsic".n11988=5F41218,=20
nomrol char(45),=20
fecha=5Fapr char(8),=20
estado=5Fcontab char(1),=20
ultima=5Fmod char(16),=20
tran=5Festado char(1),=20
tran=5Ffec=5Fcon date,=20
doc=5Ftip=5Fcom char(10),=20
doc=5Fnro=5Fcom decimal(10,0),=20
rol char(20),=20
despacho char(6),=20
fecha=5Fdes char(8),=20
tipo=5Fcomprobante char(10),=20
fecont char(8),=20
correlativo char(10),=20
cr=5Fresponsable char(4) not null constraint "xsic".n11988=5F41219,=20
cr=5Fdestino char(4) not null constraint "xsic".n11988=5F41220,=20
emp=5Fexterna char(10),=20
cr=5Fresponsable=5Fpro char(4),=20
cr=5Fdestino=5Fpro char(4),=20
emp=5Fexterna=5Fpro char(10),=20
aud=5Fing=5Frol char(10),=20
aud=5Fing=5Ffec datetime year to second,=20
aud=5Fapr=5Frol char(10),=20
aud=5Fapr=5Ffec datetime year to second,=20
aud=5Fdes=5Frol char(10),=20
aud=5Fdes=5Ffec datetime year to second,=20
aud=5Fanu=5Frol char(10),=20
aud=5Fanu=5Ffec datetime year to second,=20
ind=5Fexacto char(1),=20
sol=5Ftipo char(1),=20
sol=5Ffolio char(6),=20
sol=5Ffecha char(8),=20
fecha=5Faprobacion datetime year to second,=20
observaciones char(100)=20
);=20
revoke all on "xsic".transfbod from "public" as "xsic";=20
grant select on "xsic".transfbod to "avb2009" as "xsic";=20
grant update on "xsic".transfbod to "avb2009" as "xsic";=20
grant insert on "xsic".transfbod to "avb2009" as "xsic";=20
grant delete on "xsic".transfbod to "avb2009" as "xsic";=20
grant select on "xsic".transfbod to "luz" as "xsic";=20
grant update on "xsic".transfbod to "luz" as "xsic";=20
grant insert on "xsic".transfbod to "luz" as "xsic";=20
grant delete on "xsic".transfbod to "luz" as "xsic";=20
grant select on "xsic".transfbod to "sistec" as "xsic";=20
grant select on "xsic".transfbod to "sistec1a" as "xsic";=20
grant update on "xsic".transfbod to "sistec1a" as "xsic";=20
grant insert on "xsic".transfbod to "sistec1a" as "xsic";=20
grant delete on "xsic".transfbod to "sistec1a" as "xsic";=20
grant select on "xsic".transfbod to "sistec1p" as "xsic";=20
grant select on "xsic".transfbod to "sisteca" as "xsic";=20
grant update on "xsic".transfbod to "sisteca" as "xsic";=20
grant insert on "xsic".transfbod to "sisteca" as "xsic";=20
grant delete on "xsic".transfbod to "sisteca" as "xsic";=20
grant select on "xsic".transfbod to "xsica" as "xsic";=20
grant update on "xsic".transfbod to "xsica" as "xsic";=20
grant insert on "xsic".transfbod to "xsica" as "xsic";=20
grant delete on "xsic".transfbod to "xsica" as "xsic";=20
grant select on "xsic".transfbod to "xsiccon" as "xsic";=20
grant update on "xsic".transfbod to "xsiccon" as "xsic";=20
grant insert on "xsic".transfbod to "xsiccon" as "xsic";=20
grant delete on "xsic".transfbod to "xsiccon" as "xsic";=20
grant select on "xsic".transfbod to "xsiccona" as "xsic";=20
grant update on "xsic".transfbod to "xsiccona" as "xsic";=20
grant insert on "xsic".transfbod to "xsiccona" as "xsic";=20
grant delete on "xsic".transfbod to "xsiccona" as "xsic";=20
grant select on "xsic".transfbod to "xsicconp" as "xsic";=20
grant select on "xsic".transfbod to "xsicp" as "xsic";=20
grant select on "xsic".transfbod to "xsictec" as "xsic";=20
grant update on "xsic".transfbod to "xsictec" as "xsic";=20
grant insert on "xsic".transfbod to "xsictec" as "xsic";=20
grant delete on "xsic".transfbod to "xsictec" as "xsic";=20
grant select on "xsic".transfbod to "xsictecp" as "xsic";=20
create index "xsic".idx=5Ftransf on "xsic".transfbod (sol=5Ffolio,=20
sol=5Ffecha,sol=5Ftipo) using btree ;=20
create unique index "xsic".transfbod=5Fidx on "xsic".transfbod (folio,=20
fecha,empresa) using btree ;=20
create index "xsic".transfbod=5Fidx1 on "xsic".transfbod (fecha=5Fapr,=20
folio,fecha,empresa) using btree ;=20
It may be some lock "hold" in memory ?=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
I think there is a bug which could lead to such symptoms after a rollback of some alter table statements. Maybe this applies in your case ? In this case, you'd have to restart the instance...
Yes there is... But the one I've found should be fixed in that version. A PMR would help you. Regards. On Wed, Apr 27, 2016 at 10:45 AM, WOLFGANG EPPLER < wolfgang.eppler@de.ibm.com> wrote: > I think there is a bug which could lead to such symptoms after a rollback > of > some alter table statements. > Maybe this applies in your case ? In this case, you'd have to restart the > instance... > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f8dc02d3b48053174d36d
Hi, I had to restart the instance. After this, the issue is gone. Strange. Thanks. Roger
Hi Andreas,
onstat -g opn previously saved (namely before restart) doesn't show anyrecords for the partnums of table's indices.
The partnums (for the table and indices) are in the outputs of onstat -C all
and onstat -g ppf.
Thanks.
Roger
Sounds like the mentioned APAR... But the versions don't seem to match... On Wed, Apr 27, 2016 at 4:45 PM, ROGER VILCA <rvilca@luzdelsur.com.pe> wrote: > Hi, > > I had to restart the instance. After this, the issue is gone. > Strange. > > Thanks. > Roger > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1141bb98aa2caa053179994d
Hi Fernando, Please if you have the number of APAR, for reference. It is possible that this APAR is not completely fixed. Thanks. Roger
IC78035 but without a PMR it's useless... On Wed, Apr 27, 2016 at 5:13 PM, ROGER VILCA <rvilca@luzdelsur.com.pe> wrote: > Hi Fernando, > Please if you have the number of APAR, for reference. > > It is possible that this APAR is not completely fixed. > > Thanks. > Roger > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1134bdaa41792905317a251f
Oh!, interesting. It is new for me the concept of "counter" table's alter. I don't know where this data is saved. Thanks, Roger
In memory.... that's why a restart works..... Regards On Apr 27, 2016 18:02, "ROGER VILCA" <rvilca@luzdelsur.com.pe> wrote: > Oh!, interesting. It is new for me the concept of "counter" table's alter. > I > don't know where this data is saved. > > Thanks, > Roger > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1141bb98ef01e805317a8d65
Thanks very much Fernando, Regards, Roger
Actually, I didn't mean that old APAR. There is another one, and this one still affects 11.70.FC8W1. IT03643 AFTER ROLLBACK OF A TRANSACTION WITH SEVERAL ALTER TABLE STATEMENTS ERRORS -242/-106 MIGHT BE RETURNED WHEN TRYING TO ACCESS THAT TABLE
Similar effects... But that one would match the version in question, so it's probably that. Regards. On Thu, Apr 28, 2016 at 8:33 AM, WOLFGANG EPPLER <wolfgang.eppler@de.ibm.com > wrote: > Actually, I didn't mean that old APAR. > There is another one, and this one still affects 11.70.FC8W1. > IT03643 > AFTER ROLLBACK OF A TRANSACTION WITH SEVERAL ALTER TABLE STATEMENTS ERRORS > -242/-106 MIGHT BE RETURNED WHEN TRYING TO ACCESS THAT TABLE > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113f8dc0d22cf9053188c0dd
Thanks Wolfgang, I don't see that this issue is fixed, so probably it persists in more recent versions after that 11.70.FC8W1. The local fix (restart of the instance) is radical in an production environment.
Related threads
- record locked
- who locks a record?
- Regarding Non-Default Page Sizes
- Don't Understand Table's Space Requirement