Run unit tests in parallel without db deadlocks?

[Bruno Wouters]

Hi all,

I would like to speed up our unit test run by letting two tests run a
the same time. But running two test threads at the same time causes
deadlocks on the database. Every test is contained in a transaction
and is rolled back at the end of the test(using
ISession.BeginTransaction & ISession.Transaction.Rollback). The
isolation level is readcommitted.

I was wondering if it is possible to prevent any locks on the
database. No test should/will never see data of another test because
it is always rolled back and never committed.

Is something like this possible?

Thanks!

[Jason Dentler]

Bruno,

Run each test against its own copy of the DB. You don't even need to rollback the transaction - just delete the copy. 

This works great if you can test with a lightweight RDBMS like SQLite. If you absolutely must test against something "heavy" like SQL Server or Oracle, this probably isn't a reasonable solution.

Jason

[Stefan Steinegger]

We are also running unit tests using SqlLite in memory. We create the
database before every test (just open the connection and use Schema
Export) and it is very fast.

We can switch to sql server by changing the build configuration. Then
it has a database for each test assembly, which is created once, but
tables are dropped and created before each test. This is quite slow,
but we rarely run tests on sql server.

On 7 Sep., 16:50, Jason Dentler <jasondent...@gmail.com> wrote:
> Bruno,
> Run each test against its own copy of the DB. You don't even need to
> rollback the transaction - just delete the copy.
>
> This works great if you can test with a lightweight RDBMS like SQLite. If
> you absolutely must test against something "heavy" like SQL Server or
> Oracle, this probably isn't a reasonable solution.
>
> Jason
>

[Bruno Wouters]

I tested sqlite before but it is quite difficult to get our 'start' database
converted to a sqlite one. I could indeed get the scheme there with the
Scheme Export function but we have some default data that also needs to be
in this test database.

Currently I'm testing IsolationLevel.Snapshot and allowing snapshot on sql
server in combination with running 4 threads at the same time (==cpu core
count). This seems to work great locally but on our teamcity server it is
just as slow as before. I'm still figuring out what could be the cause of
this.

Thanks for the replies!

[Stefan Steinegger]

I've implemented a simple mechanism to put data into the database. It
just creates entities and stores them to the database. This avoids
maintaining of sql scripts. In tests, we have mostly individual data
and store only what we need.

If you really want to run them in a sql server, I would run them in
different catalog (or "schema" or "database" or however it is called
next time). For that reason, you can implement a connection pool,
where every test gets another database when requesting a connection,
and after you run out of catalogs you let it wait using a lock
statement until another test releases one.

Isolation is important in tests. If they fail because of some
interaction coincidence, you've probably found an issue, but you can't
do anything because you can't reproduce it. Limit testing strictly to
things that are reproducible else it will drive you crazy.

[Billy Stack]

For a standard codebase, I usually mock up the database behind a
repository abstraction and run 90% of our tests this way. All business
logic should be testable outside the data layer.

Then for the other 10%, I create a "slow tests" project where tests
such as database testing, temporal testing etc are all included.

I usually only run the " slow tests" once/twice a day (using automated
build process. However when working with existing code, I just run the
unit tests for the core 90%, hence I get the best of both worlds -
fast unit test results + slow tests validation also

[Jason Meckley]

I also use SqLite for query tests. any "default" data needed for a
given test is added in the setup before the test is executed.

On Sep 8, 7:12 am, Billy Stack <bs.st...@gmail.com> wrote:
> For a standard codebase, I usually mock up the database behind a
> repository abstraction and run 90% of our tests this way. All business
> logic should be testable outside the data layer.
>
> Then for the other 10%, I create a "slow tests" project where tests
> such as database testing, temporal testing etc are all included.
>
> I usually only run the " slow tests" once/twice a day (using automated
> build process. However when working with existing code, I just run the
> unit tests for the core 90%, hence I get the best of both worlds -
> fast unit test results + slow tests validation also
>
> On Tue, Sep 8, 2009 at 8:01 AM, Stefan
>

[Bruno Wouters]

Inserting the default data in the setup is not really possible. We do insert
some test specific data during the test setup.

Mocking will be difficult because of the size of the database. I think it
will be too much work. Or can the mock be generated somehow?

Does anyone know how I can convert a database from ms sql server to sqlite
or ms sql ce? I've tried some tools a while ago but none of them gave good
results. The data should also be copied/converted.

Thanks for the responses!