One issue I think I am going to run into with my integration testing is having multiple tests accessing the same database. While this is not a problem now, I know we have multiple applications here that access the same database and I am just trying to think of a way to prevent this issue before it happens.

An idea I have seen a lot is using transactions. In the start up you start a transaction and then in the teardown you rollback the transaction. This means that multiple tests accessing the same database tables and will not effect each other which is great. The issue I have is that in my case, 85-95% of the tables I am working with in MySQL are MyISAM which don't support transactions.

Are there any ways to get around storage engines that don't support transaction but still allowing multiple test to access the same tables without them effecting each other? From what I hear, the ruby on rails testing framework uses transactions in this way, how do they get around this issue (or do they)?

  • Do you run tests simultaneously?
    – JeffO
    Commented Aug 16, 2011 at 18:58
  • I could. I would not run simultaneous tests of the same project but we do have multiple projects the access the same database/tables. I could see 2 projects running their tests at the same time.
    – ryanzec
    Commented Aug 16, 2011 at 19:03
  • 1
    +1 It is even harder if you need to run tests simultaneously.
    – KLE
    Commented Aug 29, 2011 at 21:09

3 Answers 3


In my company, there was that debate: persistency is a huge problem for testing.

There are some hacks that will get you halfway. We decided not to loose our time with them:

  • using Transactions that you rollback. That fails as:
    • You sometimes open an isolated transaction (inside a more general one), so it has been closed before your test code can get a grip on it
    • You cannot test your real persistence. (What happens when a test closes one transaction, and runs a complex querying affected by that data in a different transaction ; or saving data, and loading it again later on.)
    • You cannot test your code fully (only the layers below transaction level ; not good enough if you consider the drawbacks of integration testing).
  • using a fresh clean Database for each test. That also fails as:
    • our test suite will soon get to a few thousand tests (we have big teams). Setting up a new Database requires minutes at best (as we have many catalogs and parameters that are part of each version). Our test suite would soon take months to finish, and we can only wait a few days.
    • we would need a specific database instance for each developer, or each machine, and have a policy to launch only one test at a time. We need to use Oracle 11 to check our queries are fine, that would be too costly (Oracle licences).
  • carefully cleaning up after each test (testing frameworks provide hooks for running code after a test). That fails as:
    • it is very costly to maintain a perfect match between the code that does, and the test that undoes. As soon the match is not perfect, the symptoms are not clear, a test might fail while being a few hundreds lines later ; or a test that should fail could pass erroneously.
    • there are always edge cases where the cleaning is not correct (machine or software crash ?). That leaves an unknown state for later executions.
    • when a test shows an error, the database state would be be an essential information, but it would be lost (because we clean up before the next test, to finish the test suite and show the developer a full information).

So we moved to a policy we knew was valid:

  • have automatic Unit Tests for our requirements:
    • if needed, mock database accesses (but we usually don't need JMock, I could explain our design if asked to).
    • tests execute with a rate over 100 per second
    • tests are quick to write, short and clear to read.
    • tests do not depend on any persistent state, so they are fully isolated from each other naturally (no complex mecanism needed)
  • test integrating with the database separately (that is, request by request, not integrated with the application logic).
    • we considered automating that task, but it seemed too costly (see previous part of my post)
    • so we kept these tests manual: each developer that modifies a query is expected to test it again the database (that is usually part of his end-to-end tests through the user interface).

Even if you do not have "transactions", in the T-SQL sense, you should still strive to make your transactions (in the general sense of the term) atomic. The tests should not rely on eachother and they should be reversable. If you do not have any official rollback or transaction scope, then you might want to make your own. For example, you could have your unit tests perform a clean up, where they delete all the records that were created in the test.

  • That happens already without transaction (at the end, the tables are just emptied since I always start my tests with empty tables) but it doesn't solve the issue of multiple tests running at the same time. If I am running integration tests for application A and application B at the same and they both insert 10 records in to database.table_a, if I have a test to make sure that 10 records are in the table but I get a result of 10, the test would fail even though that test is working. This is the case I am trying to avoid.
    – ryanzec
    Commented Aug 16, 2011 at 19:01
  • @ryanzec - I do not quite follow. If you have a test to make sure that 10 records are in the table and you get a result of 10, then it seems the test will pass.
    – user3792
    Commented Aug 16, 2011 at 19:13
  • @ryanzec - I think I might see what you mean. If application A requires 10 records in the table to pass, but app B inserted a few extra at the same time, so A's test fails. In this case, you will need to write your tests differently. I would provide some sort of transaction number, so that I am not looking for any 10 records in a table for a test, but 10 records with an associated transaction number.
    – user3792
    Commented Aug 16, 2011 at 19:16

Just have each user or each run override the database used. That's what we do at work. Then you never have issues with 2 simultaneous tests interfering with each other.

We have each test run build the db up with migrations, populate the db with fixtures, and then tear them back down at then end.

If the DBMS supports transactions we use that as an optimization for the initial setup and teardown. They are optional, although your test can run a little long without it. As always YYMV.

No fuss, no muss.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.