Locking in the Teradata Interface

Overview

The SAS/ACCESS Teradata interface has LIBNAME and data set options that let you modify how Teradata handles locking. Use SAS/ACCESS locking options only when Teradata standard locking is undesirable. For tips on using these options, see Understanding SAS/ACCESS Locking Options and When to Use SAS/ACCESS Locking Options. Teradata examples are available.

READ_LOCK_TYPE= TABLE | VIEW | ROW

UPDATE_LOCK_TYPE= TABLE | VIEW

READ_MODE_WAIT= YES | NO

UPDATE_MODE_WAIT= YES | NO

READ_ISOLATION_LEVEL= ACCESS | READ | WRITE

Here are the valid values for this option.

Read Isolation Levels for Teradata
Isolation Level	Definition
ACCESS	Obtains an ACCESS lock by ignoring other users' ACCESS, READ, and WRITE locks. Permits other users to obtain a lock on the table or view. You see a snapshot of the data when the lock is specified. Can return inconsistent or unusual results if another user is modifying the data at the same time that you are accessing the data.
READ	Obtains a READ lock if no other user holds a WRITE or EXCLUSIVE lock. Does not prevent other users from reading the object. Specify this isolation level whenever possible. READ is usually adequate for most SAS/ACCESS processing.
WRITE	Obtains a WRITE lock on the table or view if no other user has a READ, WRITE, or EXCLUSIVE lock on the resource. You cannot explicitly release a WRITE lock. It is released only when the table is closed. Prevents other users from acquiring any lock but ACCESS. This is unnecessarily restrictive, because it locks an entire table until a Read operation is finished.

UPDATE_ISOLATION_LEVEL= ACCESS | READ | WRITE

The valid values for this option, ACCESS, READ, and WRITE, are defined in the following table.

Update Isolation Levels for Teradata
Isolation Level	Definition
ACCESS	Obtains an ACCESS lock by ignoring other users' ACCESS, READ, and WRITE locks. Avoids a potential deadlock but can cause data corruption if another user is updating the same data.
READ	Obtains a READ lock if no other user holds a WRITE or EXCLUSIVE lock. Prevents other users from being granted a WRITE or EXCLUSIVE lock. Locks an entire table or view, allowing other users to acquire READ locks. Can lead to deadlock situations.
WRITE	Obtains a WRITE lock on the table or view if no other user has a READ, WRITE, or EXCLUSIVE lock on the resource. You cannot explicitly release a WRITE lock. It is released only when the table is closed. Prevents other users from acquiring any lock but ACCESS. Prevents all users, except those with ACCESS locks, from accessing a table. Prevents the possibility of a deadlock, but limits concurrent use of the table.

These locking options cause the LIBNAME engine to transmit a locking request to the DBMS; Teradata performs all data-locking. If you correctly specify a set of SAS/ACCESS read or update locking options, SAS/ACCESS generates locking modifiers that override the Teradata standard locking.

If you specify an incomplete set of locking options, SAS/ACCESS returns an error message. If you do not use SAS/ACCESS locking options, Teradata lock defaults are in effect. For a complete description of Teradata locking, see the LOCKING statement in your Teradata SQL reference documentation.

Understanding SAS/ACCESS Locking Options

SAS/ACCESS locking options modify Teradata standard locking. Teradata usually locks at the row level. SAS/ACCESS lock options can lock at the row, table, or view level. A change in the scope of a lock from row to table affects concurrent access to DBMS objects. Specifically, READ and WRITE table locks increase the amount of time that other users must wait to access the table and can decrease overall system performance. These measures help minimize these negative effects.

Apply READ or WRITE locks only when you must apply special locking on Teradata tables.
SAS/ACCESS locking options can be appropriate for special situations, as described in When to Use SAS/ACCESS Locking Options. If SAS/ACCESS locking options do not meet your specialized needs, you can use additional Teradata locking features using views. See CREATE VIEW in your Teradata SQL reference documentation for details.
Limit the span of locks by using data set locking options instead of LIBNAME locking options whenever possible. LIBNAME options affect all tables that you open that your libref references. Data set options apply only to the specified table.

If you specify these read locking options, SAS/ACCESS generates and submits to Teradata locking modifiers that contain the values that you specify for the three read lock options:

READ_ISOLATION_LEVEL= specifies the level of isolation from other table users that is required during SAS/ACCESS Read operations.
READ_LOCK_TYPE= specifies and changes the scope of the Teradata lock during SAS/ACCESS Read operations.
READ_MODE_WAIT= specifies during SAS/ACCESS Read operations whether Teradata should wait to acquire a lock or fail your request when the DBMS resource is locked by another user.

If you specify these update lock options, SAS/ACCESS generates and submits to Teradata locking modifiers that contain the values that you specify for the three update lock options:

UPDATE_ISOLATION_LEVEL= specifies the level of isolation from other table users that is required as SAS/ACCESS reads Teradata rows in preparation for updating the rows.
UPDATE_LOCK_TYPE= specifies and changes the scope of the Teradata lock during SAS/ACCESS Update operations.
UPDATE_MODE_WAIT= specifies during SAS/ACCESS Update operations whether Teradata should wait to acquire a lock or fail your request when the DBMS resource is locked by another user.

When to Use SAS/ACCESS Locking Options

This section describes situations that might require SAS/ACCESS lock options instead of the standard locking that Teradata provides.

Use SAS/ACCESS locking options to reduce the isolation level for a Read operation.
When you lock a table using a READ_* option, you can lock out both yourself and other users from updating or inserting into the table. Conversely, when other users update or insert into the table, they can lock you out from reading the table. In this situation, you want to reduce the isolation level during a Read operation. To do this, you specify these read SAS/ACCESS lock options and values.
- READ_ISOLATION_LEVEL=ACCESS
- READ_LOCK_TYPE=TABLE
- READ_MODE_WAIT=YES
One of these situations can result from the options and values in this situation:
- Specify ACCESS locking, eliminating a lock out of yourself and other users. Because ACCESS can return inconsistent results to a table reader, specify ACCESS only if you are casually browsing data, not if you require precise data.
- Change the scope of the lock from row-level to the entire table.
- Request that Teradata wait if it attempts to secure your lock and finds the resource already locked.
Use SAS/ACCESS lock options to avoid contention.
When you read or update a table, contention can occur: the DBMS waits for other users to release their locks on the table that you want to access. This contention suspends your SAS/ACCESS session. In this situation, to avoid contention during a Read operation, you specify these SAS/ACCESS Read lock options and values.
- READ_ISOLATION_LEVEL=READ
- READ_LOCK_TYPE=TABLE
- READ_MODE_WAIT=NO

One of these situations can result from the options and values in this situation.

Specify a READ lock.
Change the scope of the lock. Because you have specified READ_LOCK_TYPE=TABLE, when you obtain the lock requested, you lock the entire table until your Read operation finishes.
Tell SAS/ACCESS to fail the job step if Teradata cannot immediately obtain the READ lock.

When Lock Options Are Not Recognized

There are situations when the values for locking options are not recognized by Teradata. These are some examples of those situations:

You attempt to set a lock on a target table that already has a lock in place for the current session. For example, an existing READ lock prevents you from obtaining a lock at the WRITE level.
You attempt to downgrade the level of a lock that is already in place for a table. For example, you cannot downgrade a ROW lock from READ to ACCESS after the READ lock is in place.
You specify an aggregate, such as a DISTINCT or GROUP BY operation, in a SELECT statement.
You have specified READ_LOCK_TYPE=ROW, and you do not use the primary or unique secondary index to indicate rows that you want to access. Teradata uses a TABLE lock if a SELECT statement is not limited by a primary or unique secondary index.

To verify the lock type that is used, you can specify MSGLEVEL=I in the OPTIONS statement, or you can turn on lock logging in Teradata and review the locks in Teradata Viewpoint.

Examples

Specifying the Isolation Level to ACCESS for Teradata Tables

  /* This generates a quick survey of unusual customer purchases. */
libname cust teradata user=myusr1 password=mypwd1
               READ_ISOLATION_LEVEL=ACCESS
               READ_LOCK_TYPE=TABLE
               READ_MODE_WAIT=YES
               CONNECTION=UNIQUE;

proc print data=cust.purchases(where= (bill<2));
run;

data local;
 set cust.purchases (where= (quantity>1000));
run;

Here is what SAS/ACCESS does in the above example.

Connects to the Teradata DBMS and specifies the three SAS/ACCESS LIBNAME Read lock options.
Opens the Purchases table and obtains an ACCESS lock if another user does not hold an EXCLUSIVE lock on the table.
Reads and displays table rows with a value less than 2 in the Bill column.
Closes the Purchases table and releases the ACCESS lock.
Opens the Purchases table again and obtains an ACCESS lock if a different user does not hold an EXCLUSIVE lock on the table.
Reads table rows with a value greater than 1000 in the Quantity column.
Closes the Purchases table and releases the ACCESS lock.

Setting Isolation Level to WRITE to Update a Teradata Table

/* This updates the critical Rebate row. */
libname cust teradata user=myusr1 password=mypwd1;

proc sql;
  update cust.purchases(UPDATE_ISOLATION_LEVEL=WRITE
        UPDATE_MODE_WAIT=YES
        UPDATE_LOCK_TYPE=TABLE)
  set rebate=10 where bill>100;
quit;

In this example, here is what SAS/ACCESS does:

Connects to the Teradata DBMS and specifies the three SAS/ACCESS data set Update lock options.
Opens the Purchases table and obtains a WRITE lock if a different user does not hold a READ, WRITE, or EXCLUSIVE lock on the table.
Updates table rows with Bill greater than 100 and sets the Rebate column to 10.
Closes the Purchases table and releases the WRITE lock.

Note: UPDATE options are applied only to a preparatory SELECT statement when the SQL statements are passed to Teradata. As a result, deadlock errors are possible if the same table is being updated by other processes at the same time. In this case, the solution is to specify UPDATE_ISOLATION_LEVEL=ACCESS.

Preventing a Hung SAS Session When Reading and Inserting to the Same Table

/* SAS/ACCESS lock options prevent the session hang */
/* that occurs when reading & inserting into the same table. */
libname tra teradata user=myusr1 password=mypwd1 connection=unique;

proc sql;
insert into tra.sametable
   select * from tra.sametable(read_isolation_level=access
          read_mode_wait=yes
          read_lock_type=table);

Here is what SAS/ACCESS does in the above example:

Creates a read connection to fetch the rows selected (SELECT *) from Tra.Sametable and specifies an ACCESS lock (READ_ISOLATION_LEVEL=ACCESS). Teradata grants the ACCESS lock.
Creates an insert connection to Teradata to process the Insert operation to Tra.Sametable. Because the ACCESS lock that is already on the table permits access to the table, Teradata grants a WRITE lock.
Performs the Insert operation without hanging (suspending) your SAS session.

Last updated: February 3, 2026