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

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. 2. If you delete a row, the next row in the index (!) will be locked too. 3. A row can be inserted only if the next tuple in the index (!) is not locked. Row level locking works only if you use an index on that table. 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. It seems that locking is implemented via ISAM index locking. 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. 3. There are no hints in the documentation which strategy to use for defining an index in order to have correct 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?)... cu Thomas ----- Thomas G. Schiele Winterthurerstrasse 297 CH-8057 Zuerich Switzerland schiele@bernina.ethz.ch