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?
