Re: Lost Invoices et al...

Well folks, this invoice thing gets more interesting every day, but some progress is being made...sorta. I changed the program to lock a row in a new table I created when the program fires up. With a SET LOCK MODE WAIT, this should only allow one process to run at one time, hopefully elimating any concurrency problems with other invoicing processes. So far, this seems to be the case. I think some of the lost invoices may be related to another problem. For whatever reason, the invoicing programs have all managed to get stuck, with about 30 of them queued up and waiting. With all of these 4gi's running, and their sqlexecs, the OS goes into a fit about File Table Overflow. I'm thinking that when that happens, some of the rollbacks are failing (fair enough...can stand to well when the rug gets pulled out from under you). Anyway, I haven't any idea why some of these are going bannas and causing a traffic jam. I'll look into that later. I'm not going to tune the problem because it isn't the solution. Heck, if I increase the values then maybe I'll have 45 backed up instead of 30 :-(. But, dig dis! I'm more befuddled by this than anything I've seen yet. Pseudo Code follows --- (I've heard that said about a lot of my code :-) BEGIN WORK LOCK CONTROL RECORD # To make sure only one sessions runs UPDATE ORDER DATA (1 row) UPDATE DETAIL DATA (several rows, usually < 5) UPDATE SHIP DATA (similiar to detail) Now, the DETAIL and SHIP updates are of the form: update shiptable set status="I" where doc_no=cur_order_doc_no It's now in a cursor or a loop of anykind, just one statement. If any of those fail, the WHENEVER ERROR HANDLER dumps the program and the errlog gets any messages. The routine continues: . . Bunch -O- assignments etc . . INSERT INTO INVOICE VALUES (INVOICE_REC.*)UPDATE SHIP DATA with new INVOICE DOCUMENT NUMBER. This final UPDATE fails **SOMETIMES**. Errlog says it doesn't have a lock. THIS IS REALLY BIZARRE!! This update statement is updating the same rows as the earlier one is, in the same transaction. Just some new numbers that we figure out later. One may think it's a contention problem. So would I except: o LOCK MODE IS WAIT (should wait for the lock to free up) o I updated the SAME rows earlier, in the same transaction, which means that *I* should have any locks if anybody does. o This stuff isn't supposed to happen on Fridays!!! o * * ** GAH! ** * * So, that's the latest in this story. If you've got any other ideas on this part, drop me a line...Please, don't wait for the movie... Will (uunet!la4gen!villy)