I'm a Brazilian software engineer with 18+ years of experience building distributed systems with "boring" technologies like Java, SQL, Spring, and Hibernate.
One question, for your example, just using REAPEATBLE_READ on PostgreSQL would be sufficient, wouldn't it? I mean, or this issue is related to that we are doing an INSERT of a new row instead of an UPDATE in an existent row?
Thanks Rafael. Yes I think an update of a single row is safe in REPEATABLE READ because it reads (as of the start of query), then locks, then read again (the current version) and writes it. If no concurrent sessions have changed a column used in the where clause between the two reads, it is consistent (i.e the same as the read and writes occured at the same time). But if you read and write in different statement, or even in same update but different rows, you may write on a version different than the one that was read. SERIALIZABLE ensures that the read state didn't change until the write so that if the transaction completes read and writes logically occurs at the same time, as of the commit time
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Very nice article, Franck. Thanks for sharing it!
One question, for your example, just using REAPEATBLE_READ on PostgreSQL would be sufficient, wouldn't it? I mean, or this issue is related to that we are doing an INSERT of a new row instead of an UPDATE in an existent row?
Thanks Rafael. Yes I think an update of a single row is safe in REPEATABLE READ because it reads (as of the start of query), then locks, then read again (the current version) and writes it. If no concurrent sessions have changed a column used in the where clause between the two reads, it is consistent (i.e the same as the read and writes occured at the same time). But if you read and write in different statement, or even in same update but different rows, you may write on a version different than the one that was read. SERIALIZABLE ensures that the read state didn't change until the write so that if the transaction completes read and writes logically occurs at the same time, as of the commit time