Re: CONTINUE SET ISOLATION

xrizzi@arcride.edu.ar wrote: [previous discussion on REPEATABLE READ locking deleted] : 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. This is because your indexing strategy does not match you processing/isolation strategy. : 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); This index does not provide the selectivity you need for your cursor. See below. : --------------------------------------------------------------------- : 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 In order to inhibit the locking of extraneous rows you need an index on item_factura(nro_deposito,cod_tipo_comprob,nro_comprobante,entrega_total). Because this index is not unique, you will still suffer from one undesirable lock, ie the row immediately following the last row in your set. If you know that there will always be rows where entrega_total > "N", then this would not cause a problem for the functions that you have shown us. If the above is not true, then you should order the columns in the new index to minimise the impact of an unwanted lock on the "next" row. (ie you can choose between locking the next nro_deposito, the next cod_tipo_comprob or the next nro_comprobante for those cases where there are no rows where entrega_total > "N"). IMHO You should also put a column list on the FOR UPDATE clause which only lists those columns which you might update (I am assuming that you never update nro_item). This allows OnLine to optimise out the possibility of an update to the Primary Index (ie simpler locking strategy). Although I have not verified that your release actually implements this, writing efficient code is a good habit to get into. : 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 : #########################@@NL