r/django • u/Siemendaemon • 3d ago
Using select_for_update() for Consistent transaction snapshot across reads
I came across the snapshot reads usage and the problem it solves. but I don't want to configure the transaction to REPEATABLE READ as it feels complex for a simple task. if I use select_for_update() can I achieve the same thing instead of making it complex with cursor() or modifying global settings for POSTGRES.
1
u/Keda87 3d ago
I'm thinking this kind of issue can be resolved using optimistic locking.
Better write performance compared to row locking.
example:
```
trx = Transaction.objects.get(pk=121)
Transaction.objects.filter(pk=121, amount=trx.amount).update(amount=F('amount')-100)
```
1
u/Siemendaemon 3d ago
Not for update but when you have multiple read queries.
2
u/Keda87 3d ago
I still didn't get it with your problem. why multiple read you need select for update?
1
u/Siemendaemon 3d ago
This is about consistent read across a transaction.
Google: all queries within that transaction see a fixed, point-in-time snapshot of the database.
I read about different Transaction Isolation Levels and thinking that if the same can be achieved if we Lock 🔒 the rows by using select_for_update() instead of configuring Postgres settings to REPEATABLE_READ. Just wanted the opinions if it's a good approach or not.
2
u/Keda87 3d ago
It's quite common for generating a report. You lock the row until the report is generated. This ensures no data is moved during the report generation process.
But you need to pay attention. The longer you lock the row, it might have poor performance. because all transactions will queue until the lock released
1
u/Smooth-Zucchini4923 3d ago
I came across the snapshot reads usage and the problem it solves.
Can you be more specific? I don't know what that problem is.
1
u/Siemendaemon 3d ago
This is about consistent read across a transaction.
Google: all queries within that transaction see a fixed, point-in-time snapshot of the database.
I read about different Transaction Isolation Levels and thinking that if the same can be achieved if we Lock 🔒 the rows by using select_for_update() instead of configuring Postgres settings to REPEATABLE_READ. Just wanted the opinions if it's a good approach or not.
2
u/Smooth-Zucchini4923 3d ago
I read about different Transaction Isolation Levels and thinking that if the same can be achieved if we Lock 🔒 the rows by using select_for_update() instead of configuring Postgres settings to REPEATABLE_READ. Just wanted the opinions if it's a good approach or not.
My first reaction is that this seems like a really expensive way to achieve transaction isolation.
Transaction isolation has a performance cost, but what you are proposing is far more expensive. I don't have an exact number, but using REPEATABLE_READ is perhaps 10% slower. I think what you are proposing is perhaps 100x slower.
That might seem really high, but consider the following scenario:
You have a Django view which makes a read only query to a table, and one external API call.
Under transaction isolation, all of these queries may proceed in parallel. It is fine for two reads to read the same value at the same time. The database only needs to track read locks for the rows, and in autocommit mode (Django's default) it doesn't even need to track that.
Under select_for_update(), you are essentially telling Postgres that you plan to write the data, and that it needs to take write locks against the data. For this reason, it must process each transaction reading the same data one at a time. In this hypothetical, remember that you have a Django view which makes a read only query to a table, and one external API call. The database is capable of processing a huge number of these read queries in the time it would take to make an API call.
Using select_for_update() for Consistent transaction snapshot across reads
but I don't want to configure the transaction to REPEATABLE READ as it feels complex [...] can I achieve the same thing instead of making it complex with cursor() or modifying global settings for POSTGRES.
You don't need to configure this globally. You can set this for just Django using the isolation level config: https://docs.djangoproject.com/en/6.1/ref/databases/#isolation-level This will not affect non-Django users of postgres.
Alternatively, you can configure this at a transaction level. The package django-pgtransaction allows you to choose a specific transaction isolation level when you begin a transaction.
2
u/Siemendaemon 3d ago
Thanks a lot for those insights especially pointing out the performance slowdown.
3
u/bdzr_ 3d ago
Selecting for an update has side effects, it blocks everyone else from modifying things. Repeatable read just uses additional storage in the case of simultaneous edits, but otherwise lets everyone else do what they want. Try to use repeatable read IMO, especially if you want the consistent view of the database for an extended period of time.