Re: Lost invoices (Was: Couple of Oddities...)

In article <8849@emory.mathcs.emory.edu> you write: |> I'm observing some strange things happening with an invoicing process |> that we're using at a client. The operator fires off an invoice program |> that runs in the background and inevitably spools the invoice to the |> printer. |> |> They had been complaining about invoices that were completing, so I |> modified the invoice program to do the following. |> |> BEGIN WORK |> INSERT INTO INVCLOG (...LOG DATA...) |> COMMIT WORK |> |> BEGIN WORK |> CREATE INVOICE |> UPDATE ORDER |> UPDATE DETAIL |> UPDATE INVCLOG (..INVCOICE DONE DATA...) |> COMMIT WORK |> |> This pointed out that they were indeed starting several invoices, but |> they were not completing (and therefore the work rolledback). Turned out |> to be a locking problem (they would run several of things at the same time). |> |> I added a "SET LOCK MODE TO WAIT", in hopes that it would solve the locking |> problem during the updates. It has, mostly. We still seem to be seeing |> locking problems, even with the LOCK MODE WAIT. Does that make any sense? |> Does the LOCK MODE WAIT time out? I know that OnLine will lock multiple rows |> on an INSERT (notably the rows before/after the row that being inserted... |> something like that), does SE do the same thing? |> |> Anyway, another truly bizarre thing that I'm now find is that some of the |> orders say they are invoiced, yet there are no corresponding invoice rows. |> This confuses me as they are all one in the same process, with the same |> commit. |> |> Anyway, looking for any hints/help/sympathy.... |> Oh, boy! You get my sympathy and the following suggestion: The way I would handle this is to QUEUE the invoices and let one process handle the data in the queue. The same would apply anytime you use a LOG file or a LOG record. A database QUEUE is a table that you add data to that is to be processed, much like lp or lpr processes print requests. Multiple database application processes can add records to this table at will but only one process removes and processes data from this table. This means a separate running program for that process which is not tied to the application that puts data into the queue. If the data in the queue table is to be processed by priority, then one field in the table can have a priority function, like "do all A's, then B's", or "search for smallest value". The queue handler repeats its search instruction after processing each record. If you need to do multiple processing on a queue (say you have two or more printers that can handle invoices) then by all means use _interprocess communication_ to let all other processes wait on one while that one process is performing its search and retrieval (we don't want anything disappearing between the time we start our search and the time we try to grab the data). If there is a much better technique available in Informix I would like to hear about it. I've been using this one quite successfully. This one came about as a result of not trusting record locking and not wanting to build in massive "what are you doing?" code into one application. Many thanks to Covalent for this one. (I hope they didn't copyright this technique. My appologies if they did.) --- / . . \\ | - | \\ \\_/ / --- ------------------------------------------------------------------------------ John R. Carter, Sr. Programmer/Analyst - Informix DBA Computer Sciences Corporation jcarter@nas.nasa.gov Numerical Aerodynamic Simulation 415/604-3662 NASA Ames Research Center, Moffett Field, CA 94035-1000 M/S 258-6 ------------------------------------------------------------------------------