Re: [PERFORM] Incr/Decr Integer

On Thursday 16 July 2009 19:56:47 William Scott Jordan wrote:
> Hey all!
>
> Is there a better way to increase or decrease the value of an integer
> than doing something like:
>
> ---
> UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 123 ;
> ---
>
> We seem to be getting a lot of deadlocks using this method under heavy
> load. Just wondering if we should be doing something different.
Is this the only statement in your transaction? Or are you issuing multiple
such update statements in one transactions?
I am quite sure its not the increment of that value causing the problem.

If you issue multiple such statements you have to be carefull. Example:

Session 1:
BEGIN;
UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1;

Session 2:
BEGIN
UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2;

Fine so far.

Session 1:
UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2 ;
Waits for lock.

Session 2:
UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1;
Deadlock.

Andres

PS: Moved to pgsql-general, seems more appropriate