Recomendations for working with local db/sqlserver

What is "best practice" for working with local db while using nCrunch..
This might be a more generic topic though..

I want to keep the data for my database inside the test project to ensure that I know the data.. I do not want a shared db solution

Any comment/ideas?

The best approach I've found here is by using transactions to isolate activities in the database, making sure you roll back a transaction at the end of your test run, and never commit.

In this way, your database is always left in a known state. Some ORMs use a pattern of storing the transaction or connection object in a ThreadStatic field or a named slot on the thread, so it's possible to tie the transaction to the thread. You could also do this yourself if your are calling your way into the database manually (i.e. DSQL/custom DAL).

Issues start to arise with this approach when you have multiple tests running in parallel, each one manipulating interdependent areas of the database. This can cause deadlocks, so the tests can hang. If you mark all tests that write data to the database using ExclusivelyUses("Database"), and all tests that only read data from the database as InclusivelyUses("Database") you can significantly increase the number of tests that can run in parallel.

Another option is to have a seperate database for each processing thread (or rather, execution process). This has its own advantages by removing the parallelisation constraints but the infrastructure for setting up the databases can then add to the length of the test run.

Not sure you'd want this here, but it's kind of related.

We have a code-base that's been using NCrunch for it's tests for a couple few years now, and today I upgraded to 3.23.0.10 and all of a sudden I'm getting random deadlocks with my tests.

Seems that if some happen to run together that change things in my DBContext then the deadlocks occur. So, if you run one separately it looks ok. If I add ExclusivelyUses("Database") it works okay. But I have a LOT of tests, I'd rather not add the attribute to each.

Is there some behaviour change with this update or a new global setting I need to change so that it behaves as before? I'm not sure how concurrent tests weren't deadlocking each other before and now seem to be.


Here is how I set up my test classes that have these DBContext interactions:

        private TransactionScope _ts;

        [TestInitialize]
        public void TestInitialize()
        {
            _ts = new TransactionScope();
        }

        [TestCleanup]
        public void TestTearDown()
        {
            _ts.Dispose();
        }

Hi, thanks for sharing this problem.

v3.23 didn't introduce any changes to the concurrency or pipeline behaviour, so the problem is unlikely to be caused by anything directly changed in this version.

However, v3.23 DID change the cache file version, which means that NCrunch might have started making different decisions on the sequence in which to execute your tests.

If you have tests changing things in a database and those tests are running concurrently, then deadlocks are inevitable. This is not related to NCrunch version, but is basically just transactional mechanics with the DB. You have a few options here:

1. As you've described, adding 'ExclusivelyUses' to each test (a bit of a chore, and you've fairly stated you'd rather not)
2. You can add 'ExclusivelyUses' at Assembly-level (i.e. in AssemblyInfo.cs). It'll then be implicitly added to each test in the assembly.
3. You can disable parallel execution ('Allow parallel execution' = 'false').
4. You can set the 'Max number of processing threads' to '1', thus deactivating concurrency.

Thanks, great explanation. Makes sense now, I'm rocking a faster rig and have amp'd up the concurrency; I'm probably just now noticing the underlying issue. I might move all the DB tests to their own project and use #2 as described

Thanks again