Locking in the Oracle Interface

These LIBNAME and data set options let you control how the Oracle interface handles locking. For general information about an option, see LIBNAME Options for Relational Databases.

READ_LOCK_TYPE= NOLOCK | ROW | TABLE

Default: NOLOCK

Here are the valid values for this option:

• NOLOCK — table locking is not used during the reading of tables and views.
• ROW — the Oracle ROW SHARE table lock is used during the reading of tables and views.
• TABLE — the Oracle SHARE table lock is used during the reading of tables and views.

If you set READ_LOCK_TYPE= to either TABLE or ROW, you must also set the CONNECTION= option to UNIQUE. If not, an error occurs.

UPDATE_LOCK_TYPE= NOLOCK | ROW | TABLE

Default: NOLOCK

Here are the valid values for this option:

• ROW — the Oracle ROW SHARE table lock is used during the reading of tables and views for update.
• TABLE — the Oracle EXCLUSIVE table lock is used during the reading of tables and views for update.
• NOLOCK — table locking is not used during the reading of tables and views for update.
  • If OR_UPD_NOWHERE=YES, updates are performed using serializable transactions.
  • If OR_UPD_NOWHERE=NO, updates are performed using an extra WHERE clause to ensure that the row has not been updated since it was first read. Updates might fail under these conditions, because other users might modify a row after the row was read for update.

READ_ISOLATION_LEVEL= READCOMMITTED | SERIALIZABLE

Oracle supports the READCOMMITTED and SERIALIZABLE read isolation levels, as specified in the table below. The SPOOL= option overrides the READ_ISOLATION_LEVEL= option. The READ_ISOLATION_LEVEL= option should be rarely needed because the SAS/ACCESS engine chooses the appropriate isolation level based on other locking options.

Isolation Levels for Oracle

Isolation Level	Definition
SERIALIZABLE	Does not allow dirty Reads, nonrepeatable Reads, or phantom Reads.
READCOMMITTED	Does not allow dirty Reads; does allow nonrepeatable Reads and phantom Reads

UPDATE_ISOLATION_LEVEL= READCOMMITTED | SERIALIZABLE

Oracle supports the READCOMMITTED and SERIALIZABLE isolation levels, as specified in the preceding table, for updates.

Default: READCOMMITTED

This option should be rarely needed because the SAS/ACCESS engine chooses the appropriate isolation level based on other locking options.