The idea behind the request token is another one of those simple-but-powerful patterns that I’ve come to rely on in various systems. I’ll jump right into an example of a case where I wanted to use it but alas I didn’t get to make the change before I left the job.

The architecture was a simple producer-consumer model. Some piece of the system was responsible for placing a row into a table and another was responsible for finding those rows and processing them. As it turns out, the system required many more consumers than producers, which I realize is not all that uncommon.

(Before you go screaming at me about “enterprise” solutions like Oracle’s Advanced Queueing or JMS, that’s not entirely the point. It’s incidental that this situation looks like a producer-consumer problem, but this pattern in more generally useful. So bear with me and think about how to apply it elsewhere.)

So, applying it to an email system where one piece of the system generates the emails and dumps them into a table and another piece of the system takes them out and sends them, you might have a table that looks like this:

CREATE TABLE email_jobs
(id NUMBER NOT NULL
,email_to VARCHAR2(255) NOT NULL
,email_subject VARCHAR2(255) NOT NULL
,email_body VARCHAR2(255) NOT NULL
,insert_ts DATE DEFAULT SYSDATE NOT NULL
,update_ts DATE
,processed_ts DATE DEFAULT SYSDATE NOT NULL

You can imagine the consumer might wake up, ask for the oldest 10 items in the table, send them off in batch, and then go back to sleep. As you might expect, I had a recurring problem where 2 consumers were both attempting to pull the same item from the table and process it. In the above case, a bug like that might lead to the person getting 2 identical emails, which no one wants. There are ways to protect against these kinds of things at the level, but in reality you just want to ensure that no 2 consumers get the same item.

Enter the request token. With this, each consumer produces the a unique indentifier and marks the rows that it wants with that value. It then requests only the rows with that token, making it virtually impossible to have the same row processed by 2 different consumers.

CREATE TABLE email_jobs
(id NUMBER NOT NULL
,email_to VARCHAR2(255) NOT NULL
,email_subject VARCHAR2(255) NOT NULL
,email_body VARCHAR2(255) NOT NULL
,request_token VARCHAR2(255)
,insert_ts DATE DEFAULT SYSDATE NOT NULL
,update_ts DATE
,processed_ts DATE DEFAULT SYSDATE NOT NULL

Notice the addition of the request_token column. On the application side:

//produces a unique number
$token = generate_token()


//mark some rows with the token – only where the request_token is already null – important!
UPDATE email_jobs SET request_token = $token WHERE <….find oldest rows…> AND request_token is null


//do this so other consumers won’t see these rows
COMMIT


//go back and find the ones that you marked
SELECT ej.id FROM email_jobs ej WHERE request_token = $token

Even if you have more than one process hitting that table, one of them will overwrite the other’s value for the request_token. Therefore, unless your application is sensitive to the number of rows each consumer processes, this is completely safe in that it won’t lead to multiple consumers processing the same row.

In general, the request token pattern pre-marks some data so that it’s easy to find later on. Another example that I’ve used in the past is in account creation. What frequently happens is that you have to insert a row and the update it soon after. The problem is that the insert generates a new unique ID that the update needs to know, but sometimes doesn’t. My solution has been to pass a request token to the code that does the insert and then pass that same value to the code that does the update. As long as the request token is unique they should both be able to address the correct row.

At this point you might have the idea to create the request_token column with a UNIQUE constraint so that no two rows can have the same value. Not so fast. In an even more useful case, there have been times when I’ve had to create a bunch of rows and then manipulate them in bulk. So, for instance, create a bunch of new accounts and set their email address to the same value. Without a column like the request_token, you’d potentially having nothing to group them by except for an insert_ts or similar column. With the request_token, it becomes a very easy thing to do.

Funny thing happens when you start to put data into a database. It becomes important. At one point it might have seemed like a nice idea to save the visitor’s IP address. Slowly, as the system evolves, little branches of code pop up around the fact that the IP address is populated. Suddenly you find yourself in a position where you have to protect that piece of data. You can’t sit by idly and let that improperly formatted IP address bring down the whole system. You have to guard your system against these intrusions.

And the intrusions will happen. I’ve designed a number of large systems, and the only common denominator is that somehow, at one point or another, at least some of the data will get corrupted. Transactions fail, databases crash, bugs show up in the margins, users enter in stupid information, or hackers attack. I had been spending time thinking about this issue at The Sporting News, but it wasn’t until MLB that it really congealed into something useful.

In the fantasy baseball domain, a typical roster transaction leads to the addition or removal of a player from a particular manager’s roster of players. If the player is added to player P’s roster he should not be available for any other manager in that league. If he’s removed from P’s roster he should be available to all other managers (including P). A player can’t be on more than one manager’s roster at the same time.

It turns out that every once in a while something hiccups and one of these rules is violated. Over the years I learned that the single most important step to fixing the problem is to make sure it doesn’t get worse. So, for instance, imagine a manager attempts to drop a player from his roster but something goes wrong. The system shows that the player is still on the roster, but he’s also technically available to others. Now that the data is corrupt it’s crucial that the system not allow the player’s status to be modified any further. It can very quickly become an impossible problem to solve if the player is picked up by another manager, then traded to another team, then dropped, etc.

I’ve spent many, many hours fixing transactions by hand. I worked on fantasy applications for over 7 years continuously, and in that time I can’t remember a single year where I didn’t have to fix at least some transactions by hand. Let me tell you, it suuuuucks. It really suuuuucks. Sucks and blows.

With that in mind, I developed a scheme where instead of waiting to hear that some player is on two teams via the message boards I take matter into my own hands. I designed the system to check each player involved in a transaction for corruption immediately after the transaction is committed. If I detect that one of the fundamental rules were broken (e.g. owned by more than one manager), the player is immediately frozen. No further transactions on that player would be allowed until an administrator can come in and fix the issue.

So for a small incremental cost I’ve bought myself some peace of mind. And I can absolutely tell you that it paid off, time and time again. It just took a different way of thinking about the problem – being proactive versus reactive, protecting the integrity of all that data.

I thought I’d start a loose series on PL/SQL for server-side developers. As a developer who has had to defend my use of PL/SQL in various systems over the years, I have some pretty strong feelings about what it brings to the table. I think of PL/SQL as a first-class language. That’s not to say that it can be used wherever Java or PHP are. What I love most about PL/SQL is that it fills some major gaps that Java and PHP (and most other traditional programming languages) have. When it comes to manipulating the database, anything Java can do PL/SQL can do better. In the context of the modern database application, that means that PL/SQL is an essential piece of any system.

Server-side developers in general have some serious hangups about PL/SQL. For one, it looks weird. What? No braces?!?!? Impossible!

Look, it’s not Java. Heck, it’s not even PHP. PL/SQL is its own beast, and you have to learn how to pet that beast so it doesn’t turn on you (and take your database down with it). If you think of PL/SQL as a simple means to tie some logic around DML operations (select/create/update/delete) it begins to make a lot more sense. It’s not supposed to be elegant. It’s not supposed to require hours and hours poring over thick books with fancy titles. It’s supposed to help you build better database applications, and at this I believe it excels.

So what’s so great about PL/SQL? Here are my canned responses to that question whenever some upstart developer starts spewing the crap he read out of his textbooks:

1. PL/SQL is compiled in the database. It always amuses me that a community like Java, which lives and dies by strong compile-time typing, is perfectly willing to let a major component of their application be loosely typed. You know all those JDBC calls/Hibernate mappings/iBatis queries? Little news for you Java dude. They’re completely unchecked. Put in terms you might understand – when you enter in a period, there’s no code assist to help you figure out how to complete the query. If I go in and modify the database in a few discrete ways your app will crash and burn. And you probably won’t realize this until a user sends a nasty email about why they can’t access the product they purchased. Not the case with PL/SQL. Since Oracle keeps them compiled in the database, you (or more likely the DBA) will know immediately if something changes in such a way that breaks the procedure or package.
2. Since they’re compiled in the database they will run orders or magnitude faster than the corresponding queries requested by a client application. The important concept here is called context switching. In short, it turns out all those trips back and forth the database tends to slow things down. It’s much much quicker to bundle up related queries in a procedure and make one call to the procedure. I once had an argument with a Java developer about result set sorting. He was convinced that it was much faster to sort a list of objects in Java than it would be to have the database do the ORDER BY and return the results. I like the guy, but that’s just insane. The overhead of fetching each of those rows and then doing some lame bubble sort on them is astronomical. But this is the kind of thinking that infests the server side community. It’s borne out of ignorance, sometimes willful, of what a database can do.
3. Another benefit of being in the database – they can be used by any client, not just ones written in Java (or PHP, etc.). When they talk about code reuse Java developers apparently don’t consider these kinds of issues. I’m sure it’s a wonderful learning experience to write a shipping cost calculator in Java, PHP, and JavaScript, but wouldn’t it make more sense to write it in PL/SQL once and then use it everywhere? Just a thought.
4. Believe it or not, most of the good DBAs I’ve worked with prefer complex logic to be wrapped up somewhere they can keep an eye on it. Remember, if something breaks at 3am they’re the ones that will get paged. Having all that business logic tucked away in a jar file somewhere makes then nervous. And when things do go bad they can help a lot more when the code is in the database. It’s better for everyone.
5. It takes about 15 minutes to learn enough PL/SQL to export some logic to the database. Sure, PL/SQL goes deeper than that, but any curly-brace type programmer should be able to absorb the concepts easily.

Now, I’m not going to say it’s all win-win. Moving business logic into the database has a dramatic effect on system design. You’ll find a lot less justification for something like Hibernate, for instance (ok, maybe that’s a win). I’ve been through this a couple of times, I know it’s hard to find the appropriate place to draw the line in terms of what gets moved into the database. Should you go balls to the wall and have the database return cursors for select statements? I usually don’t, but I have in some instances. Should every insert/update/delete be wrapped in a stored proc? Again, not an easy call.

In my most recent fantasy baseball app, I let the client code only insert into temporary tables, and then called a stored proc to validate the data and move it into the destination table. People look at me like I’m crazy when I tell them about this. But you know what? I’d do it again. If you buy the premise that inserts are dangerous because Java code can’t type-check them, then it’s the right way to do it. Temporary tables and stored procedures are much easier to change than Java code at most “serious” companies. It’s a matter of necessity to do it that way.

Hopefully I’ve covered the “whys” of PL/SQL convincingly enough. In a future post I’ll cover some basics of the “hows”.