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

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:

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.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

mkalkbrenner’s picture

FileSize
3.63 KB

Sorry, I attached an incomplete version of my patch. Here's the right one.

mkalkbrenner’s picture

Component: database system » mysql database
dpearcefl’s picture

Status: Needs work » Postponed (maintainer needs more info)

Does this issue exist in current D6?

mkalkbrenner’s picture

Status: Postponed (maintainer needs more info) » Needs review

Yes, it existed in D5 and it still exists in D6.

Status: Needs review » Needs work

The last submitted patch, db_lock_tables.patch, failed testing.

mkalkbrenner’s picture

Status: Needs work » Needs review

BTW the most important patch is this one: #298768: Ensure that entries are written to watchdog table

dpearcefl’s picture

This issue will not go anywhere without confirmation that this issue still exists in Drupal 6.22 (current version) and without a patch against 6.22.

dpearcefl’s picture

Status: Needs review » Needs work

"Needs review" is when a valid patch is attached to the issue. The last patch (#1) failed.

Status: Needs work » Closed (outdated)

Automatically closed because Drupal 6 is no longer supported. If the issue verifiably applies to later versions, please reopen with details and update the version.