READ_LOCK_TYPE= LIBNAME Option

Specifies how data in a DBMS table is locked during a READ transaction.

Valid in:	SAS/ACCESS LIBNAME statement
Default:	DBMS-specific
Data source:	DB2 under UNIX and PC Hosts, DB2 under z/OS, Microsoft SQL Server, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata
See:	CONNECTION= LIBNAME option, READ_ISOLATION_LEVEL= LIBNAME option, READ_LOCK_TYPE= data set option [DBMS-specific locking information] DB2 under UNIX and PC Hosts, DB2 under z/OS, Microsoft SQL Server, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata

Syntax

Details

Example

Syntax

READ_LOCK_TYPE=ROW | PAGE | TABLE | NOLOCK | VIEW

Syntax Description

ROW [valid for DB2 under UNIX and PC Hosts, Microsoft SQL Server, ODBC, Oracle, Sybase IQ]: locks a row if any of its columns are accessed. If you are using the interface to ODBC or DB2 under UNIX and PC Hosts, READ_LOCK_TYPE=ROW indicates that locking is based on the READ_ISOLATION_LEVEL= LIBNAME option.

PAGE [valid for Sybase]: locks a page of data, which is a DBMS-specific number of bytes. (This value is valid in the Sybase interface.)

TABLE [valid for DB2 under UNIX and PC Hosts, DB2 under z/OS, Microsoft SQL Server, ODBC, Oracle, Sybase IQ, Teradata]: locks the entire DBMS table. If you specify READ_LOCK_TYPE=TABLE, you must also specify CONNECTION=UNIQUE, or you receive an error message. Setting CONNECTION=UNIQUE ensures that your table lock is not lost (for example, due to another table closing and committing rows in the same connection).

NOLOCK [valid for Microsoft SQL Server, ODBC with Microsoft SQL Server driver, OLE DB, Oracle, Sybase]: does not lock the DBMS table, pages, or rows during a read transaction.

VIEW [valid for Teradata]: locks the entire DBMS view.

Details

If you omit READ_LOCK_TYPE=, the default is the DBMS' default action. You can set a lock for one DBMS table by using the data set option or for a group of DBMS tables by using the LIBNAME option.

See the locking topic for your interface in the DBMS-specific reference section for details.

Example

In this example, the libref MYDBLIB uses SAS/ACCESS Interface to Oracle to connect to an Oracle database. USER=, PASSWORD=, and PATH= are SAS/ACCESS connection options. The LIBNAME options specify to use row-level locking when data is read or updated.

libname mydblib oracle user=testuser password=testpass
        path=myorapth read_lock_type=row update_lock_type=row;