First, I think that your problem is *hard* to solve. I am of the strong opinion that you should separate the concern "order of DB operations because of dependencies (foreign keys)" (which NHibernate does; and which is the reason for its operation order) from your - additional - concern "order of DB operations because of deadlocks". There are at least three options I see:
1. Use "SELECT ... FOR UPDATE", i.e. lock the objects when loading them. NHibernate has options for that (LockMode, Lock method). The challenges with this option are at least that AFAIK (a) it is not implemented for all databases; (b) lazy loads will have not do a lock, so one needs to follw up with a call to Lock. It might be hard to make the code really deadlock-free, because Locks will be forgotten by the developers all the time ... A more radical approach is to wrap a mandatory framework over NHibernate, which requires the application programmers to get all objects they need in a request up-front (ideally, syntactically - I'd start with some generics where they "declare the types they need"); this framework component can then do a "Lock" on all things it loads. This defies lazy-loading (*all* objects must be gotten up-front!) and becomes hairy when you need to load objects based on complex conditions that need other objects loaded before. Still, I know about at least one project which went this way. 2. Write your own database driver for a "virtual deadlock free database" that does "intelligent reordering": Collect all the commands coming down from NHibernate, analyze them (you need to parse the SQL ... or maybe you instruct the driver to insert sepcial (preferably comment) strings into the generated SQL to find out which database "things" are modified), and then emit them in a "suitable better order". But when do you stop collecting, and do the emit? Of course, when the transaction finishes. But you will want that also on Flush (and maybe some other NHib operations) ... but the driver is, AFAIK, not informed about a Flush. You could add an interceptor that, on PostFlush, sends a special "SELECT 'Flush happend'" down to the database, which is picked up by your driver and starts the reordering+emitting process. Of course, the problem with this approach is that you must find an order that is also correct dpendency-wise; for example, in your example, the first sequence must still remain "INSERT ORDER, then INSERT ORDERITEM", because the order item has an FK to its order. I tell you that you will need some effort to define good formal rules to mix your two aspects. 3. Rethink your application. I am quite sure it has nothing to do with orders and order items - such systems rarely need to modify the same objects all the time: They are usually "mostly insert, then a little update" systems. Your system is very peculiar: Different clients need to change the same objects in the database "haphazardly" (in a random order). Of course, you use at least "snapshot isolation" (this is what it's called in SQL Server - others may call it differently) so that you do not have read locks - typically, this reduces the deadlock danger drastically. If you could use "Serialized", your problems should vanish - but I have never seen a real system which a somewhat large load that worked with satisfying performance with "Serialized". On a higher level, if you have really so much contention / concurrent changes on your objects, you might want to think about a CQRS-style architecture: Changes are not done immediately, but queued up as "commands", that can then be sequentially executed by a few background services. There are problems with this: If there is a feedback loop, e.g. to a GUI where the user wants to see his/her changes** (e.g. when you have triggers that change other objects) the next SELECT will have to *wait* until the commands have been executed. This requires at least (a) some coupling between the commands and the feedback selects (e.g. by some transaction id that is stored with the commands; when they have been committed, an event is sent to the waiting feedback query that it now may proceed) and therefore (b) some sort of "continuation" mechanism - i.e., the feedback selects cannot be written naively in the same sequential code - not even with an intermediate implicit or explicit Flush. The new C# await/async machinery looks like an interesting way to do this - but I have not really thought about this. That's my 2 cents ... Regards Harald M. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/DXpLBaRKHOIJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
