Transaction isolation level

Transaction isolation level

The ANSI/iso SQL standard defines four transaction isolation levels. For the same transaction, different isolation levels have different results. That is to say, even if the input is the same and the same method is used to accomplish the same job, different answers may be obtained, depending on the isolation level of the transaction. These isolation levels are defined based on three phenomena. The following are three phenomena that a given isolation level may allow or disallow:

A) dirty read (dirty read): You can read uncommitted data, that is, dirty data. You only need to open an OS file that is being read and written by others (no matter what data is in the file), you can achieve dirty read effect. If dirty read , data integrity will be affected. In addition, foreign key constraints will be damaged, and uniqueness constraints will be ignored.

B) nonrepeatable read: this means that this line may have been modified when the http://www.linuxidc.com re-reads this line at T2. Maybe it has disappeared, it may have been updated, and so on.

The changes here have been submitted, andDirty readDifferent.

C) Phantom read: This indicates that if you execute a query at T1 and then execute the query at T2, at this time, another row may have been added to the database, which affects your results. The difference with non-repeated reading is that in phantom reading, the data that has been read will not change, but more data will meet your query conditions compared with the previous one.

The SQL isolation level describes the level based on these phenomena, and does not force a specific locking mechanism or a specified behavior. This allows different locking/concurrency mechanisms to exist.

Table 1 ANSI isolation level
Isolation level Dirty read Do not read the phantom repeatedly
Read uncommitted allows
Read committed allows
Repeatable read allowed
Serializable