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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
