The Easy Stuff is Hard

This morning I had trouble getting my kids to finish their breakfast in time. Progress was slow, lots of talking and procrastination, and finally I warned them that we had just five minutes left, and that they could only have as much as bread as they could eat in the remaining time – and no more. Which of course started me off thinking about database transactions.

A transaction, in case you didn’t know, is basically a non-divisible unit of processing that can have one of two end states: success or fail. These states are mutually exclusive and are followed by respectively a commit (changes are made definite) and a rollback (changes are rejected). Of course the scope of a commit or rollback is determined by a measure of time: at some point you have to tell the transaction to start, and at some other point you will need to instruct the database that the transaction has ended.

Transactions on ACID

What other properties define a transaction? Well, all transactions should behave according to the ACID properties:

• Atomic (a transaction cannot be broken up, it either commits or rolls back completely)
• Consistent (a transaction finishes leaving the system in a ‘correct state’, which begs the question what correctness means)
• Isolated (The transaction remains invisible to the rest of the system until it’s committed)
• Durable (The result of the transaction is stored permanently in the system)

Sounds straightforward so far, huh? That’s because we’ve not considered the implementation details yet. For example, your database schema & domain model should together describe the correct state of the database. This will help you in making sure that created orders all have order lines, and that a static discount – think of a 20% for a limited time – that applies to an order, cannot be deleted once it is given out. Anyone working with business-critical data will tell you that database integrity is simply everything. But integrity comes at a price, as we’re about to find out.

Feeling isolated on many levels?

A transaction must be isolated, but we do have control over the type of isolation that we can use. Let’s take a look, with the least isolated levels first:

• Read uncommitted: you can read anything in the database, no isolation at all, even on other transactions. Worst case scenario: using results from other – unfinished – transactions.
• Read committed: You only see committed transaction results, but you can still have a risk of unrepeatable read & phantoms.
• Repeatable read: better because we’re acquiring locks on all rows selected – but we still get haunted by phantom reads.
• Serializable: Best isolation level available. All transactions are handled in a serially, one after the other – though the order may be different from what you’ve specified.

While every next level looks better in terms of isolation, consider the fact that the overall performance may steeply decline. A heavily taxed database can be slowed to a crawl by having all transactions be serializable. So you have great integrity but horrible performance.

Pessimistic by Nature

In addition to ACID, you have to choose what to do with the record once you’ve read it – you have to think about the type of transaction. You can choose to either lock the entity during an update – in which case other processes have to wait to update the same entity  during the time one process is writing.  This is the pessimistic approach, and of course it impacts performance. Especially when multiple processes are trying to write to the same record, these may be waiting for an update opportunity for a long time.

Within the context of our breakfast table, the problem is analogous to the honeypot: as long as one daughter is putting honey on her bread, the other one has to wait until she’s done. When you have a lot of daughters wanting to have honey, you will end up with a queue of little girls just waiting around, doing nothing. And even though the waiting time for the last girl in the queue can be predicted in the design phase, after go live it always turns out to be longer than you expect (just like most systems I know actually).

Optimistic by Nature

The alternative is the optimistic approach, best summarized as ‘I hope no-one will need the record but I’ll leave it open anyway’. In the pessimistic approach we prevent conflicts from happening, but in this case we delegate the conflict resolving issue to the writing processes – nearly always this is a bit of code.

In the optimistic case, when two processes are in the process of updating the same record, both get an identical copy of the record. But the process to finish first gets lucky – it gets to write its changes back first. The record being updated invalidates all existing copies of that record given out. Now when the next process tries to write its changes to the database – based on the old invalidated data – you would instinctively expect it to overwrite the changes made by the first process. This is prevented by the fact that the database will recognize that the record is stale and instead of saving the record, it will return an error. The second process can now do one of two things to resolve this: either let the user know that the changes could not be saved, and allow for a retrial by the end user, OR you get a fresh copy of the record, update it again in the same way, and try to write it back to the database. No prize for guessing which is more user-friendly.

Apart from the more complex handling of persist actions, the other catch with this scenario is that when you have a large number of processes waiting to update the same record, you can run into serious performance issues. In a queue of 10 processes updating the same record, every time a process finishes an update action, the remaining 9 processes will each have to read another copy of the record, update it, and write it back. However, we know that only one will be able to succeed. That means that another round of updates is necessary, this time 8 records will need to read and update the record, repeating the previous process. That means that the system is taxed with both unnecessary database read actions, repetitive record updates, and also decreased performance since processes tend to run on for a long time.

You can see this approach in databases like CouchDB, where conflict resolution is similarly delegated to the updating process – usually, a bit of software. CouchDB is really cool example of a content-driven database BTW, if you get a chance to try it, I urge you to do so. Also, you see this approach in version control systems like SVN & CVS, where it’s called the non-exclusive checkout model.

What type of transaction to use?

The answer to this question depends largely on your context:

• How many times do you expect an entity to be written to? Once, occasionally, or every minute? Invoices tend to be written to just once, but as noted, page hits will be written all the time.
• Do you care more about performance, or more about the correctness of the data?

In addition, you will probably not want to use the same lock for every transaction. For example, when you perform a static postal code lookup for an address, you don’t need to lock those static records as Serializable – probably you can get away with Read Committed or – if the data is very dynamic – Repeatable Read.

When transactions don’t fit

Many web sites use a ‘write once, ready many times’ principle which shouldn’t require any transactions. Take a blog for example, once you publish a new post, you rarely find you need to make changes. When a user runs into read uncommited problems, it shouldn’t pose too much of a problem. But ask anyone who’s worked on enterprise systems, and the story is very different. And of course, everyone has run into the intricacies of web shops. How do you make sure the product is only taken out of stock once payment is secured? The answer is: use a transaction.

Boundaries

For example, a single purchase by a single customer is easy: only remove items from the stock once the payment is secured – that’s pretty obvious. But now consider an import – a large influx of data into the system – that happens regularly, let’s say on demand, user driven. So let’s imagine a legacy system that allows for regular imports, for example every week. On top of that, the imports are incremental – that makes it more interesting because existing records also need to be updated.

The question here: what’s the scope of your transaction? Do you want to lock each individual record? That works quite nicely because you’re locking existing records for the shortest possible time, even though this procedure requires you to parse data horizontally -  you have to follow the relations for each record. However, business needs might dictate otherwise: if you want to have every import to become available only after being processed completely, it means you might have to have pretty big transactions.

So it was on transaction boundaries that I went wrong with my kids this morning: I didn’t make clear how to proceed when a ’slice of bread’-transaction would take more time than the maximum time allotted… I didn’t specify how to handle a boundary conflict when it occurs. Either there should be a commit or a rollback, but a commit would exceed the maximum amount of time left to complete the transaction – remember, I didn’t want them to be late for school – and a rollback of a slice of bread, well… I suppose that’s theoretically possible, but it’s not a pretty sight at the breakfast table.

Then of course, I could’ve just told them to take one more bite and leave the rest of the bread as is on their plate. But that of course would have violated the first property of ACID: Atomicity. And that’s something that my programmer heart would not allow.

Summary

That about sums up the basics of what we can and cannot do with transactions. The prime consideration is with two non-functional requirements: performance of the system versus the accuracy of the data. There are other considerations which follow from here like usability (what’s an acceptable response time for users?) and reliability (what’s an acceptable level of fault tolerance?), but you can work those out for yourself, for example by looking at the ISO 9126 model (which is the definitive QA model in my experience – more about that in a future post).

In summary, what questions should we be asking ourselves whenever we are designing transactions? Here’s a suggestion:

• What kind of entities do we need to lock? Which rows, in which tables?
• Make sure your design for transactions conform to the ACID properties.
• Consider your desired isolation level. Done? Great. Now consider the appropriate isolation level for each entity.
• What’s the type of lock you want to use? Remember that you have to choose between performance and accuracy.
• How do you want to manage transactions: do you want to lock pessimistically or optimistically?
• What’s the boundary on any given transaction? Do multiple transactions go together, or would you rather split them up?

Hope you enjoyed this post. Let me know your thoughts on transactions in the comments below :  we’re all here to learn!

In my daily life, I pretend know a little about matters related to software development – mostly on the technical side. But like most developers I know, I strive to be better at what I do – developing enterprise software applications. Documenting the learning process is a part of that, that’s why I decided to start a blog.

This blog is an attempt to capture some of those otherwise fleeting thoughts that I have during a working day and expand a little on those. So this blog is mostly concerned with software development, version management, QA, methodology, etc etc.

So where do I stand professionally? Well, here’s a list of software development statements that I adhere to, not in any particular order, and I should say I rather steadfastly hold onto some of them, but not all.

• Scope is important: when you’re fixing a bug, it’s not necessary to ‘fix’ problems with other parts of the code. Try to find the easiest solution. It’s not likely that you will get paid for the work, and, more importantly, with new software comes new bugs – you might introduce more problems than you think.

• Design time is cheap: think ahead. If you’re somewhat experienced, you should be able to anticipate problems before they happen. Use that experience by thinking a little more about your design & architecture at the beginning of a project. If you end up maintaining the application yourself, you will get this time back. If you don’t, you will get back a lot of user satisfaction.

• Isolate your stuff, achieve flexibility. Don’t assume you’ll always have one database at a fixed address. Maybe you don’t want to do load balancing now, but at least allow room for it in your design and architecture.

• Test your stuff, and moreover: make it test-able. Anyone experienced with unit testing or TTD will tell you that adding unit tests onto existing code is hard to accomplish at best, and usually impossible without going through the pain of major refactoring surgery. Also, go for good coverage. And then still, 100%  code coverage is nice, but says nothing about the quality of your  tests – you may still be testing only within your best known scope.
  

  Initial speed may slow you down in the end

• Pay respect to the database. Databases are the backbone of your application. Nearly everything of what a web app does at some point touches the database layer. A slow database, or even a few slow tables can break your end user’s experience. Nobody likes a slug. It’s nice to have a framework that helps you with accessing the database. But it should still allow you to manipulate database schema and, if necessary, SQL queries. I can’t tell you how many times I’ve seen inefficient queries generated automatically from a database access layer, and that includes stuff I wrote myself, too..

• If anything sounds familiar, it’s probably because it is familiar. Object orientation has been around since the 1960s, design patterns were first done in the late 1970s. Agreed, aspect-oriented programming is relatively new, but basically these and other recent innovations are basically variations on older ideas. It feels like we’re in a lull now, awaiting the next huge paradigm shift or innovations. In the meanwhile, it’s up to us – meaning developers – to bring the tools we have to perfection.

Oh, and UTF-8? Yeah, you want to have that. Believe me. And please don’t say “it’s only a local application”. Your manager will be really pissed if you tell him the same three years from now, and he has to explain this to the sales team.