In which the author learns that any great Web application developer needs to be half DBA

The latest section of study in my database tuning class has focused on transactions and crash recovery. As with table indices, I’ve been aware of transactions on, shall we say, a theoretical level: I knew what they were, and I knew that they were useful for grouping queries together for atomicity. However, it somehow didn’t occur to me how spectacularly important they are if you’re writing a web application that has relation constraints, in order to maintain database consistency.

Perhaps it was merely that my previous argument had been, “but each request will be executed in less than a second!” That doesn’t matter when you’re trying to serve content to, say, more than ten thousand requests in an hour. On average, that’s one request every 0.36 seconds. But we all know that it doesn’t work out like...

1. 0:00.00: Request
2. 0:00.36: Request
3. 0:00.72: Request
4. 0:01.04: Request
5. 0:01.40: Request
6. &c

That just doesn’t happen. At ten thousand requests per hour, you will almost certainly see overlap in your requests. This means, of course, that if you have an application that involves relational constraints, foreign keys, and really, anything where you have to reflect a change in table A in table B in such a way that every request is consistent, that change has to be applied to both tables before another request is allowed to query either. This is why you need transactions.

I never really thought about it in terms of even modest usage patterns like that. The textbook went over it in more depth, more in a context of what considerations a DBMS programmer has to be aware of, but it was a great reminder. Just two concurrent transactions, one writing two tables, and another reading those two, can display errors in the data if the read transaction reads table A after it’s been changed, but reads table B before its change has been applied.

It retrospect, it’s so obvious. Unfortunately, I haven’t ever—and I mean ever, in my entire professional career—met a web application programmer who actually used transactions, and didn’t just blithely let their database autocommit every update as soon as the query was completed. Nobody does this, and why? At a guess, because it takes extra programmer time. Only one PHP framework I know of forces transactions by default (hint: It’s not Zend… something else I need to add in to my Still Unnamed Project), but what about developers making their own frameworks? Not using frameworks? Using a framework that relies on the programmer to specifically start transactions? It won’t happen, because so much emphasis is put on reducing programmer time that no one really, properly, considers taking the extra hour, or day, or even, at worst, week, to plan things ahead and properly insulate against failure from step one.

But it does go to show how important proper planning is to being able to do this kind of work properly. Proper analysis of your tools and knowledge on the programmer’s part of what needs to go in to the software is what makes for better software in the long run. Seriously, take the little bit of extra time to do it right, and improve your product’s performance. How much does a day or two cost—once—relative to how much an extra server costs every month to compensate for inefficient code?

You learn something new every day

I’m willing to admit when I’ve been wrong, mistaken, or just poorly informed about something. I’m always happy to learn new tricks, and useful ways to improve my productivity, and my code’s efficiency… but a lesson I just learned last night takes the cake for things I wish I’d known years ago.

After more than two years spent outside of studying, I’ve finally started to get things back in gear to complete my B.C.Sc. degree (Brief explanatory digression: I had to suspend my studies a year before they were finished due to lack of funds, and then a series of layoffs after moving to Toronto has consistently interrupted my ability to resume). I’m currently taking two courses by correspondence: Small Business Management and Distributed Database Systems and Database Tuning. The first topic in the DB Tuning course is a brief analysis of difference database indexing methods, in terms of how the DBMS manages the pages and records under certain structures. I’ve been seeing indexing options for years now, fiddling with phpMySQLAdmin, but I’ve never really been clear on how the indexing worked, from an internal point of view. Never looked into it, just as I never looked into JOINing tables instead of relying on the WHERE clause… and when the light went on, reading the textbook, it nearly blinded me.

I’m not going to get into the details of it here, primarily because I don’t feel 100% confident in my correct understanding of the mechanisms used, but I realise now that I’ve been treating my databases as magical blackboxes a little bit too much. It’s a pretty rich statement, coming from the guy who says he does his work better when he has a more thorough understand of the whole system, from top to bottom. This kind of thing certainly demonstrates how true that is, though. For, literally, years, I’ve been treating DBMSes as though they just magically know how to organise my data to be able to get at the records I need quickly. I’ve strongly espoused letting the database do the hard work of returning a specific set of tuples, instead of pulling back a huge range of candidate tuples, and letting the host language (PHP, in my case) decided yea or nay about each record, on the grounds that the DBMS has been designed to do that kind of thing, and really well. And I’d like to think that’s still true, but man alive, it’s clearer than ever that I still have a lot to learn about writing database driven applications.

On that note, I unfortunately have to admit that my Zend enhancement/application template has stalled. But don’t worry, I already have an excuse lined up and ready to go: between my new job, going back to school and a play that’s been in rehearsal since the beginning of the year, I’ve had precious little time to work on my own, personal projects, which is unfortunately what that project is. Educational, certainly. Valuable to the wider community? Possibly. An interesting problem to work, most certainly. But also something that is simply not my highest priority, and my time feels pretty stretched most days. Besides, I’m still stuck at the logging functionality and integrating it better as a Model, and in turn, redesigning my Model superclass. I've been debating whether or not I should create an Object superclass as well, for better adherence to MVC, or whether that would just be unnecessarily reinventing the wheel (unlike the project, which I feel is a fairly necessary reinvention of the wheel).