SAS supports several levels of locking through the CNTLLEV=
data set option. If CNTLLEV=REC (the default), SYSTEM 2000 performs record-
level locking. The
interface view engine interprets any value for the CNTLLEV= option other than REC, to signify that it should
enable
exclusive use of the
database. Also, the database is under exclusive use if you issue the S2KLOAD statement in
the DBLOAD procedure. Exclusive use locks out all other users until the database is
closed, which usually occurs when the procedure ends. (The database CLOSE operation
depends on the procedure used.)
Note: Some SAS procedures, such
as statistical procedures, set CNTLLEV=MEM internally because multiple
passes of the data must be made. For example, finding the median requires
more than one pass.
In a
Multi-User environment, exclusive use of a database can cause contention in a database. Also,
if you have specified optimized
load mode (S2KLOAD) in PROC DBLOAD, your input to that load cannot be a
SYSTEM 2000 view descriptor for a database in the same environment.
When exclusive use of the database is not requested, the interface view engine uses
SYSTEM 2000 record-level locking and multiple local holds. This means that an
observation is locked for retrieval, and unlocked only when some other observation is retrieved
or when the file is closed. Updates do not unlock an observation. Record-level locking
can cause contention in a SYSTEM 2000 database. The interface
view engine takes the following steps to keep the contention to a minimum:
-
At retrieval time, the engine attempts to lock all records in the
path (using the
PLEX /
HOLD option). If the lowest-level record in the path (that is, the record farthest from level
0) cannot be locked, an error
return code is sent to SAS that indicates that this observation cannot be locked. Records above
the lowest level in the view are locked if possible. However, the engine does not
regard it as an error when they cannot be locked, and no message is sent to warn you.
-
At update time, only those records that were successfully locked can be updated. For
updates at levels that were not previously locked, the engine tries again to obtain
the locks. If it cannot get them, the update fails, a return code indicates that it
could not get the necessary lock, and partial updates are rolled
back if
rollback is enabled. If the engine gets the locks, it checks to verify that the data in the
database is the same as when the data was originally retrieved. If the data is the
same, the
update takes place.
The purpose of this locking mechanism is to avoid contention. You can always access
a path if the lowest-level record can be locked. You do not have to wait for another
user to drop a lock onto
one of the upper-level records. (However, there might be relatively few locks of upper-level
records.) You are guaranteed to be able to update only items in the lowest-level
record of the
view descriptor. The engine attempted to update records at any level that you specify and performed
the update
if it can.