JonBlog
Thoughts on website ideas, PHP and other tech topics, plus going car-free
Gotcha with MySQL table locking
Categories: Technical

Some code I’m working on requires that a table is locked against any reads or writes for a short duration, and I’ve found that, with MySQL 5.1.x, any other tables that are accessed within the same lock period need also to be locked. So, I was doing something like this:

LOCK TABLES test_model_test_organiser_versionable WRITE;
LOCK TABLES test_model_test_organiser WRITE;
SELECT
    MAX(version) AS max
    FROM test_model_test_organiser_versionable
    WHERE test_model_test_organiser_versionable.ID=:p1
    AND test_model_test_organiser_versionable.CREATOR_NODE_ID=:p2
    LIMIT 1;
UNLOCK TABLES;

This fails on the select with ‘General error: 1100 Table ‘test_model_test_organiser_versionable’ was not locked with LOCK TABLES’. Took me a while to work it out, and in fact the answer was in the MySQL manual page that I had open all the while. Here’s the gotcha: “A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement” (reference).

It seems that one lock statement overwrites the other – so the solution is to do this:

LOCK TABLES test_model_test_organiser_versionable WRITE,
    test_model_test_organiser WRITE;
SELECT
     MAX(version) AS max
     FROM test_model_test_organiser_versionable
     WHERE test_model_test_organiser_versionable.ID=:p1
     AND test_model_test_organiser_versionable.CREATOR_NODE_ID=:p2
     LIMIT 1;
UNLOCK TABLES;

Bah… none of this is required for PostgreSQL!

1 Comment to “Gotcha with MySQL table locking”

  1. Stuart says:

    Running lock tables runs an implicit commit. This means that any other lock tables you are in the “midst” of are comitted and no longer locked. It ALSO means that any other transactions you are in the midst of are also committed! So beware.

Leave a Reply