Re: Row Locked Errors

> Andrew Storer <andrew@astorer.demon.co.uk> writes: > The company I work for has a client/server application - Gupta > front-end connecting to an Informix database. One part of the > application requires that an invoice be created. The processing > goes something like... > begin work > get next_invoice_no from system_counts table > increment next_invoice_no in system_counts table > insert new invoice using next_invoice_no > ..... > < do lots of other inserts/updates> > ..... > if any errors detected above > display appropriate error message > rollback work > else > commit work > endif > > Generally this is OK but sometimes a network or Gupta problem > causes the 'lots of updates' to take a long time (>1 minute when > usually <1 second) which means that a lock remains on the > system_counts table until the problem PC has finished its > transaction and nobody else can update the next_invoice_no. > > Hence, we get 1 phone call 'My PC is going slow' and 20 calls > 'I get row locked error'. > > Bearing in mind that: > 1. All of the updates must be done in a single transaction > 2. We cannot have gaps in the invoice numbers > > Does anybody have any suggestions? > Thank you in advance. > > Andrew Storer Something along the following lines might work for you. Several status/error checks have been omitted. You will of course not get invoice numbers in invoice creation sequence if a user gets an error that leads to a rollback. This will in the worst case lead to an invoice from one day having a lower number than one from an earlier day. If that is not acceptable to you you have got a bussiness rule problem that can't (easily) be programed around. Make a new table: create table xxinvno( invno serial, used char(1) primary key(invno)); create index xxinvno_used on xxinvno(used,invno); And in your program: begin work select max(invno) into next_invoice_no from xxinvno where used = "N" if status = 0 then update xxinvno set used = "Y" where invno = next_invoice_noelse insert into xxinvno (invno,used) values (0,"Y") let next_invoice_no = sqlca.sqlerrd[2] end ifif any errors detected above display appropriate error message rollback work return else commit work endif other users are now free to get there invoice numbers while your program continues: begin work insert new invoice using next_invoice_no .... < do lots of other inserts/updates> .... if any errors detected above display appropriate error message rollback work if rollback ok must set the invoice number to not used: begin work update xxinvno set used = "N" where invno = next_invoice_no if any errors from this update you have goten a real problem - solve manualy rollback work else commit work end if else real problem to handle manually end ifelse commit work endif You might want to delete rows from xxinvno from time to time to avoid any performance problems. Nils.Myklebust@ccmail.telemax.no NM Data AS, Postbox 9090, Gronland, 0133 Oslo, Norway My opinions are those of my company