Table 'watchdog' was not locked with LOCK TABLES on custom module

Question

I am creating module that need to lock table:

db_query('LOCK TABLES {eventi_list} READ');

$is_booked = _is_booked($uid, $nid);
if (! $is_booked) {
  $is_booked = _is_booked($uid, $nid, 'eventi_waiting_list');
}

if (! $is_booked) {
  $amount = _get_free_amount($nid);
  if ($amount > 0) {
    $insert = db_query("INSERT INTO {eventi_list} (uid, nid) VALUES($uid, $nid)");
  }
  else {
    $time = time();
    $insert = db_query("INSERT INTO {eventi_waiting_list} (uid, nid, created_at) VALUES($uid, $nid, $time)");
  }
  $update = db_query("UPDATE {content_type_event} SET field_event_places_amount_value = field_event_places_amount_value - 1 WHERE nid=$nid");
}
db_query('UNLOCK TABLES');

I am getting error (4 similar warnings):

Warning: Table 'watchdog' was not locked with LOCK TABLES query:
INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (19, 'php', '%message in %file on line %line.', 'a:4:{s:6:"%error";s:12:"user warning";s:8:"%message";s:139:"Table eventi_waits was not locked with LOCK TABLES query: SELECT count(nid) AS count FROM eventi_waits WHERE nid=201 AND uid=19";s:5:"%file";s:95:"D:\tools\xampp\htdocs\drupal\sites\bartenders.localhost.com\modules\\custom\eventi\eventi.module";s:5:"%line";i:115;}', 3, '', 'http://bartenders.localhost.com/bartender/book/201/%3Fdestination%3D/eventi/justin-knows', 'http://bartenders.localhost.com/eventi/justin-knows', '127.0.0.1', 1303900341) in D:\tools\xampp\htdocs\drupal\includes\database.mysqli.inc on line 135

What can I do with this, turn off database reporting?

Answer 1

This won't work with the current code. It is not the reporting that is the problem, but the implementation of locking.

From mysql lock tables reference

A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables.

You are trying to access unlocked tables while holding a lock.

I can't say that I have used it in the wild but there is a Drupal specific locking mechanism that you may want to look at instead.

Answer 2

Additionally to what Jeremy French said:

• If you really want to do a lock, you should use the provided api functions db_lock_table()

• You really need to pass arguments to your queries properly with placeholders like %d and %s. What you are doing is insecure and can lead to SQL Injection. Re-read the documentation of db_query()

Answer 3

The code is using the wrong lock. This is the description reported by the MySQL 5.0 reference manual:

READ [LOCAL] lock:

The session that holds the lock can read the table (but not write it).

Multiple sessions can acquire a READ lock for the table at the same time.

Other sessions can read the table without explicitly acquiring a READ lock.

The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 7.7.3, “Concurrent Inserts”.) However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ as of MySQL 5.0.13. (Before that, READ LOCAL essentially does nothing: It does not lock the table at all, so for InnoDB tables, the use of READ LOCAL is deprecated because a plain consistent-read SELECT does the same thing, and no locks are needed.)

The code is getting a read lock, but then it is writing in the table.

To notice that the main error is caused by the "event_list" not being write-locked, and from the fact watchdog() is trying to log the error writing to a table that was not write-locked too. As Jeremy French reported, the code should acquire all the locks it needs.

If you look at the code of db_lock_table(), which is the function that Bedir correctly suggests to use, you will notice that it is acquiring a write lock.

function db_lock_table($table) {
  db_query('LOCK TABLES {' . db_escape_table($table) . '} WRITE');
}