Can you force an SQL database to consider opoerations over a period of time to be in one transaction that you can roll back?

I am writing some integration tests for some software that relies heavily on information in its database. A majority of the logic behind our system runs via transactional SQL scripts and DDL/DML triggers against a large database.

When writing the unit tests we couldn't guarantee what order the unit tests would run in, so we had a baseline database state that each test would expect to be run from, with some data setup for each test which is a known constant. At the end of each test all of the changes it makes are rolled back. Because this was written in Java it was as simple as using the JUnit annotations:

@Test
@Transactional 
@Rollback(true)

Now that we are running integration tests we don't have access to the JUnit annotations. We are running the tests by starting up our software and then making API calls to it and verifying certain states in the database are what we expect them to be.

We still cannot guarantee what order the tests will be executed in, so we need each test to be run from a baseline state, and then have all of its changes reverted after we make our verifications. This we have constant knowledge about what state the database at the start for each test.

Is there some way to put the database we are testing in such a state that everything it considers from that point on as part of one big transaction that we can then roll back? I know we could just take a snapshot and restore from that, but depending on the size of the database and the amount of changes that each test may make it would be far far quicker to just be able to rollback.

from Newest questions tagged java - Stack Overflow http://ift.tt/1Qm8o55
via IFTTT