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!