| SQL adaptation guide For Genero db 3.6x, 3.8x / Database 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 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:
Defaults:
Genero db:
Genero db does not use the same locking mechanism as Informix; Genero db is a lock-avoidance database and thus behaves differently to other database regarding concurrency.
Control:
The following transaction control instructions exist in Genero db:
Defaults:
In order to reduce locking, Genero db supports "commutative update" of the same row without the need to wait for each other. In most cases the result of concurrent transactions will be the same; however, you should review programs if some part of the code relies on the fact that an UPDATE statement sets a lock on the row during the transaction, in order to avoid other db sessions being able to read / modify the row ( like Informix does in READ COMMITTED isolation level.) Work-arounds are discussed in the Solutionsection.
You can use the same transaction control instructions and update clauses as in Informix:
The SET ISOLATION TO ... Informix syntax is replaced by SET TRANSACTION ISOLATION LEVEL ... in Genero db. The next table shows the isolation level mappings done by the database driver:
| SET ISOLATION instruction in program | Native SQL command |
|---|---|
| SET ISOLATION TO DIRTY READ | SET TRANSACTION ISOLATION LEVEL READ COMMITTED |
|
SET ISOLATION TO COMMITTED READ [READ COMMITTED] [RETAIN UPDATE LOCKS] |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED |
| SET ISOLATION TO CURSOR STABILITY | SET TRANSACTION ISOLATION LEVEL READ COMMITTED |
| SET ISOLATION TO REPEATABLE READ | SET TRANSACTION ISOLATION LEVEL SERIALIZABLE |
Since Genero db may not set locks on modified rows during a transaction, you must check that your code does not rely of the fact that UPDATE or DELETE statements set locks on the modified rows to prevent other db session from accessing the rows. For example, see the next SQL code sequence, to be run in READ COMMITTED isolation level:
BEGIN WORK UPDATE incr SET value = value + 1 WHERE key = 45 SELECT value FROM incr WHERE key = 45 COMMIT WORK
While in Informix the UPDATE statement will set a lock and prevent other db sessions from accessing the row for modification until the transaction ends, Genero db (allowing commutative updates) will not set a lock, thus letting other db sessions increment the row, while the first transaction is still under progress. As result, a concurrent transaction may increment the column before the first sessions could actually read the value it has incremented itself, and thus get the last value incremented by the concurrent transaction.
To workaround this behavior, you can:
BEGIN WORK SELECT value FROM incr WHERE key = 45 FOR UPDATE UPDATE incr SET value = value + 1 WHERE key = 45 SELECT value FROM incr WHERE key = 45 COMMIT WORK