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.
Comment | File | Size | Author |
---|---|---|---|
#1 | db_lock_tables.patch | 3.63 KB | mkalkbrenner |
db_lock_tables.patch | 3.6 KB | mkalkbrenner | |
Comments
Comment #1
mkalkbrennerSorry, I attached an incomplete version of my patch. Here's the right one.
Comment #2
mkalkbrennerComment #4
dpearcefl CreditAttribution: dpearcefl commentedDoes this issue exist in current D6?
Comment #5
mkalkbrennerYes, it existed in D5 and it still exists in D6.
Comment #7
mkalkbrennerBTW the most important patch is this one: #298768: Ensure that entries are written to watchdog table
Comment #8
dpearcefl CreditAttribution: dpearcefl commentedThis 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.
Comment #9
dpearcefl CreditAttribution: dpearcefl commented"Needs review" is when a valid patch is attached to the issue. The last patch (#1) failed.