Strange transactional interaction between insertion and deletion

I have a table

CREATE TABLE table (
    id SERIAL PRIMARY KEY
    foo INT
)

and I run the inserts at the same time:

INSERT INTO table (foo) VALUES (?)

and removes

DELETE FROM table WHERE ctid = (
    SELECT ctid FROM table -- <= call this query 'a'
    WHERE foo = ?
    ORDER BY id LIMIT 1)
RETURNING *

(i.e. delete the row with the smallest idthat meets some criteria)

When the isolation level is set to Serializable, I sometimes get ERROR: could not serialize access due to read/write dependencies among transactionsfrom delete. This makes sense: if an insert occurs at the time the query “a” is made, and when the delete wants to commit, the result of the query “a” is no longer consistent.

, . , , , 'a' ctid, , ctid . , ; , . ( , id ctid.)

?

+4

Source: https://habr.com/ru/post/1611498/


All Articles