Lock tables

Hi, we are running IDS 7.3 on SCO Openserver Enterprise 5.0.5 We have the next 4GL module: .................. .................. .................. BEGIN WORK while for ... if ... SET LOCK MODE TO WAITinsert .... end if end for if ... for ... if ... SET LOCK MODE TO WAIT update lb_aforo set senplla .... where feccer = ..... andnrocer = ..... and codafo = ..... (** this table has a duplicate index on rows that I show in the where clause above **) end if end for end if if ... SET LOCK MODE TO WAIT update lb_aforo set senplla .... where feccer = ..... andnrocer = ..... and codafo = ..... (** this table has a duplicate index on rows that I show in the where clause above **) end if ................. ................. ................. exit while end while if (status of insert´s, update´s is ok) commit work else rollback work end if It´s a multiuser environment, each user can only insert or update one certificate identify by 4 fields in the table: depnrodoc,nronrodoc,anionrodoc and dignrodoc In one table specially get the next message: Log file: "Program error at "lb_cergra.4gl", line number 389. SQL statement error number -244. Could not do a physical-order read to fetch next row. SYSTEM error number -143. ISAM error: deadlock detected " We´ve probed the next options: 1) SET LOCK MODE TO WAIT before each select´s and update´s, but still received the error message 2) SET ISOLATION LEVEL TO DIRTY READ, we have one question: we´ve put it before each select, but it must be at the beginning of the module ?. We put if before each select but it delay the transaction to 5 minutes to commit it. 3) Another question: the error message we get specially in lb_aforo table, althought it continues executing the 4GL module to the next if sentence below the if that contains the update sentence of that table. The result of all is that the update sentence of the lb_aforo table is not executed. The question is: "the index table is also locked by users because of the update sentence, insert or delete". If so, we have set locklevel to row in all these tables and we thought that the lock is set only in the row that you are updating, leaving the rest not locked, but is seems not to be that way. 4) We have to reduce the sentences that contain the transaction to only contain update´s and insert´s ? We´ll apreciate any help.

MARCELO MUSRI wrote: > Hi, we are running IDS 7.3 on SCO Openserver Enterprise 5.0.5 > > We have the next 4GL module: <DOTS SNIPPED> I'm not sure this is the right forum for a 4GL question, but... > It´s a multiuser environment, each user can only insert or update one certificate identify by 4 fields in the table: depnrodoc,nronrodoc,anionrodoc and dignrodoc > > In one table specially get the next message: > > Log file: > "Program error at "lb_cergra.4gl", line number 389. > SQL statement error number -244. > Could not do a physical-order read to fetch next row. > SYSTEM error number -143. > ISAM error: deadlock detected "> > We´ve probed the next options: > > 1) SET LOCK MODE TO WAIT before each select´s and update´s, but still received the error message > 2) SET ISOLATION LEVEL TO DIRTY READ, we have one question: we´ve put it before each select, but it must be at the beginning of the module ?. We put if before each select but it delay the transaction to 5 minutes to commit it. > 3) Another question: the error message we get specially in lb_aforo table, althought it continues executing the 4GL module to the next if sentence below the if that contains the update sentence of that table. The result of all is that the update sentence of the lb_aforo table is not executed. > The question is: "the index table is also locked by users because of the update sentence, insert or delete". If so, we have set locklevel to row in all these tables and we thought that the lock is set only in the row that you are updating, leaving the rest not locked, but is seems not to be that way. > 4) We have to reduce the sentences that contain the transaction to only contain update´s and insert´s ? You are encountering a deadlock. Just in case you do not know what that is, it's when user 1 is locking record A and is trying to lock record B. User 2 already has record B locked and is trying to lock record A. The database server will detect this scenario and abort one of the users automatically to avoid the users sitting there forever. You should look at your logic and see if you can rewrite your code to avoid deadlocks. In any event, your code should be able to handle deadlocks and retry the operation if one is detected. I do hope all those SQL statements are PREPARED first outside the main loop. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+

What sql is running at that line of the code? How many rows are affected and is it using an index to update/? the row(s) in the table? MW > > MARCELO MUSRI wrote: > > > Hi, we are running IDS 7.3 on SCO Openserver Enterprise 5.0.5 > > > > We have the next 4GL module: > <DOTS SNIPPED> > > I'm not sure this is the right forum for a 4GL question, but... > > > It´s a multiuser environment, each user can only insert or > update one certificate identify by 4 fields in the table: > depnrodoc,nronrodoc,anionrodoc and dignrodoc > > > > In one table specially get the next message: > > > > Log file: > > "Program error at "lb_cergra.4gl", line number 389. > > SQL statement error number -244. > > Could not do a physical-order read to fetch next row. > > SYSTEM error number -143. > > ISAM error: deadlock detected "> > > >[cut] > ---------+ > >