Re: deadlock detected - when multiple threads try to update

On Fri, 2 Dec 2005, Harakiri wrote:

> im using postgresql 8 under SLES 9 and RH 3.x - under
> both OS i encountered the following problem :
>
> Lets say i have a table, which has no reference to any
> other table - and i create one entry for each day of
> the year within this table (PK). During a day, i
> update the values within that specific entry - this
> entry will be updated from multiple threads.
>
> Under a higher load, i get a message from postgresql
> "deadlock detected" - im using JDBC to update/query
> postgresql.
>
> However, i do not understand why i would get a
> "deadlock detected" error message because i only
> INCREASE the values of the fields - i do not overwrite
> them - my statement simplified looks like this :
>
> update mytable set field1 = field1 + 1, field2 =
> field2 + 4200 where pkDate = someDate
>
> Now, multiple threads with a connection pool are
> connecting to postgres - and i can reproduce the
> deadlock problem when i use a test class which starts
> 1000 threads to update this table.
>
> Increasing the value of deadlock_timeout helps a bit -
> but solves not the problem - because i think there
> should not be a problem at all - the transaction
> management of postgres should be able to handle two or
> more threads which only want to increase the value o f
> specific fields - like in programming i++;
>
> Releated Questions
>
> a) Is there another way to increase field values
> within postgres other then field = field + MY_VALUE
> b) Is it a problem with postgresql or is it my code ?

It's hard to say with just the above. Are you doing other things in the
transactions besides a single update of that table and/or is the order of
events consistent between the transactions? Does the table have any
triggers, rules or foreign keys?