• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

hibernate with postgres - deadlocks

 
Ranch Hand
Posts: 114
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi mates,

I have some really stressed application, kind of system, where customers are making actions triggering insert/updates in about 6-8 tables. Now I got traffic increased, when this action is performed about 30 times in a minute and deadlocks start to appear :/

I think with my hardware, 8 CPUs 2,4GHz each and 16GB of RAM it should be possible to serve a lot more.

So I believe I can upgrade my code. Right now I use one datasource and persistence unit for all reads and writes. They are created without specifing explicitly any isolation level. The deadlock comes on query, where I update one table, one column with amount that customer used in action. So it's basically like:

update table a set total_amount = total amount + amount what customer used with some simple subquery to aquire ID (this is one to many relationship, perhaps the issue is that I simply do like this:

where id = (select id from another_table where another_id = some_id) ?)

This update is not so important, so I don't care if get some uncommited data/small differences. What can I do about this? Is it good solution to create one more persistence unit with some read uncommited attribute?

With my knowledge the only solution I see is to create message driven bean and a queue, where those actions are performed one after another. But this can be problem if the traffic will go higher...

Could you help me with some advices?

Regards,
Michal
 
Michal Glowacki
Ranch Hand
Posts: 114
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have spotted something that could be source of the troubles, can anyone confirm I am right?

My update query was:

UPDATE table_A SET value = value + somevalue
FROM (SELECT a.A_id AS id FROM table_A a, table_B b, table_C c
WHERE a.A_ID = b.A_id AND b.B_id = c.B_id AND c.C_id = someid) AS stats
WHERE A_id = stats.id;

So in short, subquery was reading the table that was going to be updated - by mistake I took id from table_A when I could use foreign key A_id from table_B.

I think it could result in deadlocks? reading and writing in same time (during higher traffic hours)?
reply
    Bookmark Topic Watch Topic
  • New Topic