Dojo Course - IRepository and locks on database

Topics: General, Magyar (Hungarian), Troubleshooting, Writing modules
Nov 15, 2013 at 6:09 PM
Hi,

I have a webfarm of two Orchard webservers. I had an issue that one server locked the database because I used a object from an IRepository and waited on a response of the other server which uses the same table. I think NHibernate manages these locks. What is the best practice to use IRepository with minimal change on locking? Or do I have to copy the object to a new object to prevent a lock? Do I have some control on the locking behavior?

By the way your course is really great!!
Developer
Nov 15, 2013 at 8:23 PM
And did this happen when you followed the Dojo Course tutorials? :-)

So a table-level locking occurred? Is it a built-in module's table? If yes, can you tell which operation caused this?

I have to say I never had to do with similar issues so I can't tell from experience. However AFAIK (but I'm not sure about these) you can do the following things from inside Orchard to alter how locking is done, neither particularly appealing:
  • Change the transaction isolation level if applicable: there's no API for this so you'd have to modify the SessionLocator class (by default the isolation level is ReadCommitted)
  • Lock the NHibernate session by hand: through the ISessionLocator dependency you can access the NHibernate session so you could also call Lock() on it.
  • Commit the transaction by hand through the NHibernate session
I'd assume from what you told that you wanted to read something that was being written simultaneously and the read waited the other transaction to be committed, what is the desired effect.
Nov 15, 2013 at 8:47 PM
The course explained the IRepository, but unfortunally the locking behavior was not included. Maybe in a following course?

It was my own module, of course :-)

Do you have an example of a commit of a transaction, because it needed read only information and I don't want a lock at all after reading the information. A commit will release the lock?

Some background (how it was before I rewrite it):
I have a table where all the servers in the farm register themself in. Each server has its own record. A server with a specific event looks up all the members and sends information to all other nodes about the event. All the nodes placed a confirmation in the same table and the sending server waits till the nodes has responded. --> Deadlock and a lot of sql time outs.
If the sending server can read the members and release the database I guess it went fine. I am curious if you can read the database without holding a lock.
Developer
Nov 15, 2013 at 11:57 PM
The reason we didn't talk (and neither intend to) talk about what I mentioned is that this is quite low-level stuff you shouldn't really need. Well, theoretically...

Committing the transaction is supposed to be a matter of ISessionLocator.For(typof(YourRecord)).Transaction.Commit(), but this is just me guessing as I've never tried it. ITransactionManager.RequireNew() also commits the current transaction (and then begins a new one). If you want to do this you should do it as soon as possible after finishing the write operation to release the lock. See the details of transaction isolation levels: http://technet.microsoft.com/en-us/library/ms173763.aspx

BUT: again, this is quite low-level and generally the best way is not to try to handle transactions yourself because if you choose the scope of the transaction wrong (i.e. what runs between its begin and commit) it can cause inconsistencies in the DB. Also, again, this is not something I've ever done (and also I don't deal much with SQL internals), I'm only telling from what I see in the code - so think through what you do.

I don't really understand how your setup can cause deadlocks. What do you mean by "the sending server waits till the nodes has responded". How does this "waiting" happen?
Nov 17, 2013 at 7:48 AM
I agree that it is low-level development and should not be needed for a simple usage like in my case to read some data. If I copy the properties of the retrieved object to a new object and pass the new object to other classes will also release the lock?

I send a web api call to the other servers and waited for the response to see if it succeeded. The receiving servers tried to update the same table before giving a response back. I rewrite it that the receiving servers do not update the database, but the sending server updates all records based on the result of the web api calls.
Developer
Nov 17, 2013 at 6:22 PM
I don't think the object copying would work, no. AFAIK basically the following happens when you write to a table from your module:
  1. Transaction begins (as with every request)
  2. You write to a DB row. This causes a lock on that row, probably also on the whole table (I think it should be just the row, but I'm not sure).
  3. Request ends, transaction is committed. The lock is now released.
As you can see if such locking happens (what, unless you allow dirty reads with the ReadUncommitted isolation level, will happen) you can minimize it's effect by minimizing the time between the start of the write operation and the committing of the transaction. If the whole request happens fast (as it is with WebAPI endpoints normally) then everything will be fine and you don't have to care about dealing with the transaction manually to minimize the effect of locking. However if you keep a request (and thus, a transaction) open for a long time (e.g. with some long-running calculation or even with a a Thread.Sleep() or something similar artificially) locking will begin to be an issue.

What you described (i.e. server tries to update a table that is also being updated by the other nodes while the API call happens) is indeed a bad design. I think how you solved is fine. Alternatively I'd do some back-and-forth calls between the master and slave nodes if some operation takes a non-negligible time (i.e. the master pings the slave do give some information, the slave returns an "OK, will do it", then later when finished the slave posts the result to the server in a separate request).
Nov 17, 2013 at 7:23 PM
Hi Piedone, thank you for clearing this out! In my case it looks like the lock was placed on the table when only a read has occurred and the lock is kept till the repository has been disposed. Maybe I have to repeat the test with the profiler enabled to see what happens under the hood.
Developer
Nov 17, 2013 at 9:38 PM
Locking with only read (no other process writing either)? Hmm, that sounds strange. Anyway, the solution you've come up with or the one I outlined should work nevertheless, but you could probably take a look at the snapshot isolation level that removed any locking for reads.
Nov 18, 2013 at 12:54 PM
I have done a look with the profiler.

If I read only one record, the whole table get a Shared locked, but it is released immediately. In the lock period more read requests can be done, but no one can update the table.

When I do an update on a record the table is updated if the controller has finished executing. The table gets an Exclusive lock when updating the table but is released immediately. I can not explain why I could not update the table after a read from another machine, but with a good design everything should go fine.
Nov 19, 2013 at 8:35 AM
I did some other tests to understand the locking behavior of NHibernate. Here a my observations:
  • A read on a repository -> A shared locked which is released immediately
  • An update on the repository -> No data to the database
  • Another read on the repository -> The table is exclusively locked but no update to the database, it reads the database with old data and merged the modified data
  • An update on the repository -> No data to the database
  • Another read on the repository -> The lock is still present, it reads the database but uses the modified data
  • A flush of the repository -> The updated data is send to the database but the exclusive lock is maintained.
  • Call of controller ended (view is showed) -> Exclusive lock is released
I think you have to be carefully with long lasting operations to use the repository if you do an update and read the table again, because it locks the table.
Developer
Nov 19, 2013 at 10:15 AM
Nice compilation!