Should I include SELECTs in a transaction?
database, django, postgresql, transactions
Solution
The short version: "It depends".
The long version:
If you're doing a read-modify-write cycle, then not only must it be in a transaction, but you must `SELECT ... FOR UPDATE` any records you later intend to modify. Otherwise you're going to risk lost writes, where you overwrite an update someone else made between when you read the record and when you wrote the update.
`SERIALIZABLE` transaction isolation can also help with this.
You really need to understand concurrency and isolation. Unfortunately the only simple, easy "just do X" answer without understanding it is to begin every transaction by locking all the tables involved. Most people don't want to do that.
I suggest a read (or two, or three, or four - it's hard material) of the tx isolation docs. Experiment with concurrent `psql` sessions (multiple terminals) to create race conditions and conflicts.
Problem
When using a database transaction to group multiple updates, should I include SELECTs inside the transaction as well? For instance, lets say I: - get a record - check edit permissions for that record, using data from the record - update some records - update some other records Should I start the transaction before the "get a record" stage, or just around the updates? I'm using Postgres/Django `transaction.atomic()` but I don't think it matters here.