Table locking again, am I doing it correct?

Question

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:

1. eventi_list, for storing users reservation for selected event if there are free places of this event

2. 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?

Answer 1

MyISAM performs a table-level lock to do any SQL on a MyISAM Table.

If you convert everything to InnoDB, row-level locking is implicitly done for you. Multiversioning Concurrency Control (MVCC) allows each user to lock only row(s) needed.

Additionally, you may want to perform all atomic writes in a BEGIN...COMMIT transaction to group together several table updates as one unit. That's what InnoDB will do for you. InnoDB is ACID complaint. The A stands for ATOMICITY, a transaction's ability to be atomic.