Shulou(Shulou.com)06/02 Report--

The difference between POSTGRESQL RC transaction processing and ORACLE MYSQL, as well as the example analysis of the problems in the PGFANS group, many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

A classmate asked a question in the PGFANS group. In one of his experiments, he found that PG and ORACLE used the same operation flow, and the results were inconsistent. So now we are going to verify and find some reasons that can be explained.

Test library name test

Test table test

test data

Id age

1 20

2 22

3 24

First of all, we need to confirm PG's quarantined RC mode, and I want to eliminate some problems that do not exist.

The following is the whole operation flow. Since several screenshots are needed, it is not easy to view them, so turn them into text and serialize them. Each line has the sequence number of execution and the SESSION to which it belongs.

1 test=# begin; SESSION 1 # start SESSION 1

BEGIN

2 test=# select xmin,xmax,cmin,cmax,* from test; SESSION 1

Xmin | xmax | cmin | cmax | id | age

-+-

2027732 | 0 | 0 | 0 | 2 | 40

2027735 | 0 | 1 | 1 | 1 | 20

(2 rows)

# View the current transaction status of each row

3 test=# select txid_current_if_assigned (); SESSION 1

Txid_current_if_assigned

-

(1 row)

# currently no transaction number has been generated

4 test=# begin; SESSION 2 # start SESSION 2 simultaneously

BEGIN

Test=# select * from test; SESSION 2

Id | age

-- +-

2 | 40

| 1 | 20 |

(2 rows)

# data found in SESSION 2

5 test=# select txid_current_if_assigned (); SESSION 2

Txid_current_if_assigned

-

(1 row)

6 test=# select txid_current_if_assigned (); SESSION 2

Txid_current_if_assigned

-

(1 row)

7 test=# delete from test where id = 1; SESSION 1

DELETE 1

Test=# select txid_current_if_assigned ()

Txid_current_if_assigned

-

2027737

(1 row)

# SESSION 1 deleted data

8 test=# update test set age = 100 where id = 1, SESSION 2 WAITING.

UPDATE 0

Test=# select txid_current_if_assigned ()

Txid_current_if_assigned

-

2027738

(1 row)

# SESSION 2 Update data is waiting

9 test=# select xmin,xmax,cmin,cmax,* from test; SESSION 1

Xmin | xmax | cmin | cmax | id | age

-+-

2027732 | 0 | 0 | 0 | 2 | 40

(1 row)

10test=# select txid_current_if_assigned (); SESSION 1

Txid_current_if_assigned

-

2027737

(1 row)

11 test=# select xmin,xmax,cmin,cmax,* from test; SESSION 2

Xmin | xmax | cmin | cmax | id | age

-+-

2027732 | 0 | 0 | 0 | 2 | 40

2027737 | 0 | 1 | 1 | 1 | 20

(2 rows)

# SESSION 1 insert data

12 test=# insert into test (id,age) values (1m 20); SESSION 1

INSERT 0 1

Test=# select txid_current_if_assigned ()

Txid_current_if_assigned

-

2027737

(1 row)

13 test=# select xmin,xmax,cmin,cmax,* from test; SESSION 1

Xmin | xmax | cmin | cmax | id | age

-+-

2027732 | 0 | 0 | 0 | 2 | 40

2027737 | 0 | 1 | 1 | 1 | 20

(2 rows)

14 test=# commit; SESSION 1

COMMIT

# SESSION 1 COMMIT

15 test=# select xmin,xmax,cmin,cmax,* from test; SESSION 1

Xmin | xmax | cmin | cmax | id | age

-+-

2027732 | 0 | 0 | 0 | 2 | 40

2027737 | 0 | 1 | 1 | 1 | 20

(2 rows)

16 test=# commit; SESSION 2

COMMIT

# SESSION 2 COMMIT

17 test=# select xmin,xmax,cmin,cmax,* from test; SESSION 2

Xmin | xmax | cmin | cmax | id | age

-+-

2027732 | 0 | 0 | 0 | 2 | 40

2027737 | 0 | 1 | 1 | 1 | 20

(2 rows)

Result: SESSION 2 does not update data inserted later in SESSION 1.

From the steps above, we can see or understand the features of PG.

Transaction ID is self-increasing

Each row of data will use (t_xmin, t_xmax) to mark its availability.

T_xmin stores the transaction ID that generated the tuple. It may be insert or the update statement t_xmax stores the XID that deletes or locks the tuple.

Transactions can only see tuples where the t_xmin is smaller than its own XID and has not been deleted

The above is the hint in the official document, which has clearly explained the above problems, and also gives some suggestions.

Have we finished our work or not? have we raised the isolation level of PG to RR.

The results are as follows:

SESSION 1

SESSION 2

After raising the level of SESSION, the result changes, operating in SESSION 2 again and reporting an error directly.

The final question is that the students who asked the questions reflected that the feedback of ORACLE and PG was different in similar situations. At the same time, I also do the above operation through MYSQL 8, which is the same as the situation reflected by that classmate.

Personally, I don't think this is a defect of the PG database itself, it is a trade-off between the database's handling of a complex situation and the isolation level of data consistency.

If you encounter such a situation, there are some suggestions as follows

1 can increase the isolation level of the database to RR (if you have similar business or operations in your database)

2 when designing the business logic, the business table is operated by logical deletion rather than physical deletion.

3 you can add statements like selecr for update during the operation to confirm the operability of the data.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.