Correspondence between ANSI SQL Transaction Isolation Levels and Database Locks - Teradata Vantage

Teradata® VantageCloud Lake

Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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. That is, they wanted to define a standard for transaction isolation levels that could apply to many different implementations, not only those based on locking or the two-phase locking protocol (see Database Locks, Two-Phase Locking, and Serializability 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 Database Locks, Two-Phase Locking, and Serializability 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.