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 Vantage 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 …|
|FALSE||the lock severity of embedded SELECT requests is not downgraded from READ to ACCESS.|
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 …|
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.