LOCK TABLES in MyISAM is NOT a poor man’s tranactions substitute

I get to hear that a lot: that LOCK TABLES with MyISAM is some sort of replacement for transactions; some model we can work with which gives us ‘transactional flavor’.

It isn’t, and here’s why.

When we speak of a transactional database/engine, we check out its ACID compliance. Let’s break out the ACID and see what LOCK TABLES provides us with:

  • A: Atomicity. MyISAM does not provide atomicity.  If we have LOCK TABLES followed by two statements, then closed by UNLOCK TABLES, then it follows that a crash between the two statements will have the first one applied, the second one not not applied. No mechanism ensures an “all or nothing” behavior.
  • C: Consistency. An error in a statement would roll back the entire transaction in a transactional database. This won’t work on MyISAM: every statement is “committed” immediately.
  • I: Isolation. Without LCOK TABLES, working with MyISAM resembles using the read uncommitted, or dirty read isolation level. With LOCK TABLES – it depends. If you were to use LOCK TABLES … WRITE on all tables in all statements, you would get the serializable isolation level. Actually it would be more than serializable. It would be truely serial.
  • D: Durability. Did the INSERT succeed? And did the power went down just after? MyISAM provides not guarantees that the data will be there.

So of all ACID properties, the only thing we could get is a serializable isolation level, and that, too, only if we used LOCK TABLES … WRITE  practically everywhere.

Where does the notion come from, then?

There’s one thing which LOCK TABLES does help us with: race conditions. It effectively creates a mutex block. The same effect could be achieved when using GET_LOCK() and RELEASE_LOCK(). Perhaps this is the source of confusion.

3 thoughts on “LOCK TABLES in MyISAM is NOT a poor man’s tranactions substitute

  1. Good post, all true.

    few comments:

    “This won’t work on MyISAM: every statement is “committed” immediately.”

    Actually, its worse than that. MyISAM is very much row based, a single statment can succeed partly, operating on some rows but not all implied by the statement.

    “C: Consistency. An error in a statement would roll back the entire transaction in a transactional database.”

    Not true. Typically, a runtime error results in automatic rollback of the offending statement, leaving a pending transaction. But I think this behaviour is implementation dependent. (as in choice to rollback entire trans or just the statement)

    regards,

    Roland

  2. Roland,

    “a single statement can succeed partly” – absolutely right. Should have mentioned that while at it.

    Consistency: that’s very interesting. I would also suspect the case is implementation dependent. But perhaps it should be stated more clearly, then, what ACID compliance means.

    Reagards

  3. Hi!

    “But perhaps it should be stated more clearly, then, what ACID compliance means.”

    Well, I think there is not really a formal compliance. It’s more like, ACID is just a convenient acronym to refer to a number of properties that are a pre-requisite for implementing transactions. Standards like SQL take these concepts, develop specifications that could be implemented. If you have an implementation, you can then determine wether it complies to the specification. (which is slightly different than “complying” to the original concept)

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.