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: > ... > > >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. The primary key *includes* the date, but does it *start with* the date? What (if any) other criteria are used to identify the rows to be deleted, besides the date? If your delete statement looks like: DELETE FROM table WHERE date_key < today - 30then it will only use the index if the first column in the index is the date. If the index does not begin with the date column, then it will do a sequential scan of your table, which will fail if it ever encounters a locked row. This locked row does not have to match the criteria for the delete, since the delete process is sequentially scanning. Although the primary keys are not in contention, if you are not using the primary key to identify the row, it does not guarantee that there will not be contention. Fragmenting on the date and detaching the fragment is also a good scheme, as mentioned before. June