By default,
SPD Server uses member-level locking to provide data integrity. Member-level locking obtains
an exclusive lock on a table on behalf of the user who is updating the table. When
a member-level lock is held, only the owner of the lock can access the table. Attempts
by others to open the table for Write access fail with a member lock failure error.
Record-level locking
locks individual records in a table. The user updating the record
has exclusive access to the record, while other users can access other
records in the same table. Record-level locking allows multiple clients
or parallel operations from the same client to have concurrent Write
access to a table, while ensuring the integrity of the record updates.
Record-level locking enforces SAS style record-level integrity across
multiple clients. Clients are guaranteed that a row will not change
during a multi-phased Read or Write operation on the specified row.
When LOCKING=YES is set in an SPD Server LIBNAME statement, all subsequent operations
on the server
domain will use record-level locking. Operations that affect metadata, such as creating
or deleting indexes, renaming
variables, and renaming tables require exclusive access to a table, whether record-level
locking is enabled or not. These types of operations will report a member lock failure
error when record-level locking is enabled. To perform these updates, there must be
no other outstanding read or write locks on the table.
Record-level locking
must be enabled in the server before a SAS client can use the CNTLEV=REC
table option in their SAS program to access server tables.
Record-level locking can have a performance cost if it is enabled on multiple domains.
When LOCKING=YES is set, the server connection is made with the single record-level
locking proxy process. There is only one record-level locking proxy process per server.
All
server clients that use record-level locking connections are processed through this
record-level locking proxy process. If there are a large number of record-level locking
connections, there might be some contention for process resources between the clients.
In addition, the record-level locking proxy process is a single point of failure for
all of these connections.
Record-level locking is not supported for dynamic cluster tables. The server returns
an error when an attempt to update a
dynamic cluster table while using record-level locking.