Concurrency management

Data consistency and concurrency concepts

Informix®

Informix uses a locking mechanism to handle data consistency and concurrency. When a process changes database information with UPDATE, INSERT or DELETE, an exclusive lock is set on the touched rows. The lock remains active until the end of the transaction. Statements performed outside a transaction are treated as a transaction containing a single operation and therefore release the locks immediately after execution. SELECT statements can set shared locks, depending on isolation level. In case of locking conflicts (for example, when two processes want to acquire an exclusive lock on the same row for modification, or when a writer is trying to modify data protected by a shared lock), the behavior of a process can be changed by setting the lock wait mode.

Control:

• Lock wait mode: SET LOCK MODE TO ...
• Isolation level: SET ISOLATION TO ...
• Locking granularity: CREATE TABLE ... LOCK MODE {PAGE|ROW}
• Explicit exclusive lock: SELECT ... FOR UPDATE

Defaults:

• The default isolation level is READ COMMITTED.
• The default lock wait mode is NOT WAIT.
• The default locking granularity is PAGE.

Locking granularity

With short transactions / lock mode wait / isolation committed read (as recommended in Concurrent data access), the locking granularity does not have to be at the row level.

To improve performance with IBM® Informix, use the LOCK MODE PAGE locking level, which is the default.

However, if the application requires row-level locking, use the LOCK MODE ROW clause, or define the DEF_TABLES_LOCKMODE configuration parameter to ROW, in the onconfig file of the IDS server.

LAST COMMITTED option

IBM Informix IDS 11 has introduced the LAST COMMITTED option for the COMMITTED READ isolation level, which makes IDS behave like other database servers using row-versioning.

With the LAST COMMITTED option, when a lock is set on a row by another process, a SELECT statement will return the most recently committed version of that row, rather than wait for a lock to be released, or get an SQL error when the lock wait timeout occurs.