Row locked on reads even under dirty read

Hi again, family. This is not so much a call for help as it is a request for discussion. Here's the way the problem was presented to me: The programmer, Charlie, complains to me that his app is getting a slew of error 244/154 (or, if not waiting on locks, 244/144). (Exactly how much is a slew? ;-) The [local] tables are built with lock mode row and the app is running under dirty-read isolation so that eliminates those as possible solutions. Another anomaly is that when I run his query in one window and run my who-lock.sh utility in another window, I fail to show up as a waiter. (who-lock.sh is a shell script that peeks around the locks table with SMI queries. It's in IIUG archives.) Questions: 1. How can I possibly get a lock error when I am under dirty read? 2. Having set lock mode to wait 30 seconds, and having gotten ISAM error 154 (Lock time out), why did I show up as a waiter when I queried the locks? Further poking around showed that the number of rows retrieved (when I did not get the "locked" error) varied wildly each time I ran [a variant of] Charlie's query, moments apart: 67 rows, then 68, then 114, 255, 108 etc. So the apps is inserting and deleting rows at a frantic pace. Theory: Charlie's query was not actually waiting for a row lock. However, due to the frantic pace of row deletions, the B-Tree cleaner thread is one very busy thread. While an index page is being cleaned, it necessarily remains locked, even to someone operating in dirty read. After all, the page is in a state of flux. But (here's the tough part) this type of lock does not show up in the SMI query on syslocks. I'm not sure it would show up in onstat -k. Perhaps it is actually held by the BT-Cleaner with a latch. Meantime, the dirty-read query is waiting for the cleaner to give the go-ahead. It takes to blessed long so the lock wait finally times out. This would answer both above questions: A dirty read will scoot me past a lock but not past a latch. And a latch does not show up in the locks table. Can anyone bolster or blister my theory? I don't know what sysmaster table to query for this kind of latch info; there is no table "syslatches" listed among the sysmaster tables. I have tentatively suggested that Charlie: - Raise the lock wait to 60 seconds, as a band-aid solution that seems to work in my tests. - Stop deleting the rows during regular processing. Rather, mark it for deletion abd have a cron job run once an hour and/or during off hours to delete the marked rows. This should reduce conflict between the app and the BT-Cleaners. This part assumes my theory is correct. Thanks much. -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.