the problem lies in deleting records from a 18 million record database that has 22 indexes on it while it is being searched at very high rate. I have been watching in ltest and when this happens the searches back up. Ltest looks like this sometimes.
Yes.. the key from which the delete is run is indexed. I am assuming that the lock stays longer than most due to the 22 indexes that need to be updated from the delete. My long term thoughts are to add a flag column to the table and update the flag if the inventory is sold and then do a batch delete late at night. but that is long term and with XMAS pounding us I would like to find a short term boost to performance and the stop the timeouts that this problem is creating.
Unfortunately there are about 10 autonomous applications that can delete from that table so serializing the deletes could be a problem. Ill research the fairlock idea further.. thanks
If you change the applications to record the records that they want to be deleted into a table then you can perform the deletes at a convenient time and also serialize them.