Re: Do I need serializable for this query?

On Tue, 2007-04-10 at 14:45 -0400, William Garrison wrote:
> I have a table that keeps running totals.  It is possible that this
> would get called twice simultaneously for the same UserID.  Do I need to
> put this in a serializable transaction?  Since this gets called often,
> could it be a performance problem if I make it serializable?
>
> CREATE FUNCTION UpdateTotals(IN UserID int,
>                               IN AddToCount int,
>                               IN AddToSize bigint)
> RETURNS void AS $$
> BEGIN
>      UPDATE
>          Totals
>      SET
>          TotalSize = TotalSize + $2,
>          TotalCount = TotalCount + $3
>      WHERE
>          UserID = $1;
>      END IF;
> END
> $$ LANGUAGE 'plpgsql' VOLATILE;
>

You only have one command to execute; you don't need to do anything
special at all. If two UPDATEs happen to the same tuple at the same
time, one will block waiting for the other to commit.

Regards,
    Jeff Davis