Data consistency and concurrency

Data consistency involves readers that want to access data currently modified by writers and concurrency data access involves several writers accessing the same data for modification. Locking granularity defines the amount of data concerned when a lock is set (row, page, table, ...).

Informix®

Informix uses a locking mechanism to manage data consistency and concurrency. When a process modifies data with UPDATE, INSERT or DELETE, an exclusive lock is set on the affected rows. The lock is held 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 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:

Defaults:

IBM® DB2®

As in Informix, IBM DB2 uses locks to manage data consistency and concurrency. The database manager sets exclusive locks on the modified rows and shared locks when data is read, according to the isolation level. The locks are held until the end of the transaction. When multiple processes want to access the same data, the latest processes must wait until the first finishes its transaction. The lock granularity is at the row or table level. For more details, see DB2's Administration Guide, "Application Consideration".

Control:

Defaults:

Solution

The SET ISOLATION TO ... Informix syntax is replaced by an ODBC API call setting the SQL_ATTR_TXN_ISOLATION connection attribute. The next table shows the isolation level mappings done by the database driver:

Table 1. Isolation level mappings done by the IBM DB2 UDB database driver
SET ISOLATION instruction in program ODBC SQL_ATTR_TXN_ISOLATION connection attribute
SET ISOLATION TO DIRTY READ SQL_TXN_READ_UNCOMMITTED

SET ISOLATION TO COMMITTED READ

[READ COMMITTED] [RETAIN UPDATE LOCKS]

SQL_TXN_READ_COMMITTED
SET ISOLATION TO CURSOR STABILITY SQL_TXN_REPEATABLE_READ
SET ISOLATION TO REPEATABLE READ SQL_TXN_SERIALIZABLE

For portability, it is recommended that you work with Informix in the read committed isolation level, to make processes wait for each other (lock mode wait) and to create tables with the "lock mode row" option.

See Informix and IBM DB2 documentation for more details about data consistency, concurrency and locking mechanisms.