I wanted to do some stress testing to simulate the multi-user environment for my application. We're using Entity Framework (EF) as our ORM, with SQL Server 2008 R2 Enterprise for our back-end.
Here's a portion of the model - every object (NodeBase
) has Metadatum
objects - 1:1 mapped. It appears that the NodeBase
is getting written first, and then the Metadatum
object 2nd (or the association to the Metadatum
object, not sure). Either way, when I execute a search, I pull up a NodeBase
, but the Metadatum
is null. 5 seconds later, the same search reveals the Metadatum
object. :-/. Frustrating.
In my test, I have 2 instances running of our application (The GUI for reading, and unit tests for R/W). The unit tests hammer the database, creating worst-case scenario reading & writing of objects, and the GUI is reading all the changes to the DB that happen (hammering the DB with requests for new data).
The issue appears to be that my search engine is reading a newly written object, and processing it, before the object's children and written. When the search goes to process the children, there's a null exception. If the search waited a few seconds (nothing newer than DateTime.Now.AddSeconds(-5)
seems to work well), everything would be fine. While this works for my specific current problem, I doubt it won't come up in other places in the app.
What I've tried:
Transaction in my writing code (I might not have all the options right, or could be doing it wrong), but that doesn't seem to have any effect. Every write uses this transaction:
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions(){IsolationLevel = System.Transactions.IsolationLevel.Serializable})) { ContainerResult = OasisModelContainer.SaveChanges(false); OasisModelContainer.AcceptAllChanges(); transactionScope.Complete(); }
Tried pre-emptively writing my child objects to the database first, in a separate DB Context, dispose the context, re-read the child in the current context, attach to the parent object (in the transaction), but that doesn't work.
Other things that were just poking around, hints on Stack Overflow, etc.
I tried using snapshots, though I'm not confident I'm using it correctly - I set this on my DB:
ALTER DATABASE [ObjectModel] SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE [ObjectModel] SET READ_COMMITTED_SNAPSHOT ON
What I'm thinking about:
Adding a WriteLock boolean to every object that is set during write, and any time an object is read it would only proceed if the WriteLock was cleared. This has huge implications (refactoring a lot of code), and isn't preferable.
Poll during Reads - if child objects are Null, delay, try again. Again, huge refactoring potentially.
Adding a Table/DB lock to the DB during writes. I don't know enough about SQL Server to know if this is smart or stupid. Can you lock the DB or Table in such a way that it doesn't cause deadlocks? Can you set a SQL Server Deadlock timeout so the client app doesn't timeout?
Any help/pointers/advice would be greatly appreciated!