Non-transactional Database and Integration Testing

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.