Re: Lock table, Select for update and Serialization error

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