Impossible to lock two MySQL tables

Posted by mkalkbrenner on August 22, 2008 at 3:46pm

Issue Summary

Via drupal's database API it's impossible to lock two or more MySQL tables, because db_lock_table() only accepts one table name.

<?php
db_lock_table('table_a');
db_lock_table('table_b');
db_query('SELECT * FROM {table_a}, {table_b}');
db_unlock_tables();
?>

The result will be a MySQL error message:

Table 'table_a' was not locked with LOCK TABLES

The reason for this could be found at the MySQL documentation at http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html:

LOCK TABLES releases any table locks currently held by the thread before acquiring new locks.

I assume that this also might be the reason for some other bug reports here talking about an error message "Table 'XYZ' was not locked with LOCK TABLES" if a special module or a combination of modules is installed.

I attached a patch that triggers a warning if someone implicitly releases a table lock by locking another table and introduces a new function called db_lock_tables() which locks multiple tables.

With this patch applied the example above will cause a warning which tells you the reason for the database error:

You implicitly unlocked some tables (table_a) by requesting new locks on these tables: table_b

And here is the code that now works:

<?php
db_lock_tables(array('table_a', 'table_b'));
db_query('SELECT * FROM {table_a}, {table_b}');
db_unlock_tables();
?>

The patch still needs some work because I don't know PostgreSQL well enough to implement a corresponding db_lock_tables() in database.pgsql.inc.

A special Problem might be the error message "Table 'watchdog' was not locked with LOCK TABLES". This could happen if a module requests a table lock and run into an error afterwards. Using MySQL, drupal is unable to store the error in this case because it could not access the watchdog table. In bug report #195812: db_error() doesn't work on default connection I already pointed out a possible solution for this: Open a second database connection for inserting messages into watchdog table.