ANSI Read Committed and HCL OneDB Committed Read isolation

When a program requests the ANSI Read Committed or HCL OneDB™ Committed Read isolation level, the database server guarantees that it never returns a row that is not committed to the database. This action prevents reading data that is not committed and that is subsequently rolled back.

ANSI Read Committed or HCL OneDB Committed Read is implemented simply. Before it fetches a row, the database server tests to determine whether an updating process placed a lock on the row; if not, it returns the row. Because rows that have been updated (but that are not yet committed) have locks on them, this test ensures that the program does not read uncommitted data.

ANSI Read Committed or HCL OneDB Committed Read does not actually place a lock on the fetched row, so this isolation level is almost as efficient as ANSI Read Uncommitted or HCL OneDB Dirty Read. This isolation level is appropriate to use when each row of data is processed as an independent unit, without reference to other rows in the same or other tables.

Locking conflicts can occur in ANSI Read Committed or HCL OneDB Committed Read sessions, however, if the attempt to place the test lock is not successful because a concurrent session holds a shared lock on the row. To avoid waiting for concurrent transactions to release shared locks (by committing or rolling back), HCL OneDB supports the Last Committed option to the Committed Read isolation level. When this Last Committed option is in effect, a shared lock by another session causes the query to return the most recently committed version of the row.

The Last Committed feature can also be activated by setting the USELASTCOMMITTED configuration parameter to 'COMMITTED READ' or to 'ALL', or by setting USELASTCOMMITTED session environment option in the SET ENVIRONMENT statement in the sysdbopen( ) procedure when the user connects to the database. For more information about the Last Committed option to the ANSI Read Committed or HCL OneDB Committed Read isolation levels, see the description of the SET ISOLATION statement in the HCL OneDB Guide to SQL: Syntax. For information about the USELASTCOMMITTED configuration parameter, see the HCL OneDB Administrator's Reference.