Why should I use Repeatable Read(or higher) isolation level if I need to read version the second time?

Question

I've read Martin Fowler's book chapter Optimistic Offline Lock

Author describes following example(if I understood it correctly):

There are 2 entites: Order and Client. There are 2 transactions(business) involved:

1. First transaction calculates order's taxes amount. Taxes amount depends on Order points and client address

2. Second transaction updates the client address

The problem here that if the client address will be changed while order's taxes amount calculation the result can be inconsistent. Authors offered 2 solutions based on optimistic offline locking. One of them is check order and client versions in the begining of the transaction and check order and client versions in the end of transaction. But here author warns that we have to use REPEATABLE READ isolation level or higher to be able to read versions second time. It is a cause of my question. As I understand if I read any row the second time I will get the same result because while using this isolation level DB locks all rows we've read before.

Please explain me the author idea.

Answer 1

I also have difficulty with understanding that part in the book. But, seems everything was very easy. At first let me copy that paragraph from the book:

There’s no reason why Optimistic Offline Lock can’t be used to detect an inconsistent read. In the example above the charge generation session needs to recognize that its correctness depends on the value of the customer’s address. It therefore should perform a version check on the address as well, perhaps by adding the address to the change set or maintaining a separate list of items to be version-checked. The latter requires a bit more work to set up, but results in code that more clearly states its intent. If you’re checking for a consistent read simply by rereading the version rather than an artificial update, be especially aware of your system transaction isolation level. The version reread will only work with repeatable read or stronger isolation. Anything weaker requires an increment of the version.

Here author just means that in case of Repeatable-Read and stronger Transaction Level you can overcome those issues as well. You will select Client Address in the beginning of transaction and Repeatable-Read will hold lock over that row in the table and as a result 2nd transaction can't change any address info.

Important detail to remember is:

In case of Repeatable-Read every lock acquired during a transaction is held for the duration of the transaction.

Answer 2

DB locks all rows we've read before only if it's required to do so. REPEATABLE READ isolation level is the minimum level requiring it. From PG docs:

Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run. However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed. Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts.

In case you run with REPEATABLE READ + isolation level, your transaction holds the read lock on the record. So the update will either succeed or the whole transaction will be rolled back (in case of a deadlock with another transaction trying to do the same PG will kill one transaction to resolve it).

In case you run with READ COMMITTED isolation level, you don't hold the lock. If another transaction changes the version field before you do, your UPDATE will not update anything (executeUpdate will return 0). So you may check it and act accordingly.

Bottom line, both options can be used, but the behavior is not same and your code should behave differently. If you use ORM, check carefully its behavior.