Re: OnLine V5.06 locking problems

Sally Woolrich wrote: > > We are having lots of locking problems with an OnLine database. The > application (hugh!) works fine with SE and we have use DIRTY READ, LOCK > MODE WAIT 20 and ROW level locking to try to make OL behave like SE and > minimise the lock problems. > > This only starts being a problem with 150+ users. > > There are *plenty* of Informix locks! Sally, Very little to go on but if you wanted a guess: If your users are doing a lot of inserts/deletes into the same table you may be running into the adjacent key lock problem in Online V5 and below. Fixed by going to key value locks in V6+. Basically on deleting a row a key lock is taken on the next higher key until the commit occurs. This stops any other process from inserting any row with a key range between the key value inserted and the next higher. This is often both a large range and precisely the value range covering the row some other user is trying to enter. Also you cannot delete or update the row with the next higher key value. The effect is the same on updating a key value in a row as it does a delete of the old value and an insert on the new. The lock is held on the next higher value than the old key value. Informix did this because they couldn't hold a lock on the key value removed. So to stop anyone inserting the same value when the transaction might rollback they locked the whole range. Cheers - Jim -- ------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!