3

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?

3

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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