SELECT / DELETE concurrent scenario

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?

CodePudding user response：

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.