Re: Lock mechanism of Informix

> Hi > > My name is Pedro Carvalho and I am a member of a software house in > Portugal. I would like to know someone on the net who works with > Informix to discuss the lock mechanism of this DBMS. The Informix DBMS > is the only DBMS I know (I also work with Oracle, Ingres and RDB) that > locks three contiguous records when we try to update a single record. > The situation I have is this : > > - I choose row level locking > - There are several users accessing the database at the same time > - In particular, there are two people accessing two records for > update , let's say, record 1 and record 2 with contiguous keys. > What is expected to happen is that these 2 people could do the > update operation at the same time, but what really happens is > that one of them has to wait until the other completes her work > because Informix locks three records (the record who is being > updated and the two contiguous records). > > Pedro Carvalho Your problem may stem from a couple of issues in Informix. The first is if you are attempting to update any row in your table then Informix will place a key level lock on the indexes for the appropriate key values as well as a row level lock. This can often result in other programs running up against a key lock when trying to lock their own row. The way to get round this problem is - don't select for update the entire row, just select the attributes you need for this update. If only part of the row is selected for update Informix does not place key locks on keys which are made up of attributes not in the select or update statement. I don't know Oracle, etc but they must do something similar with key locks so as to maintain their own indexes. The second is a true problem with the current version of Informix locking which will be changed in V6.0 supposedly. On delete you need to delete the row and its index entries but until the commit occurs you have to be able to guarantee to be able to rollback the delete. In other words you cannot allow another program to insert a row with the same Primary key and/or unique index values. Informix uses an adjacent key lock approach to solve this problem. It places a lock on the next higher row and the next higher key entries for the row being deleted. Though this solves the rollback problem it creates a bigger concurrency issue, in that unrelated rows and index entries are locked. This is bad but it gets worse - when you try and insert or update a row no values can be entered that will insert a row or index in between the previous row and the next higher. For example:- PK UK Row 1 10 F Row 2 20 D Row 3 30 B If Row 2 is deleted a lock is placed on Row 3 and on UK index entry F. Now it is not possible to insert any row with primary keys between 10 and 30 or Unique index values between B and F, nor is it possible to work with Row 1 or Row 3, until the delete is committed. You can get the same problem when updating rows when you are changing the index key values which, of course, requires a delete and insert of the key value. In practice this doesn't often cause a problem as access to the database is generally fairly random. Also by reducing lock window time to a minimum, which is good design practice anyway, and possibly by setting lock mode to wait ? seconds you can effectively make the problem pretty well disappear. 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 --------------------------------------------------------------------