17.10 - Correspondence Between ANSI SQL Transaction Isolation Levels and Database Locks - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

The correspondence between ANSI SQL transaction isolation levels and database locks is not 1:1 because the authors of the isolation levels standard intended to define its rules in terms that were independent of their implementation. In other words, they wanted to define a standard for transaction isolation levels that could apply to many different implementations, not just those based on locking, in particular the two-phase locking protocol (see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 for information about 2PL).

The only direct correspondence is between the isolation level SERIALIZABLE and the standard commercial database management system implementation of two-phase locking (see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 for a description of two-phase locking). All other isolation levels in the standard are defined in terms of what are called phenomena, of which the dirty read is one example. Because the database permits dirty reads through the use of ACCESS locking, there is a seemingly good correspondence between the ANSI isolation level READ UNCOMMITTED and Vantage ACCESS locks. Because the ANSI definition of READ UNCOMMITTED is written in the English language rather than formally defined, however, its details are open to interpretation.