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

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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.