Re: deadlock detected - when multiple threads try to update one table

Hi, thanks for the response ,

>
> 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

in each transaction i do basically the same stuff :

insert 1 row into table A, B, C (B and C have a
foreign key in A) without any sub queries - i just
plainy insert data..

then - update my table D row for today with some
numbers (as described)

> events consistent between the transactions? Does the
> table have any
> triggers, rules or foreign keys?

The table D in question does not have any triggers,
rules or foreign keys - it has only one PK and a few
numeric fields.

I dont understand why, when multiple connections do :

T1
update myTable set field1 = field1 + 1, field2 =
field2 + 5000 where myID = 1;

T2
update myTable set field1 = field1 + 1, field2 =
field2 + 2500 where myID = 1;

there should be no deadlock here - T1 just blocks T2
till the update is done, then T2 should be able to
update the same row..

Thanks
--- Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
wrote:

> 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 ?

__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs