The LAST COMMITTED Option to Committed Read

Use the LAST COMMITTED keyword option of the Committed Read isolation level to reduce the risk of exclusive row-level locks held by other sessions either causing applications to fail with locking errors, or preventing applications from reading a locked row until after a concurrent transaction is committed or rolled back.

In contexts where an application attempts to read a row on which another session holds an exclusive lock, these keywords instruct the database server to return the most recently committed version of the row, rather than wait for the lock to be released.

This feature takes effect implicitly in all user sessions that use the Committed Read isolation level of the SET ISOLATION statement, or that use the Read Committed isolation level of the ANSI/ISO-compliant SET TRANSACTION statement, under any of the following circumstances:
  • if the USELASTCOMMITTED configuration parameter is set to 'COMMITTED READ' or to 'ALL'
  • if the SET ENVIRONMENT statement sets the USELASTCOMMITTED session environment variable to 'COMMITTED READ' or to 'ALL'.
This feature also takes effect implicitly in all user sessions that use the Dirty Read isolation level of the SET ISOLATION statement, or that use the Read Uncommitted isolation level of the ANSI/ISO-compliant SET TRANSACTION statement, under any of the following circumstances:
  • if the USELASTCOMMITTED configuration parameter is set to 'DIRTY READ' or to 'ALL'
  • if the SET ENVIRONMENT statement sets the USELASTCOMMITTED session environment variable to 'DIRTY READ' or to 'ALL'.

Enabling this feature cannot eliminate the possibility of locking conflicts, but they reduce the number of scenarios in which other sessions reading the same row can cause an error. The LAST COMMITTED keywords are only effective with concurrent read operations. They cannot prevent locking conflicts or errors that can occur when concurrent sessions attempt to write to the same row.

This feature has no effect on Committed Read or Dirty Read behavior in contexts where no “last committed" version of the table is available, including these:
  • The database does not support transaction logging
  • The table was created with the LOCK MODE PAGE keywords, or has been altered to have a locking mode of PAGE
  • The IFX_DEF_TABLE_LOCKMODE environment variable is set to ‘PAGE'
  • The DEF_TABLE_LOCKMODE configuration parameter is set to ‘PAGE'
  • The LOCK TABLE statement has explicitly set an exclusive lock on the table
  • An uncommitted DDL statement has implicitly set an exclusive lock on the table
  • The table is a system catalog table on which an uncommitted DDL statement has implicitly set an exclusive lock
  • The table has columns of complex data types or of user-defined data types
  • The table is a RAW table
  • A DataBlade® module is accessing the table
  • The table was created using the Virtual Table Interface.
User-defined access methods are not required to support the LAST COMMITTED feature.

The scope of LAST COMMITTED semantics is neither statement-based nor transaction-based. This isolation level has the same instant-in-time scope that the Committed Read isolation level has without the LAST COMMITTED option. For example, when a query is executed twice within a single transaction with LAST COMMITTED in effect, different results might be returned by the same query, if other DML transactions that were operating on the same data are committed in the interval between the two submissions of the query. This instantaneous nature of the semantics of Committed Read and of Committed Read Last Committed exactly implements the ANSI/ISO Read Committed isolation level.

The LAST COMMITTED feature does not support reading through table-level locks. If the access plan for a query that uses the LAST COMMITTED feature encounters a table-level lock in a table or index that it needs to access, the query will return the following error codes:

SQL error code:
252: Cannot get system information for table.
ISAM error code:
113: ISAM error: the file is locked.