2

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?

3

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.

3

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()

  • 1
    To add more, every module function should have a name prefixed by the module short-name; a function named _is_booked() is the perfect example of how functions should not be named. – kiamlaluno Apr 27 '11 at 14:22
  • Thanks. '_' prefix is for module 'private' functions? I cast to int those variables so I don't see need of using placeholders. – user1077 Apr 27 '11 at 15:07
  • 1
    No. '_yourmodule' is for module private functions. So you need to name that function '_yourmodule_is_booked'. – Berdir Apr 27 '11 at 15:22
  • @user1077 The value returned from the function is not the reason for which module functions should be prefixed with the module name (with, or without the initial underscore). The only functions that don't use a module prefix are the functions present in Drupal include files (see the functions contained in common.inc, for example), which are not part of any modules. Module functions need to be prefixed with the module name for compatibility with Drupal and third-party modules. – kiamlaluno Apr 28 '11 at 2:18
2

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');
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.