15.00 - Default Session Transaction Isolation Level - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Default Session Transaction Isolation Level

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 SQL Data Manipulation Language.