Re: Deadlock Problems

David Williams wrote: > > 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: [SNIP] > >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. [SNIP] A deadlock indicates that one session holds resource A and wants access to resource B while another session holds resource B and wants access to resource A. Neither can get what it needs because the other is locking it out, deadlock. The solution is to reduce the number of resources you hold in your delete session. You must not delete with an inclusive where clause on an active system. Set up an ESQL/C or 4GL program to select the set of rows to delete without locking and then delete them one or a few at a time then if you do deadlock and have to rollback and ty again you can A) do that within the application immediately since the INSERT that you are deadlocked with is presumably a very quick transaction, and B) only have to rollback and redo a few deletes rather than thousands. I am currently working on a generic version of such a deleter program and will post it to the IIUG Software Repository when I am happy with it (the current version is just too slow, but I'm working on it). Art S. Kagel