Re: Transaction vs. Savepoints

James Long <pgsql-novice@museum.rain.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