1

I know SELECT doesn't require row lock in Postgres. So I can issue a SELECT from some table T in connection 1, then someone else can issue a DELETE (of all rows in the same table T) in connection 2, and that DELETE won't cause any blockings. Right?

This is described here:

Does SELECT prevent returned rows from getting deleted?

OK... My question is: does this behavior depend on the ISOLATION LEVEL that's in use in either of the two connections?

Why am I asking all this?

In the real world, we have a similar concurrent scenario (as above) but with SELECT vs. TRUNCATE TABLE instead. And there we have a blocking problem because TRUNCATE TABLE (unlike DELETE * FROM TABLE) requires exclusive table lock (which it cannot obtain while a SELECT is running). So we're thinking to use DELETE * instead of TRUNCATE (even though DELETE is somewhat slower) to resolve this blocking issue.

  • Will this approach work?
  • And will the behavior depend on the ISOLATION LEVEL?
peter.petrov
  • 38,363
  • 16
  • 94
  • 159
  • 1) A `SELECT` can acquire a row lock if it has `FOR SHARE` or `FOR UPDATE` clause. 2) Read this [Transaction isolation](https://www.postgresql.org/docs/current/transaction-iso.html) for the various scenarios in the isolation levels. – Adrian Klaver Apr 27 '22 at 15:50
  • @AdrianKlaver Thanks. I don't have these special clauses in my SELECT. Just a normal SELECT. – peter.petrov Apr 27 '22 at 15:50
  • @AdrianKlaver And yes, I know this transaction isolation doc/tutorial but I can't quite figure out if it influences somehow the accepted answer in the question I referred to. That's exactly the point of me raising this new question. – peter.petrov Apr 27 '22 at 15:52
  • 1) You opened your question with: 'I know SELECT doesn't require row lock in Postgres.' I was just pointing out that is not always the case. 2) Assuming the only concurrent commands in play on the table are `SELECT` and `DELETE`, I would say you are safe ground with Read Committed and Repeatable Read. I have not worked enough with Serializable to comment. – Adrian Klaver Apr 27 '22 at 15:57

1 Answers1

1

Your solution will work, no matter what isolation level you use (with REPEATABLE READ or higher, you can of course get serialization errors, but that is unrelated).

However, TRUNCATE would be much more efficient. If you have trouble with the locks, that is an indication that you have long running transactions that use the table. Try to avoid that, because long transactions are problematic in general.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263