home *** CD-ROM | disk | FTP | other *** search
- Newsgroups: comp.databases.informix
- Path: sparky!uunet!mcsun!chsun!bernina!schiele
- From: schiele@bernina.ethz.ch (Thomas Schiele)
- Subject: Re: *** HELP - Index Locks are creating DEADLOCKS ***
- Message-ID: <1992Nov9.111120.25041@bernina.ethz.ch>
- Organization: Swiss Federal Institute of Technology (ETH), Zurich, CH
- References: <4309@copper.Denver.Colorado.EDU>
- Date: Mon, 9 Nov 1992 11:11:20 GMT
- Lines: 51
-
- 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
-