Concurrent Access to Tables with Exclusive Locks

After the LOCK TABLE statement with the IN EXCLUSIVE MODE option executes successfully, no other user can obtain a lock on the specified table. When you attempt a DDL operation on that table, however, you might receive RSAM error -106 if the same table is being accessed by a concurrent session (for example, by opening a cursor). This error can also affect implicit locks that certain DDL statements place on tables automatically.

This is possible because table locks do not preclude table access. An exclusive lock prevents other users from obtaining a lock, but it cannot prevent them from opening the table for write operations that wait for the exclusive lock to be released, or for Dirty Read operations on the table. You can set the IFX_DIRTY_WAIT environment variable to specify that the DDL operation wait for a specified number of seconds for Dirty Read operations to commit or rollback.

When one or more rows in a table are locked by an exclusive lock, the effect on other users partly depends on their transaction isolation level. Other users in all isolation levels except the Dirty Read isolation level might encounter locking errors, such as transactions that fail because the lock was not released within a specified time limit, or a deadlock situation.

On tables where row-level locking affects some of the rows, the risk of locking conflicts can be reduced by enabling transactions to read the most recently committed version of the data in the row-level locked table, rather than waiting for the transaction that holds the lock on that row to be committed or rolled back. This can be accomplished in several different ways, including these:

From an individual session, issue this SQL statement

 SET ISOLATION TO COMMITTED READ LAST COMMITTED;

For all sessions using Committed Read or Read Committed isolation levels, set the USELASTCOMMITTED configuration parameter to 'ALL' or to 'COMMITTED READ', or else issue the SET ENVIRONMENT USELASTCOMMITTED statement with 'ALL' or 'COMMITTED READ' as the session environment option.
For all sessions using Dirty Read or Read Uncommitted isolation levels, set the USELASTCOMMITTED configuration parameter to 'ALL' or to 'DIRTY READ', or else issue the SET ENVIRONMENT USELASTCOMMITTED statement with 'ALL' or 'DIRTY READ' as the session environment option.
For users for whom a user.sysdbopen( ) procedure is defined in the database, the DBA can define that procedure to include the SET ENVIRONMENT USELASTCOMMITTED statement with 'ALL' or 'COMMITTED READ' as the session environment option, and also issue the SET ISOLATION statement to set Committed Read as the isolation level.
For users for whom no user.sysdbopen( ) procedure exists in the database, the DBA can define a PUBLIC.sysdbopen procedure that specifies the same SET ENVIRONMENT USELASTCOMMITTED and SET ISOLATION statements.

This LAST COMMITTED isolation feature is useful only when row-level locking is in effect, rather than when another session holds an exclusive lock on the entire table. This feature is disabled for the specified table when LOCK TABLE applies a table-level lock. See The LAST COMMITTED Option to Committed Read for more information about this LAST COMMITTED feature for concurrent access to tables in which some rows are locked by exclusive locks, and for restrictions on the kinds of tables that can support this feature.