Brent’s Blog

Have you ever had the power shut down on your database server while it was running and prayed that the database wasn’t corrupt? If so, you’ve probably worked on Database Management Systems (DBMS) that did not implement ACID, specifically atomicity and durability. We can cover the other concepts in another post, or you can read about them here.

Before we dive into the database, let’s talk about where the database actually lives. Your filesystem, whether it is NTFS (Windows) or ext2 or ext3 or ReiserFS, is a series of sectors on the disk. When you update a row, table, or combination of the prior, the result will be a series of writes to one or many disk sectors.

Disk sectors are small. They could be 512 bytes or 4096 bytes or some other number. During normal operation, your file system can most likely guarantee that a single sector is written to disk or isn’t. That is, the write of a single sector is atomic during normal operation. However, during a power failure, you can’t guarantee that the single sector was written or wasn’t because you can’t be sure if the underlying drive does atomic writes. Journaling filesystems can protect against this by saving extra information about the sector writes elsewhere to guarantee that each individual sector either makes it or doesn’t make it to disk. File system journaling strategies are a topic for another post as well :).

Given the uncertainty of the underlying filesystem and the incosistencies among different filesystems’ journaling strategies, it is best for the DBMS to not assume anything about the underlying filesystem or its ability to atomically commit a series of sectors to disk. Instead, the DBMS maintains its own journal that is specifically designed to maintain the atomicity and durability of a series of database tables (as opposed to the filesystem’s journal of sector writes).

Database transactions are the data element that is stored in the database’s journal. Each transaction could be a single record insert/update/delete or a series of them. When you use the BEGIN TRANSACTION and COMMIT or ROLLBACK keywords, you are creating an entry in the database journal that will guarantee that your transaction is either fully-in-effect (or committed) or not in effect at all (ROLLBACK). The beauty of the DBMS is that even if you send a COMMIT to the database for a transaction and the database server crashes, the transaction will still be either fully-committed or fully-ignored, based on the state of the DBMS’s journal. For you, as a programmer, that means that your program must put every dependent operation into a single transaction because any one transaction in a series of transactions could fail or succeed.

So how does the database journal database guarntee consistency? Each DBMS is different but similar. I will describe one approach. In this approach, the journal contains a list of instructions about which rows to change in which tables in which order. Often times, the journal will contain a list of reverse instructions, which are to be executed before the forward instructions in the instance of a drive failure, in order to go back to where the database was before the transaction began. Let’s use an example.

I want to drive from Minneapolis, MN to Dallas, TX. Doing so will require going through Des Moines, IA and Kansas City, MO. My transaction will only succeed if I get all the way from Minneapolis to Dallas, but I need to pass through the intermediate cities on the way.

My journal looks like this:

1. Drive from Minneapolis to Des Moines
2. Drive from Des Moines to Kansas City
3. Drive from Kansas City to Dallas

My reverse journal looks like this:

1. Drive from Dallas to Kansas City
2. Drive from Kansas City to Des Moines
3. Drive from Des Moinces to Minneapolis

If we get in trouble somewhere along the way, all we have to do is: execute the reverse journal to get back to a consistent state before the transaction (Minneapolis) and then execute the forward journal to commit our new transaction. This will guarantee that we are either in Minneapolis or Dallas.
Post questions, and I’ll write a follow-up.

Post comments here. I don’t want to answer them in two places.

Now that I’m a more experienced bike rider, I decided to upgrade a couple components for this season. At first I thought, “I’ll just get clipped shoes”, but then I went on a spree and yeah…. I ended up getting new pedals with clips, a new seat (my old one was actually broken), and a new seat post.

The clips (below) are cool because they allow me get an extra 50% torque on my pedaling by being attached to the pedals and pulling up when you are resetting for the next push. Instead of mashing the pedals, you do a circular motion with your feet because you are always attached, and it’s very even. The only caveat is that you MUST CLIP OUT when you stop, or you will fall on your side and look like an idiot.

It’s even worse when you go over your handlebars with clips on. I saw a guy catch his bar ends on a tree last year, go over his handlebars with clips on, and land on his face with his bike still attached to his feet. It was not pretty. Believe me, I will be keeping that in mind when I’m riding.

