tm4j-developers

[Tm4j-developers] database deadlocks

[Pete Lewin-H. <pe...@il...>]

I'm getting a problem with deadlocks being reported by the database when I
load test tm4j using multiple threads adding, editing and deleting topics on
multiple topic maps. I'm running with the hibernate backend and a postgres
database.

I've googled this problem and it seems that it is a known postgres
'feature'. The point is that before updating a row the database locks for
update all foreign keys for that row. This can cause problems if there are
many foreign keys on a table (such as tmobjects in tm4j) due to different
threads trying to lock the same table rows in different orders (i.e. an add
locks rows in a different order to an edit) and so deadlocks can appear.

The soultion to this seems to be changing the schema to reduce the
likelyhood of deadlocks. I have managed to reduce the frequency of this
problem by reordering the foreign keys and removing the foriegn key from the
topicMap column on tmobjects to topicmaps but this is a fairly temporary
bodge.

cheers, Pete

Re: [Tm4j-developers] database deadlocks

[Kal A. <ka...@te...>]

Hi Pete,

Did you make these changes to the schema generated by Hibernate or did
you change the Hibernate mapping files ? If you did the latter, would it
be possible to get a copy of your changes ? If you had to hack the
schema generated by Hibernate then I'm a little less clear on the best
way to proceed - on the one hand this is an issue we should try to
address, but on the other hand I don't want to have to create and
maintain a set of schemas for different databases (that was one of the
reasons for using Hibernate in the first place!) - perhaps it could be
reported as an issue to the Hibernate folks though ?

Cheers,

Kal

On Fri, 2004-08-06 at 10:35, Pete Lewin-Harris wrote:
> I'm getting a problem with deadlocks being reported by the database when I
> load test tm4j using multiple threads adding, editing and deleting topics on
> multiple topic maps. I'm running with the hibernate backend and a postgres
> database.
> 
> I've googled this problem and it seems that it is a known postgres
> 'feature'. The point is that before updating a row the database locks for
> update all foreign keys for that row. This can cause problems if there are
> many foreign keys on a table (such as tmobjects in tm4j) due to different
> threads trying to lock the same table rows in different orders (i.e. an add
> locks rows in a different order to an edit) and so deadlocks can appear.
> 
> The soultion to this seems to be changing the schema to reduce the
> likelyhood of deadlocks. I have managed to reduce the frequency of this
> problem by reordering the foreign keys and removing the foriegn key from the
> topicMap column on tmobjects to topicmaps but this is a fairly temporary
> bodge.
> 
> cheers, Pete
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by OSTG. Have you noticed the changes on
> Linux.com, ITManagersJournal and NewsForge in the past few weeks? Now,
> one more big change to announce. We are now OSTG- Open Source Technology
> Group. Come see the changes on the new OSTG site. www.ostg.com
> _______________________________________________
> Tm4j-developers mailing list
> Tm4...@li...
> https://lists.sourceforge.net/lists/listinfo/tm4j-developers
-- 
Kal Ahmed <ka...@te...>
techquila

Re: [Tm4j-developers] database deadlocks

[Pete Lewin-H. <pe...@il...>]

Kal,

I made the changes to the schema generated by Hibernate so I do agree with
you that the way forward isn't clear.

The problem is fundamentally to do with postgres and the way it locks for
update all foreign keys. However in our base product we (seem to?) have
solved this problem by always creating the foreign keys in the same order
with in the database creation script and by avoiding as much as possible
tables that have foreign keys back onto themselves.

In the same way I have taken the tm4j generated hibernate schema and changed
the order in which the constraints are defined as well as removing the
foreign key from the topicMap column on tmobjects. This has stopped the any
deadlocks appearing. I realise that removing foreign keys  indiscriminately
is not the answer but for the moment I can't come up with anything better.

To be honest this is really more of an FYI post than an explicit issue.
Maybe something to keep in mind if you are bored on a wet Wednesday
afternoon and decide to re-acrchitect the entire schema ;-)

cheers, Pete


----- Original Message ----- 
From: "Kal Ahmed" <ka...@te...>
To: "Pete Lewin-Harris" <pe...@il...>
Cc: <tm4...@li...>
Sent: Wednesday, August 18, 2004 9:28 AM
Subject: Re: [Tm4j-developers] database deadlocks


> Hi Pete,
>
> Did you make these changes to the schema generated by Hibernate or did
> you change the Hibernate mapping files ? If you did the latter, would it
> be possible to get a copy of your changes ? If you had to hack the
> schema generated by Hibernate then I'm a little less clear on the best
> way to proceed - on the one hand this is an issue we should try to
> address, but on the other hand I don't want to have to create and
> maintain a set of schemas for different databases (that was one of the
> reasons for using Hibernate in the first place!) - perhaps it could be
> reported as an issue to the Hibernate folks though ?
>
> Cheers,
>
> Kal

>