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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
