Thread: Optimistic concurrency control

On Thu, Jan 13, 2005 at 11:49:00AM +0200, Frank Millman wrote:
> I have been reading up on 'optimistic concurrency control'.
>
> The following quote is taken from the on-line help for MS SQL Server 2000 -

<snip>

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

PostgreSQL already has concurrency control, it's called MVCC, so within
a transaction you will only see a consistant version of the database
without taking any locks. If you want to emulate it on top of that
on over multiple transactions, remember xmin represents the transaction
that created it, not the timestamp it was changed. If you really want
timestamps you'll need to manage that yourself...

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

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/

> 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,
It is. If you only need opportunistic locking within one
transaction you can simply rely on PG in serializable mode to
detect conflicts. However, if for various reasons you need to
detect data changes across transactions (eg. across
connections) then you need to use xmin. We do that in GnuMed
(http://www.gnumed.org) and it works nicely.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346