Specifies how data in a DBMS table is locked during a READ transaction.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Access |
| Alias: | READLOCK_TYPE= |
| Default: | none [DB2 under z/OS, Teradata] |
| set by the data provider [OLE DB] | |
| NOLOCK [Oracle, SAP ASE] | |
| ROW [Amazon Redshift, DB2 under UNIX and PC Hosts, Greenplum, HAWQ, Microsoft SQL Server, ODBC, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Vertica, Yellowbrick] | |
| Data source: | Amazon Redshift, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HAWQ, Microsoft SQL Server, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Teradata, Vertica, Yellowbrick |
| Notes: | Support for Amazon Redshift was added in the April 2016 release of SAS/ACCESS. |
| Support for Yellowbrick was added in SAS 9.4M7. | |
| Tip: | If you omit READ_LOCK_TYPE=, the default is the default action for the DBMS. You can specify 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: | CONNECTION= LIBNAME option, READ_ISOLATION_LEVEL= LIBNAME option, READ_ISOLATION_LEVEL= data set option, READ_LOCK_TYPE= data set option, READ_MODE_WAIT= LIBNAME option, READ_MODE_WAIT= data set option, UPDATE_ISOLATION_LEVEL= LIBNAME option, UPDATE_ISOLATION_LEVEL= data set option, UPDATE_LOCK_TYPE= LIBNAME option, UPDATE_LOCK_TYPE= data set option, and DBMS-specific locking information in the reference section for your SAS/ACCESS interface |
Table of Contents
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.
| Data source | Amazon Redshift, DB2 under UNIX and PC Hosts, Greenplum, HAWQ, Microsoft SQL Server, ODBC, Oracle, PostgreSQL, SAP HANA, SAP IQ, Teradata, Vertica |
|---|
locks a page of data, which is a DBMS-specific number of bytes.
| Data source | SAP ASE |
|---|
locks the entire DBMS table. If you specify READ_LOCK_TYPE=TABLE, you must also specify CONNECTION=UNIQUE, or you receive an error message. Specifying CONNECTION=UNIQUE ensures that your table lock is not lost (for example, due to another table closing and committing rows in the same connection).
| Data source | DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HAWQ, Microsoft SQL Server, ODBC, Oracle, SAP IQ, Teradata |
|---|
does not lock the DBMS table, pages, or rows during a read transaction.
| Data source | Microsoft SQL Server, ODBC with Microsoft SQL Server driver, OLE DB, Oracle, SAP ASE |
|---|
locks the entire DBMS view.
| Data source | Teradata |
|---|
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=myusr1 password=mypwd1
path=mysrv1 read_lock_type=row update_lock_type=row;