SET SESSION TRANSACTION ISOLATION LEVEL Examples - SET SESSION TRANSACTION ISOLATION LEVEL Examples - Teradata VantageCloud Lake

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

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 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.

In ANSI session mode, these requests return an Error rather than a Failure, and the system rolls the erring request back.

• 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.