15.10 - isolation_level - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Default transaction isolation level in force for the current session.

The valid transaction isolation level options are the following.

RU
READ UNCOMMITTED
Sets the read-only locking severity for the current session to ACCESS for SELECT operations embedded within DELETE, INSERT, MERGE, and UPDATE requests if the DBS Control parameter AccessLockForUncomRead is set to TRUE; otherwise, the read-only locking severity for such requests remains READ.
For outer SELECT requests and SELECT subqueries that are not embedded within DELETE, INSERT, or UPDATE requests, the default read-only locking severity is always READ.
The read-only locking severity for SELECT operations embedded within DELETE, INSERT, MERGE, or UPDATE requests remains READ when the transaction isolation level is set to READ UNCOMMITTED only if the setting of the DBS Control parameter AccessLockForUncomRead is FALSE.
If AccessLockForUncomRead is set to TRUE, the default read-locking severity lock for these requests is ACCESS.
RU and READ UNCOMMITTED are synonyms.
SR
SERIALIZABLE
Sets the read-only locking severity for all SELECT requests made against nontemporal tables, whether they are outer SELECT requests or subqueries, in the current session to READ regardless of the setting for the DBS Control parameter AccessLockForUncomRead.
Serializability violations can occur with DML operations that use Current semantics or that specify the CURRENT VALIDTIME qualifier whether the transaction isolation level is set to SERIALIZABLE or not. See Temporal Table Support, B035-1182 for details of when and why this can occur and how you can work around it.
SR and SERIALIZABLE are synonyms.
Note that transaction isolation levels affect locking severities, not locking levels (see SQL Request and Transaction Processing, B035-1142 for descriptions of locking severities and locking levels).

Example: Setting the Default Session Isolation Level To READ UNCOMMITTED

The following equivalent requests set the isolation level for the current session to READ UNCOMMITTED.

     SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL RU;
     SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ
     UNCOMMITTED;

Example: Setting the Default Session Isolation Level Back To SERIALIZABLE

The following equivalent requests set the isolation level for the current session back to SERIALIZABLE.

     SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SR;
     SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
     SERIALIZABLE;

Example: Failure Conditions

Note that both syntax and resolver errors return Failure responses for this statement in Teradata session mode.

The following request returns a Failure response because SU is not a valid isolation level.

     SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SU;
     *** Failure 3706 Syntax error: expecting isolation level.

The following Teradata session mode transaction returns a Failure response because SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL is a DDL statement, but it is not the last request in the transaction, and the only statements that are valid following a DDL statement in an explicit transaction are END TRANSACTION, ABORT/ROLLBACK, or a NULL statement.

     SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SR;
     SELECT * 
     FROM table_1;
     *** Failure 3932 Only an ET or null statement is legal after a DDL
     Statement.

Note that in ANSI session mode, these requests would return an Error rather than a Failure. The system would then roll the erring request back.

You can correct this transaction in three different ways.

  • Run the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL request as a single-statement request in Teradata session mode as an implicit transaction.
  • Commit or roll back the containing explicit Teradata session mode transaction immediately after issuing the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL request with one of the following.
  • END TRANSACTION statement.
  • ABORT or ROLLBACK statement.
  • Commit or roll back the containing ANSI session mode transaction immediately after issuing the SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL request with one of the following.
  • COMMIT statement.
  • ABORT or ROLLBACK statement.