postgresql blocking behavior

All we need is an easy explanation of the problem, so here it is.

I would like to understand the blocking behavior of postgresql. Consider I have an insert statement starting at 1:00 (stage A) completed at 2:00 (stage B).

However, I have a few complex select statements:

S1, starting at 0:30, completed at 1:30
S2, starting at 0:30, completed at 2:30
S3, starting at 1:30, completed at 2:30
S4, starting at 1:30, completed at 1:45,

What would be the associated end result I shall get? From one test I had myself, I realize
S1, S4 -> stage A.

How about S2 and S3?

Edit:

Thanks for the answer, S1->S4 are all associated with stage A.

A more complex follow up question.

Imagine I have a view based on the db,

Create view db_view1 as select a+random() as a from db

The insert (I1) statement is

insert into db select * from db_view1

Again, I1 starting at 1:00 (stage A) completed at 2:00 (stage B). However, another client implement the same insert. 4 scenario below:

S5, apply another I1 starting at 0:30, completed at 1:30
S6, apply another I1 starting at 0:30, completed at 2:30
S7, apply another I1 starting at 1:30, completed at 2:30
S8, apply another I1 starting at 1:30, completed at 1:45,

For S5-S8, what happen in each of the scenario?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

By virtue of PostgreSQL’s multi-versioning implementation, no transaction will be blocked. Each of these queries will see the state of the database like it was before the insert started.

PostgreSQL takes a snapshot of the database at the start of the query, and it sees the data as they were when the query started. Also, you never see modifications from a transaction that was not yet completed when the query started.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply