Data consistency and concurrency management

Data consistency involves readers which 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 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:

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

Defaults:

The default isolation level is read committed.
The default lock wait mode is "not wait".
The default locking granularity is per page.

Sybase ASE:

As in Informix, Sybase ASE 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 or the lock timeout occurred. The lock granularity is at the row or table level. For more details, see Sybase ASE's Documentation.

Control:

The lock wait mode can be controlled with: SET LOCK {WAIT seconds | NOWAIT}
Isolation level: Can be set with: SET TRANSACTION ISOLATION LEVEL = {0|1|2|3}
Locking granularity: Row level.
Explicit locking: SELECT ... FOR UPDATE

Defaults:

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

Solution

The SET ISOLATION TO ... Informix syntax is replaced by SET TRANSACTION ISOLATION LEVEL ... in Sybase ASE. The next table shows the isolation level mappings done by the Sybase ASE database driver:

Table 1. Isolation level mappings done by the Sybase ASE database driver
SET ISOLATION instruction in program	Native SQL command
SET ISOLATION TO DIRTY READ	SET TRANSACTION ISOLATION LEVEL = 0
SET ISOLATION TO COMMITTED READ [READ COMMITTED] [RETAIN UPDATE LOCKS]	SET TRANSACTION ISOLATION LEVEL = 1
SET ISOLATION TO CURSOR STABILITY	SET TRANSACTION ISOLATION LEVEL = 2
SET ISOLATION TO REPEATABLE READ	SET TRANSACTION ISOLATION LEVEL = 3

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.

The SET LOCK MODE TO ... Informix syntax is replaced by SET LOCK ... in Sybase ASE. If SET LOCK MODE TO WAIT is used in programs (i.e. wait forever), the driver will simulate this with a SET LOCK WAIT 5000 in Sybase ASE:

Table 2. SET LOCK MODE instruction for Sybase ASE
SET LOCK MODE instruction in program	Native SQL command
SET LOCK MODE TO NOT WAIT	SET LOCK NOWAIT
SET LOCK MODE TO WAIT n	SET LOCK WAIT n
SET LOCK MODE TO WAIT	SET LOCK WAIT 5000

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

Data consistency and concurrency management

Informix®:

Sybase ASE:

Solution

Informix^®: