How to lock a SELECT in PostgreSQL?

Question

I am used to do this in MySQL:

INSERT INTO ... SELECT ...

which would lock the table I SELECT from.

Now, I am trying to do something similar in PostgreSQL, where I select a set of rows in a table, and then I insert some stuff in other tables based on those rows values. I want to prevent having outdated data, so I am wondering how can I lock a SELECT in PostgresSQL.

Why would locking the source table prevent "outdated data"? – May 22 '19 at 14:54 — , May 22 '19 at 14:54

score 2 · Answer 1 · answered May 22 '19 at 15:01

2

There is no need to explicitly lock anything. A SELECT statement will always see a consistent snapshot of the table, no matter how long it runs.

The result will be no different if you lock the table against concurrent modifications before starting the SELECT, but you will harm concurrency unnecessarily.

If you need several queries to see a consistent state of the database, start a transaction with the REPEATABLE READ isolation level. Then all statements in the transaction will see the same state of the database.

answered May 22 '19 at 15:01

Laurenz Albe

209,280
17
206
263

I don't understand. If I do something like var data = SELECT statement, and then somebody modifies what I recently selected, wouldn't my data be outdated? – Hommer Smith May 24 '19 at 16:07
Yes, but it would also get outdated if you lock the table as soon as you release the lock. And you cannot hold the lock forever. So I don't see the benefit. The value was current at the time you selected, which should be enough. Perhaps you should share what you plan to do with the result value that would suffer. – Laurenz Albe May 24 '19 at 19:20

How to lock a SELECT in PostgreSQL?

1 Answers1