Relying on db transaction rollback in sunshine scenario

Question

In a financial system I am currently maintaining, we are relying on the rollback mechanism of our database to simulate the results of running some large batch jobs - rolling back the transaction or committing it at the end, depending on whether we were doing a test run.

I really cannot decide what my opinion is. In a way I think this is great, because then there is no difference between the simulation and a live run - on the other hand it just feels kind of icky, like e.g. depending on code throwing exceptions to carry out your business logic.

What is your opinion on relying on database transactions to carry out your business logic?

EXAMPLE

Consider an administration system having 1000 mortgage deeds in a database. Now the user wants to run a batch job that creates the next term invoice on each deed, using a couple of advanced search criteria that decides which deeds are to be invoiced.

Before she actually does this, she does a test run (implemented by doing the actualy run but ending in a transaction rollback), creating a report on which deeds will be invoiced. If it looks satisfactory, she can choose to do the actual run, which will end in a transaction commit.

Each invoice will be stamped with a batch number, allowing us to revert the changes later if it is needed, so it's not "dangereous" to do the batch run. The users just feel that it's better UX to be able to simulate the results first.

CLARIFICATION

It is NOT about testing. We do have test and staging environments for that. It's about a regular user using our system wanting to simulate the results of a large operation, that may seem "uncontrollable" or "irreversible" even though it isn't.

CONCLUSION

Doesn't seem like anyone has any real good arguments against our solution. As always, context means everything, so in the context of complex functional requirements exceeding performance requirements, using db rollback to implement batch job simulations seems a viable solution.

As there is no real answer to this question, I am not choosing an answer - instead I upvoted those who actually put forth an argument.

Answer 1

Generally speaking, you should break your logic into smaller testable chunks.

What would happen if someone forgot to run this in "test mode"? It sounds like a serious risk to me.

Answer 2

I'm not sure exactly what you're asking here. Taking you literally

What is your opinion on relying on database transactions to carry out your business logic?

Well, that's why we have transactions. We do rely on them. We hit an error and abort a transaction and rely on work done in that transaction scope to be rolled-back. So exploiting the transactional beahviours of our systems is a jolly good thing, and we'd need to hand-roll the same thing ourselves if we didn't.

But I think your question is about testing in a live system and relying on not commiting in order to do no damage. In an ideal world we have a live system and a test system and we don't mess with live systems. Such ideals are rarely seen. Far more common is "patch the live system. testing? what do you mean testing?" So in fact you're ahead of the game compared with some.

An alternative is to have dummy data in the live system, so that some actions can actually run all the way through. Again, error prone.

A surprisingly high proportion of systems outage are due to finger trouble, it's the humans who foul up.

Answer 3

It works - as you say. I'd worry about the concurrency of the system since the transaction will hold locks, possibly many locks. It means that your tests will hold up any live action on the system (and any live action operations will hold up your tests). It is generally better to use a test system for testing. I don't like it much, but if the risks from running a test but forgetting to roll it back are not a problem, and neither is the interference, then it is an effective way of attaining a 'what if' type calculation. But still not very clean.

Answer 4

Before she actually does this, she does a test run (implemented by doing the actualy run but ending in a transaction rollback), creating a report on which deeds will be invoiced. If it looks satisfactory, she can choose to do the actual run, which will end in a transaction commit.

That's wrong, prone to failure, and must be hell on your database logs. Unless you wrap your simulation and the actual run in a single transaction (which, judging by the timeline necessary to inspect 1000 deeds, would lead to a lot of blocked users) then there's no guaranteed consistency between test run and real run. If somebody changed data, added rows, etc. then you could end up with a different result - defeating the entire purpose of the test run.

A better pattern to do this would be for the test run to tag the records, and the real run to pick up the tagged records and process them. Or, if you have a thick client app, you can pull down the records to the client, show the report, and - if approved - push them back up.

Answer 5

We can see what the user needs to do, quite a reasonable thing. I mean how often do we get a regexp right first time? Refining a query till it does exactly what you want is not unusual.

The business consequences of not catching errors may be quite high, so doing a trial run makes sense.

Given a blank sheet of paper I'm sure we can devise an clean implementation expressed in formal behaviours of the system rather than this somewhat back-door appraoch.

How much effort would I put into fixing that now? Depends on whether the current approach is actually hurting. We can imagine that in a heaviliy used system it could lead to contention in the database.

Answer 6

When I was working in a company that was part of the "financial system", there was a project team that had decided to use the production environment to test their conversion procedure (and just rollback instead of commit at the end).

They almost got shot for it. With afterthought, it's a pity they weren't.

Your test environments that you claim you have are for the IT people's use. Get a similar "PRO-FORMA" environment of which you can guarantee your users that it is for THEIR use exclusively.

When I worked in that bank, creating such a PRO-FORMA environment was standard procedure at every year closure.

Answer 7

"But it's not about testing, it's about a user wanting to simulate the results of a large job."

Paraphrased : "it's not about testing, it's about simulation".

Sometimes I wish Fabian Pascal was still in business.

(Oh, and in case anyone doesn't understand : both are about "not perpetuating the results".)

Answer 8

I think it's an acceptable approach, as long as it doesn't interfere with regular processing.

The alternative would be to build a query that displays the consequences for review, but we all have had the experience of taking such an approach and not quite getting it right; or finding that the context changed between query and execution.

At the scale of 1000 rows, it's unlikely the system load is burdensome.

Answer 9

What I wrote about the PRO FORMA environment in that bank I worked in was also entirely a user thing.

Answer 10

It seems this question led to only subjective answers - therefore I'm closing the question.