PostgreSQL: How to Set Isolation Level for SELECT Statement

isolation-levelpostgresqlpostgresql-9.5selectsnapshot-isolation

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?

Best Answer

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

Related Question