Re: Incr/Decr Integer

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.

Thanks!

-William

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

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 ;

No.

> We seem to be getting a lot of deadlocks using this method under heavy
> load. Just wondering if we should be doing something different.

You can't get deadlocks with that - it only references one table.

What is the purpose of this query - how are you using it?

--
Richard Huxton
Archonet Ltd

William Scott Jordan <wsjordan(at)brownpapertickets(dot)com> wrote:

> We seem to be getting a lot of deadlocks using this method under
> heavy load.

Could you post the exact message from one of these?
(Copy and paste if possible.)

-Kevin

Hi Andrew,

That's a very good guess. We are in fact updating this table multiple
times within the same triggered function, which is being called on an
INSERT. Essentially, we're using this to keep a running total of the
number of rows being held in another table. The function we're using
currently looks something like this:

---
CREATE OR REPLACE FUNCTION the_function() RETURNS "trigger"
AS $$
BEGIN;
UPDATE the_table
SET first_column = first_column + 1
WHERE first_id = NEW.first_id ;

UPDATE the_table
SET second_column = second_column + 1
WHERE second_id = NEW.second_id ;

UPDATE the_table
SET third_column = third_column + 1
WHERE third_id = NEW.third_id ;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
---

For something like this, would it make more sense to break out the three
different parts into three different functions, each being triggered on
INSERT? Or would all three functions still be considered a single
transaction, since they're all being called from the same insert?

Any suggestions would be appreciated!

-William

Andres Freund wrote:
> 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

Hi Andrew,

That's a very good guess. We are in fact updating this table multiple
times within the same triggered function, which is being called on an
INSERT. Essentially, we're using this to keep a running total of the
number of rows being held in another table. The function we're using
currently looks something like this:

---
CREATE OR REPLACE FUNCTION the_function() RETURNS "trigger"
AS $$
BEGIN;
UPDATE the_table
SET first_column = first_column + 1
WHERE first_id = NEW.first_id ;

UPDATE the_table
SET second_column = second_column + 1
WHERE second_id = NEW.second_id ;

UPDATE the_table
SET third_column = third_column + 1
WHERE third_id = NEW.third_id ;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
---

For something like this, would it make more sense to break out the three
different parts into three different functions, each being triggered on
INSERT? Or would all three functions still be considered a single
transaction, since they're all being called from the same insert?

Any suggestions would be appreciated!

-William

Andres Freund wrote:
> 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

On Thursday 16 July 2009 23:20:34 William Scott Jordan wrote:
> Hi Andrew,
>
> That's a very good guess. We are in fact updating this table multiple
> times within the same triggered function, which is being called on an
> INSERT. Essentially, we're using this to keep a running total of the
> number of rows being held in another table. The function we're using
> currently looks something like this:
>
> ---
> CREATE OR REPLACE FUNCTION the_function() RETURNS "trigger"
> AS $$
> BEGIN;
> UPDATE the_table
> SET first_column = first_column + 1
> WHERE first_id = NEW.first_id ;
>
> UPDATE the_table
> SET second_column = second_column + 1
> WHERE second_id = NEW.second_id ;
>
> UPDATE the_table
> SET third_column = third_column + 1
> WHERE third_id = NEW.third_id ;
> RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;
> ---
>
> For something like this, would it make more sense to break out the three
> different parts into three different functions, each being triggered on
> INSERT? Or would all three functions still be considered a single
> transaction, since they're all being called from the same insert?
>
> Any suggestions would be appreciated!
You need to make sure *all* your locking access happens in the same order.
Then you will possibly have one transaction waiting for the other, but not
deadlock:

The formerly described Scenario now works:

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 = 1;
Wait.

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

Session 2:
Still waiting

Session 1:
commit

Session 2:
waiting ends.

UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2;
commit;

Sensible? Works?

Andres

William Scott Jordan wrote:
> Hi Andrew,
>
> That's a very good guess. We are in fact updating this table multiple
> times within the same triggered function, which is being called on an
> INSERT. Essentially, we're using this to keep a running total of the
> number of rows being held in another table.

This is the worst way to go about keeping running totals; it would be
far better to have a table holding a "last aggregated value" and deltas
from that; to figure out the current value of the counter, add the last
value plus/minus the deltas (I figure you'd normally have one +1 for
each insert and one -1 for each delete; update is an exercise to the
reader). You have another process that runs periodically and groups the
deltas to generate an up-to-date "last aggregated value", deleting the
deltas.

This way you should have little deadlock problems if any, because no
transaction needs to wait for another one to update the counter.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.