Re: Row Locked Errors

Like I said in an earlier message I'm not a progammer, yet it looks to me like you should commit the next invoice number as soon as you have done the insert into the table. If the other updates do not work and you do not want that invoice number to hang around then you would have to change your logic to 1) delete it(if you have a 7 DB casscade delete would be a good candidate) and 2) when you first set the value , check for gaps between invoice numbers, so someone could use that invoice number! But if invoice number is a serial field I think step two may not be required. ------------------------------------------------------------------------- Cheryl Kendricks Internet:cherylk@prod1.jcdc.doleta.gov OR kendric@gwysmtp.jcdc.doleta.gov OR cherylk@spamis.jcdc.doleta.gov DTSI, Inc. Voice: 1-800-598-5008 Database Administrator - DOL Job Corps San Marcos, Texas ------------------------------------------------------------------------ On Mon, 15 Apr 1996, Andrew Storer wrote: } 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 }