Deadlocks on parent/child row with LockMode in table-per-class?

[Brian Sayatovic]

I'm trying to use LockMode to make sure the entity I get through my session locks the row(s) in the database.  However, the generated SQL (for MS SQL Server) appears to only be locking the base row.  This leads to deadlocks when NHibernate eventually updates the properties in the subclass mapping to the child table's row.

I fetch the entity through the session like this:

      CustomerOrder myCustomerOrder = mySession.Get<CustomerOrder>(myCustomerOrderId, LockMode.Upgrade);

 Customer order is a subclass of the Order base class, mapped with table-per-subclass:

      class Order {} // tblOrders

      class CustomerOrder : Order {} // tblCustomerOrders

The generated SQL (observed via NHProf) is:

      SELECT ... 

      FROM   dbo.tblOrders customeror0_ with (updlock, rowlock) 

             inner join dbo.tblCustomerOrders customeror0_1_ 

               on customeror0_.OrderID = customeror0_1_.OrderIDID

What I *think* I need is for the "(updlock, rowlock) to also follow tblCustomerOrders (here's one source: http://stackoverflow.com/questions/2577337/can-i-lock-a-record-from-a-join-sql-statment-using-rowlock-updlock).

So if that is what is needed so that the *entity* is locked, shouldn't NHibernate be doing this?

-- 

Regards,
Brian.

[Trinition]

Here's an example of the deadlock:

 

The transaction on the left is trying to do the select and it has already got a shared lock on the subclass table (tblCustomerOrders) and is seeking an upgrade lock on the base class table (tblOrders).  I guess thats how it works when there's a join.

The transaction on the right has already done the select so it already had a shared lock on the subclass table (tblCustomerOrders) and an upgrade lock on the base class table (tblOrders), and is now trying to issue an UPDATE to the subclass' properties, so it's seeking an exclusive lock on that subclass table.

[Trinition]

In an attempt to improve the locking situation, I added a 'Version' column to my case class table so NHibernate can get an exclusive lock on it when it updates the version.  However, I still have a deadlock, and it's almost identical.  The only difference in terms of my original deadlock graph is that process 85 now owns an exclusive (X) lock on the base-class table (tblOrders) instead of an update (U) lock.

But the deadlock appears to be happening because NHibernate is not acquiring any lock on the subclass table (tblCustomerOrders).

Using SQL Server Profiler to trace my statements and deadlocks, this appears to be the order of things:

1. [85] SELECT ... FROM tblOrders WITH (updlock,rowlock) INNER JOIN tblCustomerOrders ...
• This gives [85] an "S" on tblCustomerOrders and an "U" on tblOrders
  
2. [85] UPDATE tblOrders SET Version=3 ...
• This upgrade's [85]'s "U" lock on tblOrders to an "X" lock
3. [89] SELECT ... FROM tblOrders WITH (updlock,rowlock) INNER JOIN tblCustomerOrders ...
• This statement starts by obtaining an "S" lock on tblCustomerOrders, but then...
• it's blocked trying to get a "U" lock on tblOrders (because of statement #1 above)
• Meanwhile...
4. [85] UPDATE tblCustomerOrders ...
• This requires [85] to request an "X" lock on tblCustomerOrders where it previously only had a "S" lock.  But it can't because statement #3 has an "S" lock on it.

Hence [85] and [89] are deadlocked, just as before the optimistic locking (that only introduced statement #2).

Now John Davidson posted in a thread ("Dirty optimistic locking") back in August 2011 that locking on subclass tables is purposely not done so as to avoid deadlocks.  But I'm encountering deadlocks, I think, specifically because the subclass tables aren't locked.

Am I thinking correctly?  Is there a way out?

Regards,

Brian.

[Trinition]

There's a Hibernate bug, HHH-5436 (https://hibernate.onjira.com/browse/HHH-5436) that seems related.  That bug highlights that within a dialect (Oracle, in the case of the bug author) it does not consistently apply locks to joined-subclass tables depending on whether you get with the lock up front, or get without a lock, and then lock it later.  Thus, at the very least, the OracleXxxDialect is violating John Davidson's assertion that the subclass table is never locked.

Now I've considered writing a custom dialect, subclassing the MsSql2008Dialect, that tweaks things just enough to accomplish what I want -- locking the base class table and subclass table (going against John Davidson's assertion).  However, after tinkering around with the dialect usage via a debugger, I don't think this is possible.  I believe it is the AbstractEntityJoinWaller's InitStatementString method that is building the sql-select statement and appending the lock hint only to the first (base?) table.  So I can't simply modify the dialect and make it work.

On Monday, January 7, 2013 10:34:26 AM UTC-5, Trinition wrote:

[Trinition]

Created NH-3375: https://nhibernate.jira.com/browse/NH-3375

On Monday, January 7, 2013 10:34:26 AM UTC-5, Trinition wrote: