Re: Lock table, Select for update and Serialization error

Thanks Laurenz for quick reply.

If this is the expected behavior then isn't 'Lock table' is just extra
performance penalty and achieves nothing under serializable isolation level.

The serializable isolation level in postgres is infact snapshot isolation.
Suppose a transaction T is using 'lock table' on table A and then
querying it.
Here T will be blocked untill all conflicting locks on A are released.
When there are no conflicting locks on A, T will go ahead and read data
from the snapshot taken at the T's start.

So, in short 'Lock Table' just delayed query of transaction T.

> LOCK TABLE should never give you an error, except for a deadlock
> resolution
> error.
>
> LOCK TABLE will just wait until there is no lock on the table that is
> incompatible with the requested lock, then it will obtain the lock and
> return.
>
> LOCK TABLE does not modify tables or rows and so you cannot get a
> serialization error, which is only issued when you run serializable
> and try to modify a row that is newer than your transaction begin time.
>
> On the other hand, LOCK TABLE will not necessarily prevent you from
> subsequently receiving serialization errors if you do not request
> an exclusive lock on the table.
>
> Does that answer your questions?
>
> Yours,
> Laurenz Albe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>