[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