SAS/ACCESS Software for Relational Databases: Reference

Locking, Transactions, and Currency Control

Beginning in Version 7, SAS/ACCESS provides options that allow you to control some of the row, page, or table locking operations that are performed by the DBMS and the SAS/ACCESS engine as your programs are executed. For example, the SAS/ACCESS Interface to ORACLE has the READ_LOCK_TYPE=, UPDATE_LOCK_TYPE=, READ_ISOLATION_LEVEL=, UPDATE_ISOLATION_LEVEL=, and LOCKWAIT= options. By default, the SAS/ACCESS ORACLE engine does not lock any data when reading rows from ORACLE tables. However, you can override this behavior by using these options.

If you want to lock the data pages of a table while the SAS System is reading the data to prevent other processes from updating the table, you can use the READLOCK_TYPE= option, as in the following example:

libname myoralib oracle user=testuser pass=testpass
   path='myoraserver' readlock_type=table;

data work.mydata;
   set myoralib.mytable(where=(colnum > 123));
run;

In this example, the SAS/ACCESS ORACLE engine obtains a TABLE SHARE lock on the table so that the data cannot be updated by other processes while your SAS program is reading it.

In the following example, ORACLE acquires row-level locks on rows read for update in the tables in the libref.

libname myoralib oracle user=testuser password=testpass
   path='myoraserver' updatelock_type=row;

Note: Each SAS/ACCESS interface supports specific options; see your DBMS chapter to determine which options are supported for your DBMS. [cautionend]

Chapter Contents
Previous
Next
Top of Page