The following
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
The default value is
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
The default value is
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 defined in
the following table. 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
|
|
|
Does not allow dirty
Reads, nonrepeatable Reads, or phantom Reads.
|
|
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 defined in the
preceding table, for updates.
This option should
be rarely needed because the
SAS/ACCESS engine chooses the appropriate
isolation level based on other locking options.