CODEWITHSUNDEEP
javaspringpostgresqlhibernatejpa
Whimusical
Whimusical

Reputation: 6629

Issuing Dirty Reads in a transaction in Spring Data JPA with Postgres

I'm quite surprised that PostgreSQL removed Dirty Reads (Read Uncommited Isolation Level)in 9.6 (https://www.postgresql.org/docs/9.6/static/transaction-iso.html, but it was supported in at least 9.1 https://www.postgresql.org/docs/9.1/static/transaction-iso.html).

Now, my problem is a controller method that performs:

So I had wrapped the method within a @Transactional(readOnly = false, isolation_level = READ_UNCOMMITED), which is what led me to realize the isolation was not working, since second thread/transaction does not find the id even if it's been created by the first in case both transactions are still not committed and therefore, the code tries to insert the id twice.

So, how can this be solved with current standards, should we really mess with synchronization? Treat an AlreadyExistsException as control flow, and lose performance as we replicate the process?

Upvotes: 2

Views: 3824

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51456

mvcc NEVER supported dirty reads. But I can see it clearly stated in docs from as early as 8.0:

https://www.postgresql.org/docs/8.0/static/transaction-iso.html

In PostgreSQL, you can request any of the four standard transaction isolation levels. But internally, there are only two distinct isolation levels, which correspond to the levels Read Committed and Serializable. When you select the level Read Uncommitted you really get Read Committed

and from 9.1 Repeatable read added:

https://www.postgresql.org/docs/9.1/static/transaction-iso.html

In PostgreSQL, you can request any of the four standard transaction isolation levels. But internally, there are only three distinct isolation levels, which correspond to the levels Read Committed, Repeatable Read, and Serializable. When you select the level Read Uncommitted you really get Read Committed

update as a_horse_with_no_name noticed - internally, there are only two distinct isolation levels as from 7.1:

https://www.postgresql.org/docs/7.1/static/transaction-iso.html

Postgres offers the read committed and serializable isolation levels.

thanks to @Laurenz another note:

https://www.postgresql.org/docs/current/static/transaction-iso.html

Prior to PostgreSQL version 9.1, a request for the Serializable transaction isolation level provided exactly the same behavior described here (REPEATABLE READ). To retain the legacy Serializable behavior, Repeatable Read should now be requested.

or just quoting @Laurenz himself:

In 9.1, the old SERIALIZABLE was renamed to REPEATABLE READ because it did not guarantee that there is an equivalent serialized execution order. In the same release, "real" SERIALIZABLE was added, which is something quite different.

Upvotes: 1

Related Questions