Concurrent access to a table

IDS WGE 7.30UC2 in SCO UNIX My DataBase has Unbuffered logging The problem: I have a program that makes some updates in a transaction. Within this transaction 3-5 tables will be updated and 1-3 records for each table. The updates usually use the record's primary key. I know the 4GL code is like: ... SET LOCK MODE TO WAIT 10 WHENEVER ERROR CONTINUE BEGIN WORK .. UPDATE table1 SET field1 = newvalue WHERE field_primary = this IF STATUS < .... UPDATE table2 SET field2 = newvalue WHERE field_primary = this IF STATUS < .... ... ... IF ... COMMIT WORK ELSE ROLLBACK... WHENEVER ERROR STOP SET LOCK MODE TO NOT WAIT The problem arises when another program, usually a REPORT that opens a cursor fetching a large portion of the table1 records, reads from table1 while the first program work. Then, this second program stops with something like: (This program doesn't try to make updates, only reads records!) Program error at "xplib.4gl", line number 81. SQL statement error number -243. Could not position within a table (market:market.mfpel). SYSTEM error number -107. ISAM error: record is locked. Can I have a solution using appropriate ISOLATION LEVELS and how? It would be very nice for me, if the solution could be applied without the need to modify 4GL code! Thank you very much --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003

Seems like you need to set isolation to dirty read. Check out this FAQ link http://www.developer.ibm.com/tech/faq/individual/0,,2:26263,00.html HTH, Zev Berezin B&H Photo Informix DBA On 24 Oct 2003, at 2:39, Pantelis Magos wrote: > IDS WGE 7.30UC2 in SCO UNIX > My DataBase has Unbuffered logging > > The problem: > I have a program that makes some updates in a transaction. > Within this transaction 3-5 tables will be updated and 1-3 records for > each table. The updates usually use the record's primary key. I know > the 4GL code is like: > ... > SET LOCK MODE TO WAIT 10> WHENEVER ERROR CONTINUE > > BEGIN WORK > .. > UPDATE table1 SET field1 = newvalue WHERE field_primary = this> IF STATUS < .... > > UPDATE table2 SET field2 = newvalue WHERE field_primary = this> IF STATUS < .... > ... > ... > > IF ... COMMIT WORK ELSE ROLLBACK... > > WHENEVER ERROR STOP > SET LOCK MODE TO NOT WAIT> > The problem arises when another program, usually a REPORT that opens a > cursor fetching a large portion of the table1 records, reads from > table1 while the first program work. Then, this second program stops > with something like: (This program doesn't try to make updates, only > reads records!) > > Program error at "xplib.4gl", line number 81. > SQL statement error number -243. > Could not position within a table (market:market.mfpel). > SYSTEM error number -107. > ISAM error: record is locked.> > Can I have a solution using appropriate ISOLATION LEVELS and how? It > would be very nice for me, if the solution could be applied without > the need to modify 4GL code! > > Thank you very much > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 > > > >

This seems happened to my programs also. We have several programs that used transaction log (but some don't) and all doing inserting, updating and deleting within the (almost) same tables. The steady problem is trying to delete row from a table. Most applications used '...DIRTY READ'. Once I changed some to '...REPEATABLE READ' and some to '...WAIT MODE'. My question here is what is the best ISOLATION level that I should use to avoid this conflict? Your help would be appreciated Tah Davis -----Original Message----- From: Zev Berezin [mailto:zevb@bhphotovideo.com] Sent: Friday, October 24, 2003 7:22 AM To: ids@iiug.org Subject: Re: Concurrent access to a table [2069] Seems like you need to set isolation to dirty read. Check out this FAQ link http://www.developer.ibm.com/tech/faq/individual/0,,2:26263,00.html HTH, Zev Berezin B&H Photo Informix DBA On 24 Oct 2003, at 2:39, Pantelis Magos wrote: > IDS WGE 7.30UC2 in SCO UNIX > My DataBase has Unbuffered logging > > The problem: > I have a program that makes some updates in a transaction. > Within this transaction 3-5 tables will be updated and 1-3 records for > each table. The updates usually use the record's primary key. I know > the 4GL code is like: > ... > SET LOCK MODE TO WAIT 10> WHENEVER ERROR CONTINUE > > BEGIN WORK > .. > UPDATE table1 SET field1 = newvalue WHERE field_primary = this> IF STATUS < .... > > UPDATE table2 SET field2 = newvalue WHERE field_primary = this> IF STATUS < .... > ... > ... > > IF ... COMMIT WORK ELSE ROLLBACK... > > WHENEVER ERROR STOP > SET LOCK MODE TO NOT WAIT> > The problem arises when another program, usually a REPORT that opens a > cursor fetching a large portion of the table1 records, reads from > table1 while the first program work. Then, this second program stops > with something like: (This program doesn't try to make updates, only > reads records!) > > Program error at "xplib.4gl", line number 81. > SQL statement error number -243. > Could not position within a table (market:market.mfpel). > SYSTEM error number -107. > ISAM error: record is locked.> > Can I have a solution using appropriate ISOLATION LEVELS and how? It > would be very nice for me, if the solution could be applied without > the need to modify 4GL code! > > Thank you very much > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003 > > > >

As with your first program, you set your lock mode to wait 10. The simplest is also to set your lock mode on your second program. You can set it to a number of seconds or forever, that depends on you and how long your are willing to wait for a transaction to finish. In fact, you should have it set on all your programs unless you have a good reason to fail on transient locks. You shouldn't use an isolation level of dirty read on your report unless the report doesn't care about half/uncommitted transactions, that's up to you to determine. Scott M. Kolaya Lead Database Administrator Fleet Libris Information Solutions Ph: 518-471-1830 Fax: 518-471-1841 <mailto:Scott_M_Kolaya@fleet.com> -----Original Message----- From: Pantelis Magos [mailto:pas@logifer.gr] Sent: Friday, October 24, 2003 2:40 AM To: ids@iiug.org Subject: Concurrent access to a table [2068] IDS WGE 7.30UC2 in SCO UNIX My DataBase has Unbuffered logging The problem: I have a program that makes some updates in a transaction. Within this transaction 3-5 tables will be updated and 1-3 records for each table. The updates usually use the record's primary key. I know the 4GL code is like: ... SET LOCK MODE TO WAIT 10 WHENEVER ERROR CONTINUE BEGIN WORK .. UPDATE table1 SET field1 = newvalue WHERE field_primary =this IF STATUS < .... UPDATE table2 SET field2 = newvalue WHERE field_primary =this IF STATUS < .... ... ... IF ... COMMIT WORK ELSE ROLLBACK... WHENEVER ERROR STOP SET LOCK MODE TO NOT WAIT The problem arises when another program, usually a REPORT that opens a cursor fetching a large portion of the table1 records, reads from table1 while the first program work. Then, this second program stops with something like: (This program doesn't try to make updates, only reads records!) Program error at "xplib.4gl", line number 81. SQL statement error number -243. Could not position within a table (market:market.mfpel). SYSTEM error number -107. ISAM error: record is locked. Can I have a solution using appropriate ISOLATION LEVELS and how? It would be very nice for me, if the solution could be applied without the need to modify 4GL code! Thank you very much --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003