Re: Transaction vs. Savepoints

James Long <pgsql-novice(at)museum(dot)rain(dot)com> writes:
> On Fri, Feb 09, 2007 at 11:21:59AM -0500, Tom Lane wrote:
>> Usually the best approach is to fetch the data without acquiring any
>> lock, allow the interactive editing to happen outside a transaction,
>> then when the user hits SAVE, perform a transaction that locks the
>> row(s), checks for conflicting changes, and commits if no conflict.

> Might you please expand on how the application could check for
> conflicting changes? Would this be simply fetching the record
> again, and comparing to the previously-fetched version, to see
> if the record is still as it was when the user started editing?

Comparing all the fields is certainly the most portable way. If you
don't mind being Postgres-specific you can instead check xmin and ctid;
if those are the same as what you read before, the tuple hasn't been
modified. So:

SELECT xmin, ctid, * FROM mytab WHERE whatever;

... let user edit data ...

BEGIN;
SELECT xmin, ctid FROM mytab WHERE whatever FOR UPDATE;
-- abort if xmin or ctid is different from before, else:
UPDATE mytab SET ...
COMMIT;

Note the FOR UPDATE, that's to avoid race conditions by locking the
tuple momentarily.

regards, tom lane