Data consistency and concurrency management

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 according to the 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.

ORACLE

When data is modified, exclusive locks are set and held until the end of the transaction. For data consistency, ORACLE uses a multi-version consistency model: a copy of the original row is kept for readers before performing writer modifications. Readers do not have to wait for writers as in Informix. The simplest way to think of Oracle's implementation of read consistency is to imagine each user accessing a private copy of the database, hence the multi-version consistency model. The lock wait mode cannot be changed session wide as in Informix; the waiting behavior can be controlled with a SELECT FOR UPDATE NOWAIT only. Locks are set at the row level in ORACLE, and this cannot be changed.

Control:

• Lock wait mode (on SELECT only): SELECT ... FOR UPDATE NOWAIT
• Isolation level: SET TRANSACTION ISOLATION LEVEL TO ...
• Explicit exclusive lock: SELECT ... FOR UPDATE [NOWAIT]

Defaults:

• The default isolation level is Read Committed ( readers cannot see uncommitted data, no shared lock is set when reading data ).

The main difference between Informix and ORACLE is that readers do not have to wait for writers in ORACLE.

Solution

The SET ISOLATION TO ... Informix syntax is replaced by ALTER SESSION SET ISOLATION_LEVEL ... in Oracle. The next table shows the isolation level mappings done by the database driver:

ORACLE does not provide a dirty read mode, the (session wide) lock wait mode cannot be changed and the locking precision is always at the row level. Based on this, it is recommended that you work with Informix in the read committed isolation level (default), make processes wait for each other (lock mode wait), and use the default page-level locking granularity.

See the Informix and ORACLE documentation for more details about data consistency, concurrency and locking mechanisms.