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 field. -- 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