16.20 - Correspondence Between ANSI SQL Transaction Isolation Levels and Database Locks - 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 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 Teradata Database permits dirty reads through the use of ACCESS locking, there is a seemingly good correspondence between the ANSI isolation level READ UNCOMMITTED and Teradata Database 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.