Correspondence Between ANSI SQL Transaction Isolation Levels and Database Locks
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 SQL Request and Transaction Processing 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 SQL Request and Transaction Processing 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.
See SQL Request and Transaction Processing for information about transaction processing in general and for a description of the difference between locking levels and locking severities.
See “SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL” in SQL Data Definition Language Syntax and Examples for the syntax of this statement and examples of its use.