Search code examples
informixdatabase-deadlocks

Informix - deadlock scenario involving single table

I am looking for a way to force a deadlock on Informix IDS 11.50. The solution has to fulfill the following conditions:

  • Only one table involved,
  • Isolation level: COMMITTED READ,
  • ROW level locking,
  • Two threads, but only one can access the row(s) modified by the other one.

I came up with the solution that satisfies every condition but the last one:

Table t1 is created and populated as follows:

CREATE TABLE t1
(
    id SERIAL PRIMARY KEY,
    name CHAR(20)
)
LOCK MODE ROW;

CREATE INDEX idx_name on t1 (name);
INSERT INTO t1 (name) VALUES ('A');
INSERT INTO t1 (name) VALUES ('B');

On 2 separate dbaccess consoles (with lock mode set to wait for 15 seconds) I execute the following commands:

dbaccess #1: BEGIN;
dbaccess #1: UPDATE t1 SET name = name WHERE name = 'A';
dbaccess #2: BEGIN;
dbaccess #2: UPDATE t1 SET name = name WHERE name = 'B';
dbaccess #1: SELECT * FROM t1;
dbaccess #2: SELECT * FROM t1; -- 143: ISAM error: deadlock detected

These threads are deadlocking cause the SELECTs are waiting for the other thread to commit. This solution is not good cause both threads select the rows updated by the other thread which violates the fourth condition. Can anyone think of a way to improve this solution or provide other that meets all the criteria?

Solution

  • The fourth condition denies the possibility of deadlock.

    Deadlock occurs when two threads are each waiting on the other to release a resource (while holding a resource that the other wants), but your fourth condition precludes that possibility.