3

Using PostgreSQL 9.5 I would like to make sure that when I run a SELECT statement I get consistent results, i.e. if the table is modified while the SELECT is running, I want to get the state that was valid when the SELECT started.

Basically, it's like: Take a snapshot of a table, and don't allow updates to the table while the snapshot is being taken, and then deliver the snapshot, while updates are allowed again.

If I understood isolation levels correctly, REPEATABLE READ is what I'm looking for, isn't it?

Now my question is: How can I run a SELECT with REPEATABLE READ isolation level? Or am I missing something, and my approach is wrong? How would I solve this?

4

You don't need to do anything to get statement level consistency

A query always sees a consistent state of the database regardless of the isolation level you use.

Quote from the manual:

This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.

See the chapter about Multi Version Concurrency Control in the manual

  • Okay, this is great news (as, in MongoDB this is different AFAIK). – Golo Roden Feb 4 '16 at 12:34
  • 6
    Well the NoSQL databases specifically give up on transactions, consistency and transaction isolation for performance (and scalability) reasons. – a_horse_with_no_name Feb 4 '16 at 12:36
3

Read committed (especially section 13.2.1) is the default read level in PostgreSQL.

This read level will give you a snapshot of what has been committed before your transaction starts. It will allow other transactions to read and write to your table, you just won't be able to see any writes made after the start of your transaction.

Does this only apply to SELECTs run in the context of a transaction, or also to SELECTs that are run alone, without an explicit transaction around them (i.e., just a single SELECT statement, e.g. manually entered to the REPL)?

Again from the documentation:

PostgreSQL actually treats every SQL statement as being executed within transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

So, individual SELECTs (one after the other) may see different contexts. There will be a difference if another user has modified your tables in the meantime.

However (and this should clarify) in a query with subselects - the individual subqueries will be consistent i.e. see the same context.

  • One last question on this: If I want to run two SELECTs, one after the other, and I want to make sure that both definitely see the same data, is it enough to simply wrap them within BEGIN and COMMIT? – Golo Roden Feb 4 '16 at 12:47
  • 4
    @GoloRoden not necessarily - if some other transaction commits before the second SELECT starts, the two may see different data. In this case, you might need REPEATABLE READ. – dezso Feb 4 '16 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.