Lock table, Select for update and Serialization error

As per postgres docs, 'Select for update' is used to obtain row level
locks where as 'lock table' is used to obtain table level locks.

Under serializable isolation level, select for update gives error if
rows selected have been modified concurrently.
but 'lock table' does not give such error even though some of the rows

in the table are modified by concurrent transaction.

Is this the expected behavior?

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

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.

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

"Albe Laurenz" <all@adv.magwien.gv.at> writes:

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.

The critical point here is that LOCK TABLE commands at the start of a
serializable transaction are performed *before* taking the transaction's
snapshot (the snap happens at the first regular DML command). They not
only protect against post-snap changes as shown by Albe's example, but
against uncommitted changes that were made before transaction start
(by making the serializable xact wait until those changes are committed
or aborted before it takes its snap).

regards, tom lane

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

It is not necessary that LOCK TABLE will be the first statement.
(assuming serializable isolation level is snapshot isolation in postgres)
For serializable transaction, snapshot should be taken when the 'BEGIN'
statement is executed, and not when LOCK TABLE succeeds.
Hence, uncommitted changes should be invisible to serializable transaction.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of sudhir
Sent: dinsdag 22 mei 2007 19:28
To: Tom Lane
Cc: Albe Laurenz; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Lock table, Select for update and
Serialization error

[note: text reordered]

The critical point here is that LOCK TABLE commands at the

start of a

serializable transaction are performed *before* taking the
transaction's snapshot (the snap happens at the first regular DML
command). They not only protect against post-snap changes

as shown by

Albe's example, but against uncommitted changes that were

made before

transaction start (by making the serializable xact wait until those
changes are committed or aborted before it takes its snap).

[end reorder]

It is not necessary that LOCK TABLE will be the first statement.
(assuming serializable isolation level is snapshot isolation
in postgres) For serializable transaction, snapshot should be
taken when the 'BEGIN'
statement is executed, and not when LOCK TABLE succeeds.

Tom is correct, the snapshot is taken at the first DML statement, NOT at
transaction start (the "begin" statement). Test it yourself.
Your 'should' be might be the expected behaviour, but its not the
implemented behaviour.

As Tom is point out, the LOCK TABLE as the first statement is to prevent
serializable errors from happening.

Hence, uncommitted changes should be invisible to serializable
transaction.

Uncommited changes are at all times only and only visible to the
transaction that made those changes. No other transactions, of any
isolation level, can see uncommited changes from other transactions.
Remember, postgres uses the MVCC model.

- Joris Dobbelsteen

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of sudhir
Sent: dinsdag 22 mei 2007 19:21
To: Albe Laurenz
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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)

The locking level is at a very different level and you have to see the
implications of the diffent ways:

The LOCK statement is to prevent other transactions from accessing the
table. This is a high-level lock with very low overhead to take. The
disadvantage is obviously the performance impact is has, as it is highly
likely to block other transactions.
The mechanism is very useful to get some guarentees about what will
happen with the data in the table. This allows for synchronizing
modification between different transactions.

The select for update has two uses:
1) Ensure the data is current and remains so, for a small subset of a
table.
2) Prevent deadlocks caused by lock escallation.
What I didn't put explicitly is that select for update is to indicate
that a tuple will be updated.
For serializable it implies that the current version you see should be
current.

Obviously there is a common need for something with the concurrency
benefit of "select for update", but with relaxed requirements. The
postgres developers envisioned this and for this purpose use "select for
share".
The select for share only does:
1) Ensure the data is current and remains so, for a small subset of the
table.

Summarizing:
* Lock table - High-level: executes fast, but concurrency problems.
Guarentees about future changes.
Select for update - Low-level, concurrent, ensures data validity and
indicates its modified shortly.
Select for share - Low-level, concurrent, ensures data validity.

Hopefully this clears it up a bit.

- Joris Dobbelsteen

[snip]

Summarizing:
* Lock table - High-level: executes fast, but concurrency problems.
Guarentees about future changes.
Select for update - Low-level, concurrent, ensures data validity and
indicates its modified shortly.
Select for share - Low-level, concurrent, ensures data validity.

Hopefully this clears it up a bit.

Yeah, it does. Thanks for your help guys.

One last comment:

sudhir wrote:

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;

The 'snapshot taken' are misplaced.

The snapshots are actually taken when the first 'real'
statement in the transaction is executed.

In the case of T1, this would be at
Update account set bal=bal-100 where accno=129;

This is, as Tom Lane has pointed out, the reason why
locking the table as first statement in a transaction
will prevent serialization errors.

Tom said, 'the first DML statement', but as far as I know
a SELECT statement will also cause the snapshot to be taken.

Yours,
Laurenz Albe