Hibernate and PostgreSQL: REPEATABLE_READ and use of @Version annotation to avoid write skews and ot-CodePudding

I'm using Isolation.REPEATABLE_READ to copy a large entity graph in a Spring Boot application. It basically executes SELECTs and INSERTs. I also employ optimistic locking using @Version annotation on all of the sub-entities. I see that even if I try to change something in the entity graph during the copy execution, the latter continues as if nothing happened. Let's assume that business-wise, this is accepted. According to SQL-92 Standard, the phenomena that are possible to happen are:

Dirty Read
Non-Repeatable Read
Phantom Read

Four additional ones (A Critique of ANSI SQL Isolation Levels: https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf):

Dirty Write
Read Skew
Write Skew
Lost Update

As far as I know, using repeatable read on PostgreSQL, only write skew is possible. But with optimistic locking, no inconsistencies occur. Is this thought correct?

CodePudding user response：

using repeatable read on PostgreSQL, only write skew is possible

Correct.

But with optimistic locking, no inconsistencies occur.

No, that is not correct. You can get “write skew” with optimistic locking:

CREATE TABLE worker (
   name text,
   on_duty boolean,
   version bigint DEFAULT 0
);

INSERT INTO worker (name, on_duty) VALUES
   ('alice', TRUE),
   ('bob', TRUE);

Now two transactions work on the table:

Transaction 1                             Transaction 2

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT name, version FROM worker WHERE on_duty;

 name  │ version 
═══════╪═════════
 alice │       0
 bob   │       0
(2 rows)

                                          BEGIN ISOLATION LEVEL REPEATABLE READ;

                                          SELECT name, version FROM worker WHERE on_duty;

                                           name  │ version 
                                          ═══════╪═════════
                                           alice │       0
                                           bob   │       0
                                          (2 rows)

                                          UPDATE worker
                                          SET on_duty = FALSE, version = version   1
                                          WHERE name = 'bob' AND version = 0;

                                          COMMIT;

UPDATE worker
SET on_duty = FALSE, version = version   1
WHERE name = 'alice' AND version = 0;

COMMIT;

Both transactions succeed, but you got write skew.