Deadlock Problems
Posted in 1998
We are having some trouble with impending deadlocks. Here is our situation: We have Informix 7.30.UC2 running on HP-UX 10.20. We have a fairly large database of meteorologic and hydrologic data that cycles through the database at a high rate. Many thousands (and over 100000) of records arrive every day. We keep about 30 days of data. So, we run a DB clean up program once or twice a day to chop off data older than the 30 days while new data arrive all the time. When our DB clean up program runs, it very occasionally hits a -244 error with an accompanying -143 ISAM error on one of the most dynamic tables in the database. This table has row locking defined, it holds 30 days of data in the several megabyte range. The impending deadlock (as indicated by the -143 ISAM error) appears to be between this program and the program that continually writes new data into this table. Keep in mind that the purge program is deleting data whose primary key includes dates 30 days earlier than the dates in the primary key of the data being posted. That is, there is no direct primary key contention. Both programs, the purge program and the poster program, have the directive "SET LOCK MODE TO WAIT". The poster program does not write directly to the problem table (call it "B") but rather writes to another table (call it "A") that has a trigger/procedure to move the record from table "A" to table "B" upon detection of the insert to table "A". Can anyone help me to avoid this impending deadlock? What am I doing wrong here? It is serious to fail in the delete because then the failed delete is rolled back and tried again on the next run of the purge program. Sometimes, if deletes fail a couple of cycles, then the delete transaction starts getting too large for our data logging/replication set up and we get failures for long transactions. So, I like to make sure that each cycle of the purge program runs successfully on all tables, especially the high volume tables. Thanks for any help here. Jon Roe NOAA/NWS