RE: Deadlock Problems

Since your contention is centered on this one table, I would strongly suggest that you look at fragmenting the table. You would probably want to fragment by expression, putting one day's worth of data into a fragment. At the start of each day, simply do an "ALTER FRAGMENT... DETACH..." to detach the fragment that is 31 days old (you will have to detach it into some new table, say "foo"), and then just drop that new table -- no long transaction to do that!! Then you can turn around and do an "ALTER FRAGMENT... ADD..." to reassign that same fragment back to the table, but this time with a new expression, say for tomorrow's data. We do this with some cyclic data, and I've heard of many other organizations that use this technique. I suggest having MORE than 30 fragments, so that you won't be in a time crunch to get an old fragment reassigned before some new data comes in for it. On one of our "cyclic" applications, we store 60 days of data, and have 67 fragments assigned to the table, so that we can do the fragment reassignments once a week, right before we run our weekly db maintenance jobs. HTH ================================ Paul A. Mosser, Open Systems DBA Informix Certified DBSA Wells Fargo & Co. Tempe, Arizona mosserp@wellsfargo.com ================================ > -----Original Message----- > From: David Williams [mailto:djw@smooth1.demon.co.uk] > Sent: Monday, August 10, 1998 4:52 PM > To: informix-list@iiug.org > Subject: 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 >