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.
On Monday, January 7, 2013 10:34:26 AM UTC-5, Trinition wrote:
>
> 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.
>
--
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/-/fNalwUgQ5NUJ.
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.
