Sat Oct 11, 2003 12:01 pm
Hello everyone, is there a pattern to avoid relational database deadlocks?
We update tables in a predefined order, but is not easy to develop since we
have to think of databases in the business layer.
Any input?
Thanks,
PabloSchor
I'd like to hear what others say about patterns of avoiding deadlocks. In my book, the "Aggregates" pattern addresses part of the problem. The model should not be a boundaryless web of objects, but a set of clumps which have fairly tight consistency rules within them and fairly loose coupling to other objects. Choosing these aggregates well, and they are part of the model, not just a technical contrivance, helps reduce such problems and at least narrows the scope within which you have to look for them.
EricEvans
Don't know if it can be useful to you but if you use topological sort
you may achieve the desired effect.
Example:
operationA depends on operationB
operationA depends on operationC
operationC depends on operationB
ergo perform
operationB, operationC and operationA
If you manage the dependencies as above somewhere in the persistence
layer you'll don't need to pollute the business layer: just collect
requests and then execute them in the TopologicalSorted order.
I think you can detect also circular references.
Hope that helps,
FedericoSpinazzi
Thanks, Federico, where can I find more information about the topological order? what happens if your database has more than 300 tables?
PabloSchor
I don't know of a java implementation outside a graph package (please
google for it).
However I think you can find c++ implementation in the boost library.
Since that code maybe difficult to read (heavily templated) may be you
can find the code and discussion at
http://www.relisoft.com/resource/topsort.html more useful.
300 tables are not a problem, I think, once you have computed the
ordering, it won't change and you can cache the ordering or the like
P.S.: I assumed you were searching for a java implementation, don't kow
why ...
FedericoSpinazzi
DbUnit contains a class DatabaseSequenceFilter that may be of interest. You should check out the sources from cvs from sourceforge.
Cheers,
FedericoSpinazzi
In order of ease...
1. Can you redefine the tables or are they a given?
2. Can you serialize updates, e.g. via separate tables and background
processes or via an asynchronous queue?
3. Can you define an order to acquiring pessimistic locks?
4. Can you retry a failed optimistic operation?
5. Can you explain a failed optimistic operation in domain language?
I think all of these can be based on and driven by a thorough
understanding of the domain model. I don't think your goal has to be to
suppress all concurrency below the level of a domain model.
PatrickLogan
Thanks, Patrick. We are developing a whole new application (haven't started to code yet). It's a J2EE financial application with more than 300 tables in a relational database, wich is also our design. We are using the DAO design pattern, and have the final decision in whatever design strategy we think is best.
To avoid deadlocks, we have a numbered list of tables and update only in that order. We use JTA for transactions, thus the business layer is responsible for the order of the updates. Doesn't sound good, that's why I wonder if there's one or more design patterns related to relational databases and deadlocks.
Thank you,
PabloSchor
PatternForDatabaseDeadlocks is mentioned on: ThreadView
