Friday, January 01, 2016

Rolling Back Time

Would it be nice if life had a rollback command?

If we found our day was unproductive, we could just issue the command and do the day again.

This was an unproductive day. So, I wrote a function to wrap the PDO database connection and decided to add support for the transaction processing features.

Transaction processing works as follows. You start a transaction. Run commands to insert and update tables. If something goes wrong; you can rollback the whole transaction.

I've worked on projects that made heavy use of transaction processing and have come to see transaction processing as a crutch for bad design.

Rather than using transactions, I used status variables and events tables. Let's say I have a piece of problematic code such as a call to an external credit card processor. I want a complete trace of information through the problematic code. If the transaction crashes, I can determined where it crashed and figure out why.

Anyway, I wasted the day testing the transaction features of different databases and have simply concluded that I don't even want any form of transaction processing in my code, and I would prefer not even seeing it on the server.

I just wish I could rollback this day and live it over on a more fruitful endeavor.

This page shows my SQL Wrapper code as it currently stands. The link shows a Test Script which calls the code. The test establishes connections to multiple databases including a remote DB.

I need to amend this post. I've reduced my need for transactions down to one simple operation: Updating a sequence. Updating a sequence requires two SQL statements: an UPDATE seq = seq+1; and SELECT seq;. One has to wrap those two statements in a transaction; otherwise there is a possibility of two transactions getting the same seq. I usually get the sequence with a stored procedure.

I wish Database manufactures would include a seq++ operation. An inline increment is one of the easiest pieces of code to write. Since people love the ++ operator, I am surprised that it doesn't exist already.

No comments: