Topics for Database Administrators

Locking Record Levels

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. [cautionend]

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 will be locked if possible, but the engine does not regard it as an error when they cannot be locked, and no message is sent to warn the user.
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 on 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 will attempt to update records at any level that you specify and will perform the update if it can.

Top of Page