Re: Index Read Errors

DRI wrote: > > We're using stored procedures to update a set of tables on a data server > which are maintained by an invoice payment app used concurrently by 100 > or so users. The application sets isolation to DIRTY READ for each > session, yet during periods of heavy usage, stored procs are failing due > to index read errors. Row locking is used on all tables and a row is > locked while a user is modifying an invoice that is in the tables. > > Looking for suggestions as to how we can eliminate this problem. DRI (Is that a gender-neutral name or what? ;-) If I understand your situation (a dubious proposition in itself ;), your application is continuing to run - including updates - against the database. If this is indeed the case, the DIRTY READ won't prevent the app from placing locks. (OK, the engine from placing locks on behalf of the the user session running the app. Sheesh! You are really nitpicking! ;-) An update imposes a lock no matter what the isolation level. No matter what isolation level you are running under, a lock placed because of an update (or insert, or index lock due to a delete) hangs around until the app executes a COMMIT WORK. Furthermore, if you place an index lock by deleting a row, or updating a column that is part of an index key, the index key-value structure hangs around in a "deleted" state until the B-tree cleaner thread gets around to it. (I am now assuming you are using DSA 7.1 or higher.) Similarly, the key value lock hangs around until the B-tree cleaner finishes its janitorial duties. (I could expand on this but if I have not understood you correctly, I'd be wasting my fingers to elaborate..) What can you do about this? Weelll..... If you have not done so already, you might mitigate these collision by: 1. Running the stored procedures with "set lock mode to wait". That way, if a key value is still locked (and *IF* this is the reason for the index read errors) the procedure will happily wait for the lock to get released. 2. Try using "lock mode row". Though I am skeptical if this will help much for the indexed reads. This will not help for "deleted" key values. If you have tried these already you may need to run these procedures at times of lower user activity. Perhaps run it several times, each time with a different value to define a subset of the rows in the tables to be updated. Good luck. -- -- Jake Salomon (Concise is NOT my middle name) +-----------------------------------------------------------+ | Diplomacy: The art of getting something off your | | chest without losing your shirt | +------------------------Alfred E. Neuman-------------------+