Thread: deadlock detected - when multiple threads try to update one table
Hello, 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 ? Thanks __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
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?
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@megazone.bigpanda.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
On Fri, 2 Dec 2005, Harakiri wrote: > 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; I don't immediately see a reason either if it's deadlocking on the myTable changes. Can you build a self-contained example with schema?