Michael J. Swart

August 23, 2011

ACID Properties By Example (And Counterexample) Part Four: Durable

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , , — Michael J. Swart @ 9:00 am
ACID Properties By (Counter) Example

The last ACID property is D, Durability. Again, Haerder and Reuter describe Durability:

“Once a transaction has been completed and has committed its results to the database, the system must guarantee that these results survive any subsequent malfunctions.”

What does this mean exactly? That’s a tall order for a database system! I mean any malfunction whatsoever? I’m pretty sure our database systems are designed to survive a power failure but I don’t expect that they could survive something as severe as the heat death of the universe.

Actually databases don’t have to go that far. When designing a database system, only two kinds of malfunctions are considered: media failure and system failure.

Media Failures

For media failure (e.g. a faulty hard drive) databases are recovered by using backups and transaction logs. And this leads directly to three bits of super-common DBA advice:

  • Take backups regularly.
  • Keep your transaction logs and your main database files on different hard drives.
  • When dealing with a disk failures, step one is backing up the tail of the log

System Failures

System failures (e.g. system crashes, power outages etc…) have to be handled too.

SQL Server does it this way. When SQL Server is processing transactions, it will first write changes to a transaction log and then write the associated changes to the database file. Always always in that order (There’s a bit more too it, but that’s the main part). It’s called the Write-Ahead Transaction Log.

But when there’s a system malfunction, a few things need to be cleaned up the next time the server restarts (to maintain atomicity and consistency). There may be transactions that were interrupted and not yet committed. And some transactions may not have their changes written to disk, or sometimes not written completely to disk. How do you recover from stuff like that?

Well the database recovers from a failure like that during a startup process called (unsurprisingly) “recovery”. It can look at these half-performed transactions and it can roll them back using the info in the logs. Or alternatively it can roll-forward and replay committed transactions that haven’t made it to disk if the conditions are right and there’s enough info in the transaction log to do so. (Further Information at MCM Prep Video: Log File Internals and Maintenance)

So What Does This Mean To You?

If an ACID database system like SQL Server reports that your transaction has committed successfully then because it’s durable, your transaction is truly persisted: You don’t have to worry about buffer flushes or power failures “losing” your work.

Example

So what is interestingly durable? Durability in database systems usually means that something is redundant so that if one thing is lost, the transaction is not lost. So I give a list here of things that are too redundant:

  • The Hydra‘s heads (Greek Mythology)
  • Enchanted Brooms from the Sorcerer’s Apprentice.
  • Autofac (An interesting short story by Philip K. Dick which I finished reading last night).

Counter-Example

I have two examples and they both come from the career of Richard Harris (best known to my family as the first Dumbledore). Did you know he was a one-hit wonder? He had a hit single in the seventies called MacArthur Park. If you’ve never heard the song, skip this article and experience the utter madness that is MacArthur Park. You won’t regret it.

Back to the example. The singer of MacArthur Park would like to have his cake. Unfortunately, it’s been left out in the rain (malfunction). But that’s okay right? He could always get out the recipe (transaction log) and make a new one right? Wrong! He’ll never have that recipe again (durability fail). Had he persisted that recipe, the poor sucker would still have his cake.

Bonus Richard Harris Counterexample

You may remember he played Emperor Marcus Aurelius in the movie Gladiator. (Spoiler alert!) In that movie, he plans to make Maximus his heir instead of his son Commodus. He first tells his plans to Maximus (who is reluctant to rule Rome) and then he tells Commodus who did not take the news well at all. In fact he murdered his father after hearing it!  The Emperor’s plans never make it to the public and so Commodus becomes Emperor.

You see, his plans to make Maximus his heir was not durable! Had the Emperor told a bunch of other people first, then his intended heir Maximus would have ruled Rome as he wanted (Not to mention it would have removed the motive for his murder).

That’s The Series

So that’s it. I had fun with it. It gave me a chance to “geek out”. And even though blog post series are a nice way of treating a topic in depth, I still found myself struggling to keep each article to blog-post length. There’s just so much to learn here. I guarantee I learned more writing this series than a reader would reading it ;-)

Tell me what you think!

6 Comments »

  1. A brilliant finale to a tremendous series. Nicely done sir.

    I’m off to practice my restore strategy to cater for the heat death of the universe……

    Comment by John Sansom — August 23, 2011 @ 9:47 am

  2. Thanks John, that means a lot.
    Let me know how that restore strategy of yours works out. :-)

    Comment by Michael J. Swart — August 23, 2011 @ 2:12 pm

  3. [...] ACID Properties By Example (And Counterexample) Part Four: Durable - Here’s the finale to Michael J. Swart’s (Blog|Twitter) brilliant blog post series. Good form sir. [...]

    Pingback by Something for the Weekend – SQL Server Links 26/08/11 — August 26, 2011 @ 9:37 am

  4. well done, very informative and easily understandable series.

    I am preparing for 70-432. any help on that will be highly appreciated.

    Thanks John, its because of your blog I have been directed here.

    Thank you.

    Comment by Tahir — September 1, 2011 @ 9:43 am

  5. Hi Tahir,
    Thanks for coming by. I’m glad you liked it.
    I’ve only taken the 70-433 exam http://michaeljswart.com/2011/04/whew-microsoft-certification-exam-70-433/
    The difference between 432 and 433 is that 433 is db development and 432 is db administration.
    I don’t think I can help you with the 70-432 exam but I wish you the best of luck.

    Comment by Michael J. Swart — September 1, 2011 @ 11:01 am

  6. [...] Michael J. Swart dishes out ACID properties by example and also the counter example. [...]

    Pingback by Log Buffer #236, A Carnival of the Vanities for DBAs | The Pythian Blog — September 2, 2011 @ 8:44 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress