Locking in the Teradata Interface

Overview

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

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

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. Can return inconsistent or unusual results.
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, it 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 the entire table until the 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 the 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 the 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 lock at the table or view level. The change in the scope of the lock from row to table affects concurrent access to DBMS objects. Specifically, READ and WRITE table locks increase the 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 the 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 a different 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 a different 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 settings 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 is waiting 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 settings in this situation.

Specify a READ lock.
Change the scope of the lock. Because SAS/ACCESS does not support row locking 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.

Examples

Setting the Isolation Level to ACCESS for Teradata Tables

  /* This generates a quick survey of unusual customer purchases. */
libname cust teradata user=testuser password=testpass
               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 a different 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=testuser password=testpass;

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.

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=testuser password=testpass  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.