Row and key locks

You can lock one row of a table. A program can lock one row or a selection of rows while other programs continue to work on other rows of the same table.

Row and key locking are not the default behaviors. You must specify row-level locking when you create the table. The following example creates a table with row-level locking:

CREATE TABLE tab1 
(
col1...
) LOCK MODE ROW;

If you specify a LOCK MODE clause when you create a table, you can later change the lock mode with the ALTER TABLE statement. The following statement changes the lock mode on the reservations table to page-level locking:

ALTER TABLE tab1 LOCK MODE PAGE

In certain cases, the database server has to lock a row that does not exist. To do this, the database server places a lock on an index-key value. Key locks are used identically to row locks. When the table uses row locking, key locks are implemented as locks on imaginary rows. When the table uses page locking, a key lock is placed on the index page that contains the key or that would contain the key if it existed.

When you insert, update, or delete a key (performed automatically when you insert, update, or delete a row), the database server creates a lock on the key in the index.

Row and key locks generally provide the best performance overall when you update a relatively small number of rows because they increase concurrency. However, the database server incurs some overhead in obtaining a lock.

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 whose isolation levels is not Dirty Read might encounter transactions that fail because the exclusive lock was not released within a specified time limit.

For Committed Read or Dirty Read isolation level operations that attempt to access tables on which a concurrent session has set exclusive row-level locks, the risk of locking conflicts can be reduced by enabling transactions to read the most recently committed version of the data in the locked rows, rather than waiting for the transaction that set the lock to be committed or rolled back. Enabling access to the last committed version of exclusively locked rows can be accomplished in several ways:

For an individual session, issue this SQL statement
```
 SET ISOLATION TO COMMITTED READ LAST COMMITTED;
```
For all sessions using the Committed Read or Read Committed isolation level, the DBA can set the USELASTCOMMITTED configuration parameter to 'ALL' or to 'COMMITTED READ'.
For an individual session using the Committed Read or Read Committed isolation level, any user can issue the SET ENVIRONMENT USELASTCOMMITTED statement with 'ALL' or 'COMMITTED READ' as the value of this session environment option.
For all sessions using Dirty Read or Read Uncommitted isolation levels, the DBA can set the USELASTCOMMITTED configuration parameter to 'ALL' or to 'DIRTY READ'.
For an individual session using the Dirty Read or Read Uncommitted isolation levels, any user can issue the SET ENVIRONMENT USELASTCOMMITTED statement with 'ALL' or 'DIRTY READ' as the value of this session environment option.

This LAST COMMITTED 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 any table on which the LOCK TABLE statement applies a table-level lock. See the description of the SET ENVIRONMENT statement in the Informix® Guide to SQL: Syntax and the description of the USELASTCOMMITTED configuration parameter in for more information about this 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.