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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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.