ANSI Serializable, ANSI Repeatable Read, and HCL Informix Repeatable Read isolation

Where ANSI Serializable or ANSI Repeatable Read are required, a single isolation level is provided, called HCL Informix® Repeatable Read. This is logically equivalent to ANSI Serializable. Because ANSI Serializable is more restrictive than ANSI Repeatable Read, HCL Informix Repeatable Read can be used when ANSI Repeatable Read is desired (although HCL Informix Repeatable Read is more restrictive than is necessary in such contexts).

The Repeatable Read isolation level asks the database server to put a lock on every row the program examines and fetches. The locks that are placed are shareable for an ordinary cursor and promotable for an update cursor. The locks are placed individually as each row is examined. They are not released until the cursor closes or a transaction ends.

Repeatable Read allows a program that uses a scroll cursor to read selected rows more than once and to be sure that they are not modified or deleted between readings. (SQL programming describes scroll cursors.) No lower isolation level guarantees that rows still exist and are unchanged the second time they are read.

Repeatable Read isolation places the largest number of locks and holds them the longest. Therefore, it is the level that reduces concurrency the most. If your program uses this level of isolation, think carefully about how many locks it places, how long they are held, and what the effect can be on other programs.

In addition to the effect on concurrency, the large number of locks can be a problem. The database server records the number of locks by each program in a lock table. If the maximum number of locks is exceeded, the lock table fills up, and the database server cannot place a lock. An error code is returned. The person who administers the Informix database server system can monitor the lock table and tell you when it is heavily used.

The isolation level in an ANSI-compliant database is set to Serializable by default. The Serializable isolation level is required to ensure that operations behave according to the ANSI standard for SQL.