Fixing my broken seat was a no brainer, but I wanted to solve the sore ass problem at the same time. My bike has a front suspension but not a rear one, so wasn’t able to go over bumps sitting down, which required a lot of energy to do over and over on very technical courses. Plus, when you’re standing, your center of gravity is much higher, making you vulnerable to the endo. Along comes the flexible seatpost to save the day.

The flexible seatpost (seen below) actually flexes when you go over bumps to absorb the shock. Instead of being a pogo stick, which would amplify turbulence, it actually flexes horizontally, which is much easier on the old back.

To further protect myself, I actually picked up biking shorts, which have a padded ass, to protect from the wear of the seat on a long ride.

I took my new toys out for a 6 mile ride yesterday, which was nice, but now we got snow again. Stupid april in Minnesota!!!

I kind of went on a Itunes spree tonight. I had to pick up a few guitar hero songs from Itunes and ended up buying a bunch of music, kind of like when wikipedia consumes your entire day off. *sigh*. From Guitar Hero came Killer Queen, More than A Feeling, Rush’s YYZ, and Bark at the Moon. Great mix of music.

My transactions were almost complete when I remembered that I wanted With a little help from my friends from. That turned into several Joe Cocker songs, which turned into purchasing his entire anthology. In listening to the demos, I realized that his style has a lot in common with Neil Diamond, the 70s singer who my parents listened to while I was growing up in the 80s….the time when Monster Ballads were in full bloom, thanks to Neil’s lead.

OK. The gloves are off. Who’s the better singer: Neil Diamond or Joe Cocker? That’s right. I’m not an 80 year old woman, even though my 85 year old grandma had Neil Diamond tapes in her car. Damn you Itunes, you enabler.

Neil has some sort of weird 1970s appeal to him. You’d probably throw your soda at him if you saw him dancing on the street corner in a creepy silk shirt, but you’d promptly apologize when you heard his 3-packs-a-day vibrato. Plus, he actually did Saving Silverman, which either really makes fun of him or props him up as some sort of musical god. You decide. Neil has tons of hits which don’t even need mentioning because they’ve been on every radio station your parents listen to hundreds of thousands of times. They’re also on my Itunes list

Joe’s got a lot of emotion, and he doesn’t always sing about some chick. He sings about chicks sometimes, but not as much as Neil. Joe’s also very much into his music. Check out this video from woodstock if you doubt that:

[youtube QIKBq9TeFlw]

He looks really stoned, but I guess that’s just his stage presence.

Winner?

I’ll just let the videos speak for themselves.

[youtube UwQV_JJXulM]

[youtube rfP90uJ12eQ]

[youtube bvWQNa1czG4]

[youtube 1wptJ2fYm3A]

Unless you’re from a state that starts with M in the USA, college hockey probably doesn’t matter to you. Fortunately, I’m from one of those M states :).

This weekend were the divisional playoffs for the WCHA, the division the University of Minnesota Gophers play for. Since the tournament is usually dominated by Minnesota, St. Cloud State, UND, Wisconsin, and other regional schools, it’s held at our beautiful Excel Energy Center in St. Paul, MN….nothing like a 20000 person capacity hockey arena in the middle of largest city in the state of hockey.

After winning their first round game against the Wisconsin Badgers, the Gophers played their final match against the UND Fighting Sioux. Both teams  played extremely well, and the score was 2-2 at the end of regulation.

A couple minutes into the first overtime period, the Gophers broke out from a defensive play and sent Blake Wheeler a long pass down the side. He dove down to the ice to beat the icing call. Had the puck reached the red line before he touched it, it would have meant a face off in our zone. He touched it perfectly so that it went over the left shoulder of the UND goalie and into the far corner. We didn’t know this yet. We were just cheering because he had sprinted to beat the icing.

Suddenly, there was a loud roar (120 decibels), and the goalie judge’s light lit up as Blake Wheeler sprinted into the corner with his team right behind him. We looked at each other in disbelief that the entire tournament had been decided by what seemed like an impossible, yet spectacular goal. Check out the video.

[youtube EuepVP-NBrs]

Congrats, Gophers.