Thread: 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
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.