Re: Will key locking strategy in OnLine be changed?

> Not long ago I read an article in this group about the strategy used > in OnLine whereby locks are placed on adjacent keys when a row has been > inserted or deleted. Someone mentioned that they heard that this will > be changed in a future release. > > What I would like to know is if anyone can confirm or deny that this > will be changed? And if so, when and what to? > > My problem is that I want to be able to ensure at a certain point in a > transaction that a row can be deleted later in the transaction. I thought > I would be able to do this by getting an exclusive lock on the row. This > will not work if another user inserts a row with a key between the one I > want to delete and the next 'higher' key, because a lock will be placed > on the new key, thereby causing my delete to fail. If Informix changes > this strategy it may make it easier for me. Stephen, Two things. Firstly the intention was to change the strategy in V6.0, whether that will actually happen I have been unable to confirm. Secondly I think you misunderstood my and other peoples explanations of the problem the current strategy creates. If as a user you select for update a row within a transaction and the row is returned to you. You WILL have an exclusive lock on that row until you commit or rollback the transaction. If sombody else inserts a row between your row and the row below a lock will be taken on the row inserted no lock will be placed on your row. The problem the strategy creates is slightly different. The strategy reduces the amount of concurrent activity that can occur on the database by placing locks on keys not involved in a transaction. The result is that users attempting to select for update a row may be refused because a lock exists on the key of the row on investigation they find that nobody is using the data and they get upset. The other problem is similar - a user deletes a row but waits before committing the transaction, any other user attempting to insert a row from the key value of the deleted row to the key value of the next higher row will be rejected as the system will find a lock exists on the key value of the row being inserted. Again users investigate find no row and get upset. But it needs to be clear that nothing is wrong with the Begin Commit cycle or with placing locks on existing rows to gain exclusive access. Also nothing is wrong with the programs that you are writing to access the database. All that happens is sometimes an attempt to do a transaction will fail because of a lock that exists where no other user is apparently working, not actually any different from meeting a true lock, it just reduces the apparent concurrency of the system. The new system should remove the problem - probably by creating a lock that has the key value of the deleted row. This will increase the concurrency but not remove the possibility of hitting a lock. A couple of database access techniques also increase concurrency and if concurrency is a problem these should be considered. Hope this helps Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------