Re: *** HELP - Index Locks are creating DEADLOCKS ***
Informix: Locks and Indexes
Thanks for the fast response and the competent feedback.
David Kosenko writes:
>>2. There are conflicts between insert and/or delete operations because
>> row level locks affect neighbour tuples in the index (side effect
>> called 'feature'!). ...
>> ...
>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.
Thank you for the information about the 6.0 release :-)
>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.
Ok. We have an application where users (processes) do not only selects
but also updates, inserts and deletes in transaction mode. In order to
demonstrate the problem let's use the following simplified example:
The test database:
CREATE DATABASE test WITH LOG;
CREATE TABLE rel (
id CHAR(1),
data CHAR(10)
)
LOCK MODE ROW;
INSERT INTO rel VALUES ("1", "one");
INSERT INTO rel VALUES ("2", "two");
INSERT INTO rel VALUES ("3", "three");
INSERT INTO rel VALUES ("4", "four");
INSERT INTO rel VALUES ("5", "five");
Example 1:
We use the test database.
User A does the following and stays in transaction mode:
BEGIN WORK;
DELETE FROM rel WHERE id = "2";
User B does the following:
BEGIN WORK;
DELETE FROM rel WHERE id = "4"; result: 244: Could not do a physical-order read to fetch next row.
107: ISAM error: record is locked. It doesn't seem to work as expected.
Example 2:
We use our test database.
We install an index in our test database:
CREATE INDEX relind ON rel (
id
);
User A does the following and stays in transaction mode (same as above):
BEGIN WORK;
DELETE FROM rel WHERE id = "2";
User B does the following::
BEGIN WORK;
DELETE FROM rel WHERE id = "4"; result: 1 row(s) deleted.
It works as expected/
(remark: the delete statements in the examples above are used to place
exclusive locks.)
>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. In
>such a case, a table lock is much less costly.
Just some questions:
1. Does this mean that the delete statement of user A in example 1 places
an exclusive lock on the table?
2. Or does this mean that the delete statement (or select etc.) places
locks on all rows (shared or exclusive locks) before (!) evaluating
the WHERE clause? (I can't imagine that :-) )
Or does this even mean that informix uses the "optimization" mentioned
above to prevent exaclty that effect?
3. Does this mean that I have to use an index as demonstrated in example 2
to turn off the "optimization" with table locks in order to get row
level locking?
4. If question 3 can be answered with yes, then why is my statement
"Row level locking works only if you use an index on that table"
completely false?
cu THGSCH
---------------------------------------------
Thomas G. Schiele
Winterthurerstrasse 297
CH-8057 Zuerich
Switzerland