Re: Deadlock Problems

In article <35cb1c2e.16386947@news.rdc.noaa.gov>, Jon M. Roe <jroe@erols.com> writes >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 Commit more often!! >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 -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care