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 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 the 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:

Netezza®

Netezza servers are designed for Data Warehouse applications, not for OLTP applications: Concurrent data access is not the best thing that a Netezza server can do. There are a bunch of limitations that you must be aware of. You must not expect to be able to migrate an existing OLTP application running against Informix or Oracle to a Netezza database server. The purpose of a Netezza-based application is mostly to do queries, with few insert or updates. Typically a Netezza database is fed with data by using tools such as nzload, not by Genero BDL programs.

Some limitations of Netezza:

Solution

Understand that the main difference with Informix is that Netezza is not good at concurrent data modification. Note also that readers do not have to wait for writers in Netezza.

Genero applications should mainly do queries against a Netezza server. You must review your program logic that modifies data, having in mind that only one process can modify a table at the time. Note however, that if you write short transactions this is not visible to the end users, except that an INSERT / UPDATE / DELETE of a single row takes more time than with another database server.

The SET ISOLATION TO ... Informix syntax is replaced by SET TRANSACTION ISOLATION LEVEL ... in Netezza. However, only the REPEATABLE READ level is supported with Netezza.

The next table shows the isolation level mappings done by the Netezza database driver:

Table 1. Isolation level mappings done by the Netezza database driver
SET ISOLATION instruction in program Native SQL command
SET ISOLATION TO DIRTY READ Not supported (SQL Error)

SET ISOLATION TO COMMITTED READ

[READ COMMITTED] [RETAIN UPDATE LOCKS]

Not supported (SQL Error)
SET ISOLATION TO CURSOR STABILITY Not supported (SQL Error)
SET ISOLATION TO REPEATABLE READ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Important: Since Netezza does not support the lock wait mode, you must check that your programs do not include a SET LOCK MODE instruction. This instruction will fail with error -6370 if it is executed when connected to Netezza.

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