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

This article mainly explains "what is the vision prohibited by PostgreSQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the vision prohibited by PostgreSQL".

test data

Data table idx, which has an index on the id column. The default isolation level of the database is serializable.

15:44:16 [local:/data/run/pg12]: 5120 pg12@testdb=#\ d + idx Table "public.idx" Column | Type | Collation | Nullable | Default | Storage | Statstarget | Description-+- -+-id | integer | plain | | C1 | character varying | extended | | Indexes: " Idx_id "btree (id) Access method: heap [pg12@localhost pg122db] $grep 'isolation' postgresql.confdefault_transaction_isolation =' SERIALIZABLE'Write trigger

The sequence of operations is as follows:

Point in time T1T2T3t1begin

T2select * from idx where id = 1

T3

Begin

T4

Select * from idx where id = 10000

T5

Begin;t6

Update idx set C1 ='x' where id = 10000 witch t7

Commit;t8

Update idx set C1 ='x' where id = 1

Session 2 (T2) reports an error when performing a update operation

-- session 215 pg12@testdb=#* update idx set 46 where id 52 [local:/data/run/pg12]: 5120 pg12@testdb=#* update idx set C1 ='x 'where id = 1 * * error: could not serialize access due to read/write dependencies among transactionsDETAIL: Reason code: Canceled on identification as a pivot, during write.HINT: The transaction might succeed if retried.15:47:10 [local:/data/run/pg12]: 5120 pg12 triggers testdbtransactions read trigger

The sequence of operations is as follows:

Point in time T1T2T3t1begin

T2select * from idx where id = 1

T3

Begin

T4

Update idx set C1 = 'x1' where id = 1

T5

Begin;t6

Update idx set C1 ='x' where id = 10000 witch t7

Commit;t8

Select * from idx where id = 10000

Session 2 (T2) reports an error when performing a select operation

15:54:41 [local:/data/run/pg12]: 5120 pg12@testdb=#* select * from idx where id = 10000 * error: could not serialize access due to read/write dependencies among transactionsDETAIL: Reason code: Canceled on conflict out to pivot 423284, during read.HINT: The transaction might succeed if retried.15:55:16 [local:/data/run/pg12]: 5120 pg12 trigger testdbtransactions commit

The sequence of operations is as follows:

Point in time T1T2t1begin

T2select * from idx where id = 1

T3update tbl set C1 ='x' where id = 10000

T4

Begin;t5

Select * from idx where id = 10000 per T6

Update idx set C1 ='x' where id = 1bot T7

Commit;t8commit

T1 will report an error when performing commit:

16:07:50 [local:/data/run/pg12]: 5120 pg12@testdb=#* commit;ERROR: could not serialize access due to read/write dependencies among transactionsDETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.HINT: The transaction might succeed if retried. Thank you for your reading, the above is the content of "what is the vision prohibited by PostgreSQL". After the study of this article, I believe you have a deeper understanding of what the vision prohibited by PostgreSQL is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.