Re: [nhusers] How to influence order in which tables are updated on session commit?

[Oskar Berggren]

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 <juanita.v...@googlemail.com>

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 nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Oskar Berggren]

Does sql server CE support the needed SQL syntax to acquire the requested lock type? You might like to dig a bit in the relevant dialect classes in https://github.com/nhibernate/nhibernate-core/tree/master/src/NHibernate/Dialect.

/Oskar

2012/8/20 juanita <juanita.v...@googlemail.com>

Trying to acquire a lock was my first thought as well. However the corresponding session.lock statement does not seem to have any effect with my database (SQLServerCE) -- see my other post. 

Switching the database at this stage is not an option. SQLServer CE would generally allow anything we want from the DB, we just need to resolve this.

J.-

To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/_vQ_2Xqsr-gJ.

[Oskar Berggren]

This documentation section describes the statement order:
http://nhforge.org/doc/nh/en/index.html#manipulatingdata-flushing

You may be able to do what you request through clever ordering of your code and explicit calls to Flush(). A bit weird though.

SQL Server CE do seem to support locking hints similar to SQL Server:
http://msdn.microsoft.com/en-us/library/ms172398%28v=sql.90%29.aspx

Support for this is implemented in MsSql2000Dialect.cs (see AppendLockHint() etc.) but I can find nothing of this for the CE dialect implementations. Perhaps you can extend and adapt this code?

/Oskar

2012/8/20 juanita <juanita.v...@googlemail.com>

I am not sure how to check this. I took a quick look into the source, but haven't seen anything obvious.

However, the general questions, i.e. what determines the order in which NHibernate submits the SQL updates, still remains valid. Even with other database systems, accessing resources (which insert and update table essentially are) in different orders always has the potential to result in deadlocks.

There must be a way to make NHibernate issue the statements in the proper order.

J.-

To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/ulYYVG83_uwJ.

[Trinition]

It is indeed unclear at the finer-grained level.  For example, I'm interested in the order in which an object is persisted when it spans a base-class table and subclass-table.

On Thursday, August 23, 2012 5:48:30 AM UTC-4, juanita wrote:

The documentation is pretty clear on the order of statements regarding major categories (i.e. inserts before updates, before collection deletion, etc.). However, on the topic of the order within a category, the documentation merely states: "all entity updates" -- no more details.

I will attempt to use some explicit calls to Flush() - not nice, but acceptable. The alternative is a stateless session, but I would loose lazy loading, which hurts. 

I can also check the SQL Server CE dialect and see whether an explicit lock is of any help.

It would be nice if the problem could be resolved at the root, though. 

J.-