ACID: Keeping your database safe
Sunday, April 8th, 2007Have 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:
- Drive from Minneapolis to Des Moines
- Drive from Des Moines to Kansas City
- Drive from Kansas City to Dallas
My reverse journal looks like this:
- Drive from Dallas to Kansas City
- Drive from Kansas City to Des Moines
- 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.