Data consistency and concurrency

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:

• 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 per page.

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:

• An application can only execute one cursor (or statement handle) at a time.
• Singular data modification statements (INSERT, UPDATE, DELETE) are much slower than with traditional OLTP database servers. Netezza is, however, very good when it comes to loading a huge amount of data with special tools like the nzload utility.
• SELECT ... FOR UPDATE is not supported. Regular SELECTs never lock rows.
• Locks can only be set for an entire table with LOCK TABLE.
• A maximum of 31 concurrent INSERT processes are allowed (Netezza V6), and there must be only INSERTs in a transaction block.
• UPDATE/DELETE statements lock the entire table, but don't prevent SELECTs. Other processes doing UPDATEs/DELETEs will wait until the first session has committed.
• Netezza (V6) understands the SET TRANSACTION ISOLATION statement, but currently implements only the SERIALIZABLE level.
• There is no way to define the LOCK WAIT mode. With Netezza, processes always wait for locks to be released.

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:

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