Should I include SELECTs in a transaction?

Question

When using a database transaction to group multiple updates, should I include SELECTs inside the transaction as well? For instance, lets say I:

1. get a record
2. check edit permissions for that record, using data from the record
3. update some records
4. 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.

Answer 1

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.

Answer 2

Ideally (if possible) you would do all of your four steps in a single data-modifying CTE (which automatically happens inside a single transaction).

That still does not rule out race conditions, just makes them very unlikely, because the time frame between SELECT .. FOR UPDATE and a later UPDATE is minimized. (Yes, you still should use FOR UPDATE (or another appropriate locking level) to counter race conditions under heavy concurrent access.)

This is not the typical (inefficient) approach of a web-framework like Django. But it's the superior approach. It optimizes performance in a number of ways:

• Fewer round trips to the db server (probably most important)
• Minimize lock times
• Allow Postgres to optimize queries

When using SELECT .. FOR UPDATE in a data-modifying CTE, be aware that unreferenced CTEs are not executed at all, which would also not lock rows as intended.

Code examples for data-modifying CTEs:

• How to use UPDATE in PostgreSQL with variable table?
• Are SELECT type queries the only type that can be nested?

There are many more on SO. Try a seach.