MS SQL Server dialect(s) Upgrade locks on joined sub class leads to deadlocks
Description
Environment
Attachments
Activity
Brian J. Sayatovic September 11, 2013 at 12:44 PM
I'm using ISession.Get<T>(id, LockMode). From there, NHibernate uses its mapping to figure out how to apply that lock mode to whatever tables it thinks it needs. In my case, it decides it needs tblOrders and tblCustomerOrders. But the NHibernate code (as best as my novice eyes can tell) is designed to only apply the locking at one point in the SQL-query construction, and its at the point it adds the base table name in. When it adds in the join tables, the lock mode isn't added.
Maximilian Haru Raditya September 10, 2013 at 5:09 PM
Brian,
How do you issue the query?
IQuery provides SetLockMode() per table alias. It's just it has to be specified manually for each table alias.
Alex Zaytsev January 14, 2013 at 9:50 PM
I think both variants are good.
Brian J. Sayatovic January 14, 2013 at 9:33 PM
Thank you, Alexander. I've downloaded it and have started writing said test case(s). What I'm still uncertain of is whether it's sufficient to demonstrate that the MS SQL dialect generated SQL only locks the base class' table, or whether I need to produce a multi-thread test case that actually exercises MS SQL Server to reproduce the deadlocks (and avoid them when fixed).
Alex Zaytsev January 14, 2013 at 9:05 PM
Hi, Brian. Start by downloading this https://github.com/nhibernate/nhibernate-core-testcase/archive/master.zip
Then copy NH0000 to NH3375 and add your logic inside.
Also, please point how locking should look with MS SQL
Details
Details
Assignee
Reporter
Labels
Components
Affects versions
Priority
Major
The use of Upgrade locks with the MS SQL Server dialects (confirmed through MsSql2008Dialect) on subclass entities mapped with joined tables leads to deadlocks with high probability. Other database/dialects (e.g. Oracle) use the "SELECT ... FROM tblBase INNER JOIN tblSubclass ... FOR UPDATE" syntax which locks all rows involved in the query (e.g. tblBase and tblSubclass). However, SQL Server uses the "SELECT ... FROM tblBase (updlock,rowlock) INNER JOIN tblSubclass ..." syntax which locks only the row in the base class table, not the subclass table. This leads to deadlocks when concurrent threads are both in the midst of trying to update an entity fetched with Upgrade lock.
The nature of the deadlock is as follows (see attached Deadlock.png) with a base class (Order) and subclass (CustomerOrder) and two transactions (85 and 89):
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
3a. [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...
3b. [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.
Note that a deadlock still exists without optimistic version control except, but with a "U" lock on tblOrders from [85] instead of an "X".
While (N)Hibernate has the philosophy of locking the base table instead of the subclass table, this leads to very real deadlock situations. While locking the subclass tables instead of the base class tables would be a mistake, I believe locking the base table and subclass table would prevent the deadlocks already prevented by the base-class-only locking philosophy as well as the deadlocks described in this issue.
Note that there is a Hibernate issue, HHH-5436 ( https://hibernate.onjira.com/browse/HHH-5436 ), that touches on this base-only/base-and-subclass locking disparity.