Re: Lock table, Select for update and Serialization error - Mailing list pgsql-general

sudhir wrote:
>> 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.
>
> 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.

I think that you still do not understand it completely.

Consider these two cases:

Case a)

Session 1 starts a serializable transaction T.
The first statement in transaction T will mark the time at which
the 'snapshot' that you mention above is 'taken'. Let's call this
time t1.

At a time t2 > t1, Session 2 updates a row on table r.

At t3 > t2, Session 1 tries to update the same row in table r.
Session 1 will fail with a serialization error.

Case b)

Session 1 starts a serializable transaction T.
The first statement in transaction T is 'LOCK TABLE r'. The statement
returns at time t1 which is the 'snapshot' time for transaction T.

At time t2 > t1, Session 2 tries to modify a row in table r.
Session 2 will have to wait until transaction T is completed, because
it cannot get a shared lock on the table.

At any time > t1, Session 1 can update the same row in table r
without receiving an error.


You see, there is a difference. In case a) the serializable transaction
will very likely fail if there are many concurrent changes on the table.
In case b), the serializable transaction will always succeed, while
all concurrent updates must wait.

Does that make sense to you?

Yours,
Laurenz Albe