Re: OnLine6.0 Locking BUG !HELP!

> The commit work statement may fail, > usually when a larger number of users is using the system, with the > following error: > > SQL statement error number -246. > Could not do an indexed read to get the next row. > SYSTEM error number -154. > ISAM error: Lock Timeout Expired First of all, I'm not sure this is a bug. I have seen this a few times on Online 6.00UE1 but the error never occurred on a COMMIT, only on FETCHes. I guess it could happen on a COMMIT if you use insert cursors. The error seems to occur when an index value (we use row level locking) is locked. We use the following guidelines: 1. Make index more unique. If you have a highly duplicated index, add another column to make it more unique. This increases the chance that different transactions will lock different key values and therefore you will have less contention. 2. Avoid index scans. Even if sqlexplain says that the index path is used, look carefully at the filters used. For example, if it uses a composite index made up of fields A, B, C, make sure you have a value for A or A,B otherwise it will do a scan of all index pages. Ideally, it should only read one page at each level of the index tree. 3. Avoid updating indexed columns. When you know that a row's indexed columns never change, don't update them. i.e. never do an UPDATE table SET * = record.* if you know that the indexed fields never change. This avoids locking any index pages. 4. Add index. Sometimes, you have to add an index to avoid index scans or sequential scans. Of course, it is always better to use an existing index and you can sometimes do this by using ORDER BY on SELECTs even if you are not bothered about the order or it is a singleton select. 5. Avoid deadlock. Sometimes, this error occurs when you have a deadlock across tables, or rows within one table. If it's across tables, change the application so that tables are updated in a certain order (e.g. alphabetically or master before detail). If it's across rows, then order your rows before the insert/update (we use a simple bubble sort on an array). I hope some of this helps. > Russ Kahler > Program/Consultant > on!contact Software David W Chan Objective Research Ltd dwc@cix.compulink.co.uk