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.

enter image description here

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).

enter image description here

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:

  1. 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();
        }
    
  2. 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.

  3. Other things that were just poking around, hints on Stack Overflow, etc.

  4. 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:

  1. 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.

  2. Poll during Reads - if child objects are Null, delay, try again. Again, huge refactoring potentially.

  3. 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!

share|improve this question
Those "everything-related-to-a-guid-in-a-single-table" designs were horrible when in was just good-old-relational. Doing it with EF seems to me like somewhere between a nightmare and pornography. Sorry to be harsh, but I think you might be better in another industry. – smirkingman Aug 14 '12 at 19:47
How are you reading the data? – podiluska Aug 14 '12 at 21:00
Smirkingman - it's possible our design isn't optimal. We wanted to keep metadata in its own table because it is infrequently used. We also never delete objects (rarely, at least) from our database, so the Metadata also serves as a type of action log we can refer to. We have 31 (and counting) different objects that are all related (they were derived from a single entity, but Inheritance is an Achilles heel for EF, we found performance was horrible). – DR_Hydrologics Aug 14 '12 at 23:42
Smirkingman - Do you have a recommendation for how I can better read / write the data, or just want to be a critic? I'll admit to being an engineer first, and a .Net programmer 2nd, but I think I have a pretty good handle on what's going on. I'm open to suggestions on changing just about anything at this point, but, you know, you have to pony up a suggestion first! – DR_Hydrologics Aug 14 '12 at 23:46
podiluska - Not clear on the question, entirely. We have a single context instance (good idea / bad idea, it is what it is) for the life of the app. We read the objects from that single context, and have lazy loading on. It was perhaps naive to believe that EF's ORM was suitable to be used this way. If we had to do it again, I'm sure we'd be reconsidering our choice of using EF in the first place, but that's behind us now! – DR_Hydrologics Aug 14 '12 at 23:48
show 4 more comments
feedback

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.