CONTINUE SET ISOLATION

>>W Mat Waites <ren1347@harriet.rencorp.com> wrote: >>xrizzi@arcride.edu.ar writes: >>> From different processes is NOT possible to agree to rows NOT selected >>>by the CURSOR FOR UPDATE. >>> >>> The messages error of informix is the following: >>> >>> - 246 could not I give an indexed read to get next row. >>> >> >>It sounds like you are hitting the "adjacent row key locking" >>problem. This is supposedly fixed in 6.0. >> >>When a row is locked, the row before it and after it is also locked. June Tong <junet@informix.com 14-MAR-1995 07:44> wrote: >Well, not exactly. When a row is deleted, the index key value after it (or >the next rowid, if the index accepts dups and there is a next rowid) is >locked. When a row is inserted, the index key value (or next rowid) is TESTED >for a lock. When a row is updated, IF the index key value is updated, then >it's treated as a delete and an insert. At no point is the row before it >locked. >This has been changed in 6.0 and greater versions of OnLine. Also, in 4.11 >and 5.01, a change was added to allow adjacent inserts. >None of this necessarily explains why the original poster is getting -246 >errors. It would be necessary to know: >- What rows had been selected with the CURSOR FOR UPDATE >- What rows had been updated >- What other rows were being (attempted to be) selected >- What indexes exist on the table >- What ISAM error was returned by the 2nd select Our platform is INFORMIX-ONLINE 5.00.UD3 and the situation is the following: We enter a invoice number and we need to lock ONLY those items of this invoice that correspond to a number of deposit determined, of such way to permit to agree from another screen to the items of this same invoice but that correspond to another number of deposit. To attempt to make this, we find us with the surprise of the fact that only can agree to invoices with numbers not correlative. The return code errors are: - 246 could not I give an indexed read to get the next row - 107 ISAM error: record is locked For example: a) Error when: User 1 agrees to: Number Invoice 1., Number deposit 1 User 2 agrees to: Number Invoice 1., Number deposit 2 b) Error when: User 1 agrees to: Number Invoice 1., Number deposit 1 User 2 agrees to: Number Invoice 2., Number deposit 1 c) No error when: User 1 agrees to: Number Invoice 1., Number deposit 1 User 2 agrees to: Number Invoice 3., Number deposit 1 The scheme of tables and indices is the following: { TABLE "informix".FACTURAS row size = 61 number of columns = 14 index size = 82 } create table "informix".facturas ( COD_TIPO_COMPROB integer not null, NRO_COMPROBANTE decimal(12,0) not null, fecha date not null, hora datetime hour to minute not null, cod_tipo_doc smallint not null, nro_documento decimal(11,0) not null, cod_tipo_venta integer not null, cod_tipo_credito integer not null, numero integer not null, cod_situ_credito integer not null, fecha_situ_credito date not null, lugar_pago integer not null, nro_responsable integer not null, total_gral decimal(10,2) not null, PRIMARY KEY (COD_TIPO_COMPROB,NRO_COMPROBANTE) constraint "informix".u375_663 ); { TABLE "informix".ITEM_FACTURA row size = 65 number of columns = 15 index size = 84 } create table "informix".item_factura ( COD_TIPO_COMPROB integer not null, NRO_COMPROBANTE decimal(12,0) not null, NRO_ITEM smallint not null, codigo_articulo integer not null, cantidad smallint not null, NRO_DEPOSITO integer not null, cod_forma_envio char(1) not null, precio_uni_contado decimal(10,2) not null, precio_uni_sin_iva decimal(10,2) not null, iva_unitario decimal(10,2) not null, recargo_iva_uni decimal(10,2) not null, impuesto_int_uni decimal(10,2) not null, bonificacion decimal(10,2) not null, nro_vendedor integer not null, entrega_total char(1), PRIMARY KEY (COD_TIPO_COMPROB,NRO_COMPROBANTE,NRO_ITEM) constraint "informix".u376_671 ); alter table "informix".item_factura add CONSTRAINT (foreign key (COD_TIPO_COMPROB,NRO_COMPROBANTE) references "informix".FACTURAS constraint "informix".r283_286); --------------------------------------------------------------------- The .4gl it is the following: ... stuff deleted SET ISOLATION TO REPEATABLE READ ############## BEGIN WORK ############## DECLARE c_items_fact CURSOR FOR SELECT nro_item, codigo_articulo, cantidad,cod_forma_envio FROM item_factura WHERE nro_deposito = (numero de deposito ingresado) AND cod_tipo_comprob = (tipo de comprobante ingresado) AND nro_comprobante = (numero de comprobante ingresado) AND entrega_total = "N" FOR UPDATE LET p_indice = 1 OPEN c_items_fact #------------------------------------------------------------# # The FETCH asigns the rowss selected by the CURSOR to the # # ARRAY that is used to show by screen trough a INPUT ARRAY # #------------------------------------------------------------# FETCH c_items_fact INTO ga_item_factura[p_indice].nro_item, ga_items[p_indice].codigo_articulo, ga_items[p_indice].cantidad, p_cod_forma_envio WHILE STATUS <> NOTFOUND AND p_indice < 12 IF STATUS < 0 THEN #--------------------------------------------------# # IF error ==> EXIT WHILE # #--------------------------------------------------# stuff deleted.. ELSE #------------------------------------------------------# # IF not error ===> add 1 to the index of the ARRAY # #------------------------------------------------------# stuff deleted.. LET p_indice = p_indice + 1 END IF IF p_indice < 12 THEN FETCH NEXT c_items_fact INTO ga_item_factura[p_indice].nro_item, ga_items[p_indice].codigo_articulo, ga_items[p_indice].cantidad, p_cod_forma_envio END IF END WHILE stuff deleted... INPUT ARRAY ga_item_factura.* ... stuff deleted.... END INPUT stuff deleted... ######################### COMMIT WORK or BEGIN WORK ######################### ---------------------------------------------------------------- We are not interested in migrateing short term to the version 6.0, therefore we wish to know if exists another solution to our problem You wrote:@@NL@