In most cases,
SAS spooling is on by default for the Informix interface and provides
the data consistency that you need.
To control how the Informix
interface handles locking, you can use the
READ_ISOLATION_LEVEL= LIBNAME Option. Here are the valid values.
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.
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.
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.
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 clear that libref when you finish 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.