SET SESSION TRANSACTION ISOLATION LEVEL Examples - 17.00 - SET SESSION TRANSACTION ISOLATION LEVEL Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

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.