17.05 - SET SESSION TRANSACTION ISOLATION LEVEL Syntax Elements - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)
isolation_level
Default transaction isolation level in force for the current session.
The valid transaction isolation level options are the following.
READ UNCOMMITTED
RU
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.
SERIALIZABLE
SR
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 Teradata Vantage™ - 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 Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 for descriptions of locking severities and locking levels).