The Death of the Database Transaction

What is this – how you say – database transaction?

Remember back in college when we were taught that all “real” databases had transactions and foreign keys.

Remember when all those Postgress fanboys would make fun of anyone using MySQL because it was inferior since it lacked foreign keys?

Turns out they were all wrong.

Running without databases transactions can even be really fun. It’s a bit like driving 120 Mph down the highway on a motorcycle without a helmet. Just don’t crash and you’ll be fine! This is how I stopped worrying and learned to love the auto commit.

With the recent Scaling eBay PDF I think we can put the final nail in the coffin.

We’re now seeing more and more real world examples of large production databases that run without transactions and use application level partitioning. Flickr, LiveJournal, eBay, and Google being all great examples.

(Note. I’m assuming Flickr, Google and LiveJournal aren’t using transactions or FKs since they use partitioning. Google does in fact have a distributed lock coordinator but they admit that they try to use it sparingly)


  1. Ho there!

    “Running without databases transactions can even be really fun. It’s a bit like driving 120 Mph down the highway on a motorcycle without a helmet. Just don’t crash and you’ll be fine! This is how I stopped worrying and learned to love the auto commit.”

    “Autocommit on” has nothing to do with not using transactions! Quite the contrary.

    Non-transactional engines in MySQL don’t care a bit wheter autocommit is on or off. They simply don’t know or care about it.

    Suppose we’re doing an insert statement on a table backed by a non-transactional engine like MyISAM (or Archive etc). A problem might occurr while executing a single statement that inserts many rows. Suppose this happens when only half of the rows are processed: the statement fails halfway…not doing the rest. This is completely independant of the autocommit setting.

    On the other hand, tables backed by a transactional engine (like NDB/Cluster, InnoDB and also SolidDB and PBXT) will listen to autocommit (and to COMMIT and ROLLBACK when it’s off).

    Having autocommit on will COMMIT each *statement* directly after execution. It will *not* commit after each row processed within the statement.

    This is a major difference with non-transactional tables, because there processing is on a row by row basis (not statement). So, setting autocommit to on will just prevent you from doing multiple statements within one transaction. However, the individual statements are still executed transactionally. They are just committed immediately after completion.

    Regardless of the autocommit setting, a statement on a table backed by a transactional engine will either succeed, or fail as a whole. So even if your autocommit is on, it’s still transactional, because multiple rows handled by the statement are processed as a whole.

    When you do use transactional engines, it’s advisable to have autocommit off as much as possible. Especially when the transactions are not too large (measured by the amount of data processed – not the number of statements or rows) having autocommit off provides considerable gains in performance, because each commit requires that the data be made persistent (which costs IO).

    Of course, when you have large transactions, it’s better to have autocommit on (or COMMIT very regularly) because the buffers/logs used to temporarily store the information until COMMIT might not fit into memory, slowing things down considerably.

    kind regards,

    Roland Bouman

  2. >Turns out they were all wrong.

    Not at all. First of all, transactions for saving data make sense when you have multiple rows to save. Transactions for fetching data always make sense to avoid reading uncommitted data.

    Autocommit might mean you save your data on a per row basis, but doesn’t mean you don’t use transactions when reading.

    Using no FKs means you either don’t give a shit about invalid data, or you don’t know any better.

    How does this say “they were wrong”? These examples show that they were right all along.

    Martijn






%d bloggers like this: