Some thoughts: Maybe you can add code to explicitly begin with grabbing a lock on the order table for these use cases. This will serialize access, but it should prevent deadlocks. Perhaps by loading the order with an explicit lock. Or use a database engine with more fine grained lock handling.
/Oskar 2012/8/20 juanita <[email protected]> > As posted in another thread, I am struggeling with database deadlocks, but > since this is a different aspect, I have opened a new thread instead: > > The issue that I am trying to resolve is as follows: I have an application > that uses multiple threads to insert and update data using NHibernate and > SQL Server CE. I am getting deadlock issues, because: > > - thread #1 creates new entries for table ORDER and its children > ORDERITEM > - thread #2 updates ORDERITEM status and sometimes ORDER status (when > all are finished) > > Apparently, NHibernate generates the following SQL sequence > > - thread #1: insert into ORDER, then insert into ORDERITEM > - thread #2: update ORDERITEM, then update ORDER > > This is causing deadlocks with thread #1 holding a lock on ORDER waiting > for a lock on ORDERITEM and thread #2 the other way round. > Unfortunately, SQL Server CE has no way of preventing index PAGE locks, so > the threads will conflict, even if they do not access the same rows. > > I am clear why NHibernate is doing the inserts the way it does - due to > the PK/FK constraints, ORDER needs to be inserted first. > > Why does NHibernate submit the updates ORDERITEM, then ORDER? What > influcences that decision and do I have a way to make NHibernate submit the > updates ORDER then ORDERITEM? > The code in thread #2 loads ORDERITEM and ORDER, does the changes in > memory and commits the session. I does not do any explicit session.Update() > or .SaveOrUpdate() and the entire configuration is done using fluent > automapping with DefaultCascade.SaveUpdate. > > Thanks for any help. > > J.- > > -- > 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/-/DGBhHta79cMJ. > 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. > -- You received this message because you are subscribed to the Google Groups "nhusers" group. 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.
