Re: *** HELP - Index Locks are creating DEADLOCKS ***

By the way, you have not, in this article, descibed any deadlock situations. Thomas Schiele writes: >Informix: Locks and Indexes > >I've noticed the following points using Informix Version 4: >1. Even if you use the option 'LOCK MODE ROW' in the SQL statement 'CREATE > TABLE', the database will use table locks! There is no row level > locking. > Row level locking works only if you use an index on that table. These two statements are, of course, completely false. There most certainly is row level locking and there is not a requirement to have an index to use row level locking. >2. If you delete a row, the next row in the index (!) will be locked too. This is correct. As explained in the manual (and repeated below), this is to prevent the insertion of a duplicate key value before the transaction ends. Informix recognizes the drawbacks to this implementation and are planning an alternate method for out 6.0 release. >3. A row can be inserted only if the next tuple in the index (!) is not > locked. Yes. The goal here was, again, to prevent inserting duplicate keys prior to the completion of another transaction. That probably requires a bit of explanation. If a key value is locked, another user cannot check its value and thus cannot determine if it has the same value as the key it is inserting. Inserting a duplicate key cannot be permitted for unique indexes; for duplicate indexes, it requires modifying the (locked) key entry, to add the new rowid to the list of rowids for that key. In either case, the insert cannot be allowed to succeed. By locking the adjacent key, we prevent this insert. Unfortunately, that also prevents inserting the "next value up" from the locked key. > Row level locking works only if you use an index on that table. Again, this is not true. >I've found in the complete documentation for Informix one (!) remark only: > "A process running under Informix-OnLine acquires an exclusive > lock on a row before it updates a table, deletes a row, or inserts > a row. A exclusive lock prevents another process from modifying the > contents of the row before the lock is released. The delete process > also locks the next, or adjacent key, value in the index. This > feature ensures that another process does not insert a row with > the same value as the deleted key until the lock is released. The > insert process acquires an exclusive lock on the row and checks to > make sure that the next, or adjacent key, values are not locked. If > the values are locked, then the insert fails." (Informix-OnLine > Administrator's Guide) > >My remarks: >1. Row level locking works with indexes only. You have to use an index > even if you don't need it. Repeating it does not make it true. THIS IS NOT TRUE. What you may be seeing is this: using higher isolation levels (REPEATABLE READ) will cause table level locks to be placed when queries do not use indexes. This is actually an optimization, believe it or not: since RR locks all rows examined, and a non-indexed read must examine every row (sequential scan), the alternative is to lock each individual row. In such a case, a table lock is much less costly. Perhaps you could describe your application, in order to allow others to understand what you are trying to accomplish. You would then be more likely to receive useful suggestions. > It seems that locking is implemented via ISAM index locking. Not true at all. >2. There are conflicts between insert and/or delete operations because > row level locks affect neighbour tuples in the index (side effect > called 'feature'!). Deleting the last row in the index even locks > the index to the end, no more rows can be inserted. This is basically tru, and is an unpalatable side-effect of this locking strategy. Again, Informix recognizes this and is re-engineering it for our 6.0 release. >3. There are no hints in the documentation which strategy to use for > defining an index in order to have correct row level locking. That is because it is not a requirement to have indexes in order to have row level locking. > For example it is not defined, whether one must use all fields of > all WHERE clauses (!) of a whole application (!!) to have correct row > level locking. It is even not defined how locking works if there is > more than one index on a table. > >>>Can anyone help: >...I need some help too (or is this really a 'feature' of informix?)... Again, if you could provide some idea of what your application is doing (esp. what isolation levels are used, logging on or off, etc), it would be easier to provide advice. Dave -- Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney