Locking in the Sybase Interface

Overview

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

READ_LOCK_TYPE= PAGE | NOLOCK

The default value for Sybase is NOLOCK.

UPDATE_LOCK_TYPE= PAGE | NOLOCK

PAGE

SAS/ACCESS uses a cursor that you can update. PAGE is the default value for Sybase. When you use this setting, you cannot use the SCHEMA= option, and it is also recommended that the table have a defined primary key.

NOLOCK

SAS/ACCESS uses Sybase browse mode updating, in which the table that is being updated must have a primary key and timestamp.

READ_ISOLATION_LEVEL= 1 | 2 | 3

For reads, Sybase supports isolation levels 1, 2, and 3, as defined in the following table. See your Sybase documentation for more information.

Isolation Levels for Sybase

Isolation Level	Definition
1	Prevents dirty Reads. This is the default transaction isolation level.
2	Uses serialized Reads.
3	Also uses serialized Reads.

UPDATE_ISOLATION_LEVEL= 1 | 3

Sybase uses a shared or update lock on base table pages that contain rows representing a current cursor position. This option applies to updates only when UPDATE_LOCK_TYPE=PAGE because cursor updating is in effect. It does not apply when UPDATE_LOCK_TYPE=NOLOCK.

For updates, Sybase supports isolation levels 1 and 3, as defined in the preceding table. See your Sybase documentation for more information.

Understanding Sybase Update Rules

To avoid data integrity problems when updating and deleting data in Sybase tables, take these precautionary measures:

Always define a primary key.
If the updates are not taking place through cursor processing, define a timestamp column.

It is not always obvious whether updates are using cursor processing. Cursor processing is never used for LIBNAME statement updates if UPDATE_LOCK_TYPE=NOLOCK. Cursor processing is always used in these situations:

Updates using the LIBNAME statement with UPDATE_LOCK_TYPE=PAGE. This is the default setting for this option.
Updates using PROC SQL views.
Updates using PROC ACCESS view descriptors.