Thanks for you're help. I got it all figured out in the end. If anyone else reads this, this blog post summed up everything pretty nicely.
https://vladmihalcea.com/2014/09/14/a-beginners-guide-to-database-locking-and-the-lost-update-phenomena/ Also this explains the combination of locks and conflicts. https://msdn.microsoft.com/en-us/library/ms186396.aspx On Saturday, September 3, 2016 at 5:14:44 PM UTC+3, Gunnar Liljas wrote: > > Nhibernate can only offer what's supported by the DBMS. If you're using > MSSQL you can simulate the behaviour by executing SQL in two open SSMS > windows. Start a transaction in both, but hold off the rollback/commit. > LockMode.Upgrade is > > Select ... With(updlock,rowlock) > > > > > > On Sat, Sep 3, 2016 at 1:23 PM +0200, "David Meagor" < > [email protected] <javascript:>> wrote: > > I think I understand now. >> >> READ COMMITTED doesn't lock out rows that are being read, only those that >> are being updated. Seems I need either REPEATABLE READ, SERIALIZABLE >> isolation to lock read rows. >> >> I assume nHibernate won't interfere with these isolation levels. >> >> >> On Friday, September 2, 2016 at 11:16:10 PM UTC+3, Gunnar Liljas wrote: >>> >>> That's how it's supposed to be. The Find will not be blocked from >>> selecting the row, but it will be blocked from updating it, until it's >>> released. If both methods use FindForUpdate, however, the second select >>> will be locked until the first transaction releases the lock. >>> >>> Also, your "cacheable" thing looks a bit strange. Using Cacheable() on >>> the query will be pointless (actually it will possibly be slower) unless >>> the entity itself is cached, and if it is, it will be pulled from the cache >>> also when Session.Get is used. Maybe CacheMode.Ignore is what you want? >>> >>> /G >>> >>> >>> >>> 2016-09-02 18:06 GMT+02:00 David Meagor <[email protected]>: >>> >>>> We have two methods, Find (Get) and FIndForUpdate (QueryOver). >>>> >>>> If one process does a FindForUpdate to lock a row, and another uses >>>> FindForUpdate to query that same row. The second process pulls out the >>>> old >>>> modified data. I expected it to be locked out by the first. >>>> >>>> Process 1. >>>> var user = Repository.FindForUpdate<User>(id); >>>> //change ExternalSubscriptionId to 222222 >>>> // delay (10000) >>>> // end >>>> >>>> Process 2 >>>> var user = Repository.Find<User>().SingleOrDefault(x => >>>> x.ExternalSubscriptionId == "111111"); >>>> //shouldn't be able to find this user as the id has changed >>>> above and should have been locked our until the transaction was complete. >>>> >>>> Looking at the database profiler, the select query for process 2 >>>> actually runs first! >>>> >>>> Any idea why this is happening. >>>> >>>> >>>> >>>> public T Find<T>(object id, bool cacheable = false) where T : >>>> class, IIdentifiable >>>> { >>>> return !cacheable >>>> ? Session.Get<T>(id) >>>> : Session.QueryOver<T>().Where(x => x.Id == >>>> id).Cacheable().SingleOrDefault(); >>>> } >>>> >>>> public T FindForUpdate<T>(object id) where T : class, >>>> IIdentifiable >>>> { >>>> return Session.Get<T>(id, LockMode.Upgrade); >>>> >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "nhusers" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To post to this group, send email to [email protected]. >>>> Visit this group at https://groups.google.com/group/nhusers. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- >> You received this message because you are subscribed to the Google Groups >> "nhusers" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> Visit this group at https://groups.google.com/group/nhusers. >> For more options, visit https://groups.google.com/d/optout. >> > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
