[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: RFC: Preventing double-updates

On Fri, Jul 13, 2007 at 01:05:01PM +0100, Ed W wrote:
> Also "locking" is usually a pain.  Much better to go for "optimistic 
> locking" which means that two uses are allowed to have a go at updating 
> or printing something, but one of them wins and the other gets returned 
> an error.  There are no locks to prevent either of them trying right up 
> to the point one of the transactions fails.  This simplifies the 
> interface by not needing an unlock interface for cases of lost sessions 
> (my CC payment processor does this - you can get locked out of the 
> system for 15 mins if you press links in the wrong order and hence loose 
> your session, but the system still thinks you are logged in - grrr)

Agreed. In the past, I've used a last_modified field to both track the
last time a record was updated as well as provide optimistic locking.
What I'd do is put a BEFORE UPDATE trigger on the table that would
compare NEW.last_updated to OLD.last_updated; if they were the same it
would allow the update. In the application you then need to ensure that
you grab last_updated when you pull data for display, and that you
include that in your UPDATE when you save changes; if someone else had
made a change in the meantime, last_updated would then be different and
the trigger would throw an error. In PostgreSQL, you could probably use
xmin in a similar fashion if you didn't want to add a last_modified
Decibel!, aka Jim C. Nasby, Database Architect  ..hidden.. 
Give your computer some brain candy! www.distributed.net Team #1828

Attachment: pgpnoLn658x3F.pgp
Description: PGP signature