Re: Optimistic concurrency control

Frank Millman wrote:
> MS SQL Server uses a 'timestamp' column to check if a row has been
> altered since it was read. The equivalent in PostgreSQL seems to be
> the system column 'xmin'. However, the notes say 'You do not really
> need to be concerned about these columns, just know they exist.' Is
> it ok to use xmin for this purpose, or is there another way of
> achieving OCC?

If you set the transaction isolation level to Serializable, you get what
might be called optimistic concurrency control or optimistic locking
because all concurrent sessions are allowed to proceed in parallel
unless they attempt to alter the same row, in which case one of the
transactions is aborted. So you don't need to check yourself whether a
row was updated; the system will do that for you. You will find a
detailed description of the behavior in the documentation chapter
"Concurrency Control".

FWIW, the "pessimistic" alternative is using the transaction isolation
level Read Committed and explicitly locking all rows you want to alter
using SELECT FOR UPDATE. Again, read the documentation for details.

In any case, explicitly reading xmin and friends is not necessary.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/