Description
Brian J. Sayatovic created an issue — 14th January 2013, 20:54:52:
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):
- <85> SELECT ... FROM tblOrders WITH (updlock,rowlock) INNER JOIN tblCustomerOrders ...
This gives <85> an "S" on tblCustomerOrders and an "U" on tblOrders- [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 Added .gitignore #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 patch for NH-2863 (NotExpression.GetProjections() bug introduced in NH-1280) #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.
Brian J. Sayatovic added a comment — 14th January 2013, 20:55:42:
I did not attach a test case. However, if someone can suggest a direction for a test case to cover the situation I described, I'll gladly write one.
Alexander Zaytsev added a comment — 14th January 2013, 21:05:44:
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
Brian J. Sayatovic added a comment — 14th January 2013, 21:33:38:
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).
Alexander Zaytsev added a comment — 14th January 2013, 21:50:31:
I think both variants are good.
Maximilian Haru Raditya added a comment — 10th September 2013, 17:09:05:
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.
Brian J. Sayatovic added a comment — 11th September 2013, 12:44:16:
I'm using ISession.Get(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.