Skip site navigation (1) Skip section navigation (2)

Re: Question Regarding Locks

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Question Regarding Locks
Date: 2004-10-28 13:56:27
Message-ID: 20041028155627.C726@hermes.hilbert.loc (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
Tom,

thanks ! You are even helping lurkers like me that haven't
asked anything :-)

...
> A better design is to fetch the data without locking it, allow the
> user to edit as he sees fit, and then when he clicks "save" you do
> something like
> 
> 	begin;
> 	select row for update;
> 	if [ row has not changed since you originally pulled it ] then
> 		update row with changed values;
> 		commit;
> 	else
> 		abort;
> 		notify user of conflicts
> 		let user edit new data to resolve conflicts and try again
> 	fi
> 
> In this design the row lock is only held for milliseconds.
> 
> You need to provide some code to let the user merge what he did with the
> prior changes, so that he doesn't have to start over from scratch in the
> failure case.

> BTW, a handy proxy for "row has not changed" is to see if its XMIN
> system column is still the same as before.  If so, no transaction has
> committed an update to it.  (This may or may not help much, since you're
> probably going to end up groveling over all the fields anyway in the
> "notify user" part, but it's a cool hack if you can use it.)

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

In response to

pgsql-novice by date

Next:From: Jeff EckermannDate: 2004-10-28 14:25:24
Subject: Re: Retrieve columntypes and checks?
Previous:From: Terry Lee TuckerDate: 2004-10-28 13:13:30
Subject: Re: Question Regarding Locks

pgsql-general by date

Next:From: Joshua D. DrakeDate: 2004-10-28 14:07:33
Subject: Re: Tables and Indexes
Previous:From: Patrick FicheDate: 2004-10-28 13:26:01
Subject: Re: Tables and Indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group