16.20 - Default Session Transaction Isolation Level - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

The initial default transaction isolation level for any session is SERIALIZABLE. In this context, that is equivalent to saying the default read-only locking severity for any session is READ. This applies to all SELECT operations, whether they are standalone SELECT requests, ordinary SELECT subqueries, or SELECT subqueries embedded within DELETE, INSERT, or UPDATE requests.

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL permits you either to set the default read-only locking severity for SELECT operations embedded within DELETE, INSERT, or UPDATE requests for the current session to ACCESS or to set it back to READ after it has been set to ACCESS.

This means that changing the session-level isolation level to READ UNCOMMITTED affects the default read-only locking severity for any SELECT requests embedded within DELETE, INSERT, MERGE, or UPDATE requests, making the default lock severity ACCESS, but only when the value for the DBS Control flag AccessLockForUncomRead is set TRUE. When AccessLockForUncomRead is set FALSE, the default read-only locking severity for those SELECT operations is READ.

Note that you must both specify READ UNCOMMITTED and set the DBS Control flag AccessLockForUncomRead to TRUE for Teradata Database to downgrade the default locking severity for embedded SELECT requests within data manipulating requests from READ to ACCESS for the session. In this context, a data manipulating request is any DELETE, INSERT, MERGE, or UPDATE request.

IF you set the isolation level to this value … AND set the value for the DBS Control flag AccessLockForUncomRead to this value … THEN …
READ UNCOMMITTED

RU

FALSE the lock severity of embedded SELECT requests is not downgraded from READ to ACCESS.
SERIALIZABLE

SR

TRUE

The following table presents this information in a slightly different way.

IF the transaction isolation level is … AND the DBS Control AccessLockForUncomRead flag is set … THEN the default locking severity for outer SELECT and ordinary SELECT subquery operations is … AND the default locking severity for SELECT operations embedded within a DELETE, INSERT, MERGE, or UPDATE request is …
SERIALIZABLE FALSE READ READ
TRUE READ
READ UNCOMMITTED FALSE READ
TRUE ACCESS

Note that the Optimizer implicitly specifies the locking level (either row hash, view, or table) for any read-only operation, irrespective of whether the default session isolation level is SERIALIZABLE or READ UNCOMMITTED. Isolation levels affect locking severities, not locking levels.

You can only specify locking levels explicitly using the LOCKING request modifier. For details, see Teradata Vantage™ SQL Data Manipulation Language , B035-1146 .