I had a problem with table locking, solved in this question:
Table 'watchdog' was not locked with LOCK TABLES on custom module
I want to share my current code, because I'm still not sure if I am doing it correct.
I have two tables:
- eventi_list, for storing users reservation for selected event if there are free places of this event
eventi_wait, for storing users reservation if there are no free places for selected event. If any user withdraw her/his reservation, the oldest reservation for current event (if exists) from this table move to eventi_list table.
function eventi_book($nid) { $nid = (int) $nid; if (_eventi_if_node($nid)) { global $user; $uid = (int) $user->uid; $is_booked = _eventi_is_booked($uid, $nid); if (! $is_booked) { $is_booked = _eventi_is_booked($uid, $nid, 'eventi_waits'); } if (! $is_booked) { db_query('LOCK TABLES content_type_event WRITE, eventi_list WRITE, eventi_waits WRITE, watchdog WRITE'); $amount = _eventi_places_amount($nid); if ($amount > 0) { _eventi_add($uid, $nid); db_query("UPDATE {content_type_event} SET field_event_places_amount_value = field_event_places_amount_value - 1 WHERE nid=$nid"); // cache_clear_all(); } else { _eventi_add($uid, $nid, 'eventi_waits'); } db_unlock_tables(); } } // _eventi_redirect_to_prev(); } function _eventi_if_node($nid) { $query = db_query("SELECT COUNT(nid) as count FROM {node} WHERE nid=$nid AND type='event'"); $result = db_fetch_array($query); return $result['count'] > 0; } function _eventi_is_booked($uid, $nid, $tbl_name = 'eventi_list') { $tbl_name = '{'.$tbl_name.'}'; $query = db_query("SELECT count(nid) AS count FROM $tbl_name WHERE nid=$nid AND uid=$uid"); $result = db_fetch_array($query); return $result['count'] > 0; } function _eventi_places_amount($nid) { $query = db_query("SELECT field_event_places_amount_value AS amount FROM {content_type_event} WHERE nid=$nid"); $result = db_fetch_array($query); return (int) $result['amount']; }
function _eventi_add($uid, $nid, $tbl_name = 'eventi_list') { $tbl_name = '{'.$tbl_name.'}'; $t = time(); return db_query("INSERT INTO $tbl_name (uid, nid, created_at) VALUES($uid, $nid, $t)"); }
I want to make sure that I never end up with negative number of free places of selected event. So I have to atomic access to places amount:
$amount = _eventi_places_amount($nid);
and then I can update places amount, when I am sure that $amount is greater than 0:
db_query("UPDATE {content_type_event} SET field_event_places_amount_value = field_event_places_amount_value - 1 WHERE nid=$nid");
I don't like this solution, because I must to lock many tables. Is there a better way of doing this in Drupal?