SAS/ACCESS Interface for Informix

Locking in the Informix Interface

In most cases, SAS spooling is on by default for the Informix interface and provides the data consistency you need.

To control how the Informix interface handles locking, you can use the READ_ISOLATION_LEVEL= LIBNAME Option. Here are the valid values.

COMMITTED_READ: retrieves only committed rows. No locks are acquired, and rows can be locked exclusively for update by other users or processes. This is the default setting.
REPEATABLE_READ: gives you a shared lock on every row that is selected during the transaction. Other users or processes can also acquire a shared lock, but no other process can modify any row that is selected by your transaction. If you repeat the query during the transaction, you re-read the same information. The shared locks are released only when the transaction commits or rolls back. Another process cannot update or delete a row that is accessed by using a repeatable read.
DIRTY_READ: retrieves committed and uncommitted rows that might include phantom rows, which are rows that are created or modified by another user or process that might subsequently be rolled back. This type of read is most appropriate for tables that are not frequently updated.
CURSOR_STABILITY: gives you a shared lock on the selected row. Another user or process can acquire a shared lock on the same row, but no process can acquire an exclusive lock to modify data in the row. When you retrieve another row or close the cursor, the shared lock is released.

If you set READ_ISOLATION_LEVEL= to REPEATABLE_READ or CURSOR_STABILITY, it is recommended that you assign a separate libref and that you clear that libref when you have finished working with the tables. This technique minimizes the negative performance impact on other users that occurs when you lock the tables. To clear the libref, include this code:

libname libref clear;

For current Informix releases, READ_ISOLATION_LEVEL= is valid only when transaction logging is enabled. If transaction logging is not enabled, an error is generated when you use this option. Also, locks placed when READ_ISOLATION_LEVEL= REPEATABLE READ or CURSOR_STABILITY are not freed until the libref is cleared.

To see the SQL locking statements that SAS issues to the Informix server, include in your code the SASTRACE= System Option.

option sastrace=',,,d';

For more details about Informix locking, see your Informix documentation.

Top of Page