PostgreSQL Learning (2)--MVCC

In the database, concurrent operations to read and write data, you will encounter dirty read, non-repeatable read, Phantom Read, serialization anomalies and so on.

Characteristics of database transactions:

1. Atomicity (atomicity): The transaction is executed as a whole, and the operations contained therein are either completely executed or not executed;
2. Consistency (consistency): a transaction should ensure that the state of the database transitions from one consistent state to another consistent state. The meaning of the consistent state is that the data in the database should satisfy the integrity constraints;
3. Isolation (Isolation): When multiple transactions are executing concurrently, the execution of one transaction should not affect the execution of other transactions;
4. Persistence (Durability): Once a transaction is committed, his modifications to the database should be persisted in the database.

There are 4 types of transaction isolation levels in the database, read UNCOMMITTED, Read committed, Repeatable read, and serializable. PostgreSQL has supported these four standard transaction isolation levels after the 9.3 release. The isolation level (Transaction isolation) of the current transaction can be set through the set Transaction command.

The relationship between the PostgreSQL transaction isolation level and the corresponding database problem

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom reading	Serialization exception
Read not submitted	Allowed, but PG does not support	possible	possible	possible
Read Committed	No way	possible	possible	possible
REPEATABLE READ	No way	No way	Allowed, but PG does not support	possible
Serializable	No way	No way	No way	No way

You can see from the table above that in PostgreSQL, the "READ UNCOMMITTED" isolation level, dirty reads are not allowed, "repeatable read" isolation levels, and Phantom reads are not allowed.

　　

PostgreSQL Learning (2)--MVCC