[Dbix-class] Accumulating totals in a column

[Len Jaffe]

On Wed, Jun 13, 2012 at 4:24 PM, Bill Moseley <moseley at hank.org> wrote:

> Say I have a table "task" in a Postgresql database that has an integer
> column "total_seconds" which is a total number of seconds one or more
> actors (people, worker processes, etc.) spent working on the task.   So,
> these various actors need to add some value to an existing column.
>
> What is the recommended way to to add to an existing integer column like
> this and avoid race condition?
> I can see in the past I've used a customer result set:
>     UPDATE task SET total_seconds =3D total_seconds + ? WHERE id =3D ?
> but, I also have used  SELECT .. FOR UPDATE  ( i.e. { for =3D> 'update' }=
 )
> in a transaction.
> Is there any advantage of one over the other?
>

It would appear that for a single row update, they're generally equivalent,
depending on the nature of concurrent updates against the same row.
However, in a multi-row update, or a multi-table update it looks like the
'select for update' is the clear winner, as that ensures you have all the
rows you need locked up before you start your updates.

L.

-- =

lenjaffe at jaffesystems.com   614-404-4214             www.volunteerable.net
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar <http://www.greenbartraining.org/>: Grubmaster: 2012-2009, Grub
Asst: 2008, Trained: 2007.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120613/0f3=
a144b/attachment.htm