Re: Lock table, Select for update and Serialization error

OK. In your example lock table command is used to avoid rollbacks due
to concurrent transaction.
So LOCK TABLE is useful in this situation.

I have one last doubt:
why there is difference between behavior of 'select for update' and
'lock table'.
one causes serialization error and other does not.
(even though both are variations of locking mechanism)

case 1)

T1# BEGIN -- snapshot taken
T1# Set transaction isolation level serializable;
T2# BEGIN -- snapshot taken
T2# Set transaction isolation level serializable;
T1# Update account set bal=bal-100 where accno=129;
T2# lock table account; -- *blocked*
T1# commit;
T2# -- lock obtained

case 2)

T1# BEGIN -- snapshot taken
T1# Set transaction isolation level serializable;
T2# BEGIN -- snapshot taken
T2# Set transaction isolation level serializable;
T1# Update account set bal=bal-100 where accno=129;
T2# select * from account where accno=129 for update; -- *blocked*
T1# commit;
T2# -- serialization error

> 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.
>