Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the level of database transaction isolation". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1 Overview and background

This is the first of a series of articles on database transaction principles and engineering practices. Based on Jim Gray's papers, this paper mainly analyzes the transaction isolation level standard of relational database and the behavior under different isolation levels. Section 2 mainly discusses the isolation level under the ANSI standard, section 3 mainly discusses the transaction isolation level based on pessimistic lock, section 4 mainly discusses transaction isolation based on multi-version technology, and finally summarizes the isolation levels discussed in this article.

ACID is a group of important characteristics of relational database, in which Isolation (isolation) describes the ability of a database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent data inconsistencies caused by interlaced execution of multiple transactions. In the most extreme case, the database executes each transaction in complete serialization, and all transactions obey the full order relationship. In this case, there is no isolation problem between concurrent transactions, but in practical engineering practice, considering the performance and throughput of the database, multiple transactions are allowed to break the full order relationship of serial words according to certain rules. ANSI SQL Isolation Levels stipulates this "rule". Isolation is divided into four levels in exchange for multi-level inter-transaction concurrency (that is, database throughput).

Note: the content of this article is integrated into the author's personal understanding and does not strictly follow the content of the original text; the cursor stability isolation level will be discussed in subsequent articles, and the comparison of the snapshot isolation level with the ANSI standard anomaly is also different.

2 ANSI transaction isolation level

ANSI SQL-92 standard (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) divides the isolated behavior between database concurrent transactions into three "phenomena", and the natural language definitions from low to high are as follows:

P1 dirty ("Dirty read"): SQL-transaction T1 modifies a row. SQL- transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.

P2 can not be read repeatedly ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL- transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

P3 Phantom ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same, it obtains a different collection of rows.

By banning these three anomalies in turn, ANSI identified four standard isolation levels, as shown in the following table:

Level P1 (dirty read) P2 (non-repeatable) P3 (phantom reading) Read Uncommitted allow Read Committed disable allow allow Repeatable Read disable

Prohibited

Allow (Anomaly) Serializable to disable

Prohibited

Prohibited

Note: The exclusion of these penomena or SQL-transactions executing at isolation level SERIALIZABLE is a consequence of the requirement that such transactions be serializable.

As stated in the standard documentation, transactions that prohibit P1/P2/P3 visions meet the Serializable level, but paradoxically, Serializable is described as follows:

The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions

It requires that the effect of multiple concurrent transaction execution is consistent with that of some serialization execution, but only banning the P1/P2/P3 anomaly does not necessarily guarantee the effect of "serial execution", so ANSI Serializable is called Anomaly Serializable in this paper.

Formal description of P1/P2/P3

According to the definition of the standard document, these three anomalies can be described as follows in formal language, which is called A1/A2/A3 (where W1 [x] represents transaction 1 write record x Magi R1 means transaction 1 read record x Magi C1 represents transaction 1 commit, A1 indicates transaction 1 rollback, R1 [P] indicates that transaction 1 reads several records according to the condition of predicate P W1 [y in P] indicates that transaction 1 writes record y to satisfy the predicate P):

A1 dirty reading: W1 [x]. R2 [x]... (A1 and c2 in any order)

A2 cannot be read repeatedly: R1 [x]. W2 [x]... C2... R1 [x]... C1

A 3 Phantom Reading: R1 [P]. W2 [y in P]... C2... R1 [P]... C1

The above formal description of A1/A2/A3 is transformed from the natural language description of the P1/P2/P3 anomaly defined by the standard, but the anomaly defined by the ANSI standard is only for a single record or predicate description, and cannot be covered for scenarios where multiple records are required to meet business consistency (for example, the sum of balances required for transfer between two accounts remains unchanged):

H1: R1 [x = 50] W1 [x = 10] R2 [x = 10] R2 [y = 50] c2 R1 [y = 50] W1 [y = 90] C1

Transaction 1 executes account x to transfer money to account y 40, and transaction 2 reads transaction 1 (Read Uncommitted), which is halfway through, which destroys the consistency of the sum of balances.

Because transaction 1 is not rolled back, the behavior of H1 does not conform to the formal definition of A1

H2:r1 [Xero50]

Read LockWrite Lock

Locking

Read Uncommited

None required

Well-formed Writes

Long duration Write locks

Locking

Read CommitedWell-formed Reads

Short duration read lock

Well-formed Writes

Long duration Write locks

Locking

Repeatable ReadWell-formed Reads

Long duration data-item Read locks

Short duration Read Predicate locks

Well-formed Writes

Long duration Write locks

Locking

SerializableWell-formed Reads

Long duration Read locks

Well-formed Writes

Long duration Write locks

Compare the four isolation levels marked by locking with the ANSI isolation level:

Well-formed Reads, Short duration read lockRead Uncommitted

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.