Concurrency management
Data consistency and concurrency concepts
- Data Consistency applies to situations when readers want to access data currently being modified by writers.
- Concurrent Data Access applies to situations when several writers are accessing the same data for modification.
- Locking Granularity defines the amount of data concerned when a lock is set (for example, 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, depending on 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:
- 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
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. RegularSELECT
statement 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 onlyINSERT
statements in a transaction block. UPDATE/DELETE
statements lock the entire table, but don't preventSELECT
statements. Other processes doingUPDATE/DELETE
statements will wait until the first session has committed.- Netezza (V6) understands the
SET TRANSACTION ISOLATION
statement, but currently implements only theSERIALIZABLE
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.
It is recommended that Genero applications mainly do queries against a Netezza server. You must review your program logic that modifies data,
keeping 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 table shows the 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 |
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.