Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    17

    Unanswered: Lock out concurrent users

    I have a table that can be updated/modified or have records removed. I wanted to make a php page so that when two people try to update a record, one will see a "lockout page" while the other (the one who got there first) will have his changes go through. Are there defined ways to handle this type of thing?

    I was thinking of having a timestamp field in the table, and when the update page appears, it stores the timestamp field. When a user submits the update form, it retrieves the timestamp field again and compares it with the stored timestamp field... Would this be sufficient?

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    I'll assume you are talking about mysql, this link might help - http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    A mix of checking the timestamp AND a lock shoudl do what you want.

    how you handle your lock conflicts is up to you. you could examine the changes the later record wants to make and see if they are compatible and update accordingly or just chuck out the later users chnages. as ever its up to you

    you should probabluy only attempt to secure the lock when you plan to do your update.
    ie
    - secure a lock
    - read the current timestamp
    - compare to previous timestamp
    - if same ie ok update the row
    - if not do error handling
    - in any event release the lock

    it might be appropriate to encapsualte the whole thing as a stored procedure within MySQL, assuming iof course you are using the more recent V5 MySQl which supports stroed procedures

    it might be appropriate to use the innoDB engine and use a row level locking scheme rather than a table level lock
    http://dev.mysql.com/doc/refman/5.0/...ock-modes.html
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2006
    Posts
    17
    Thank you, this helps a lot.

    Unfortunately, the server we have is running MySQL 4.1.11. All the tables I have are of MyISAM type. I don't know much about MySQL LOCK (or MySQL in general - still a beginner!).. Will changing to InnoDB affect any of my other queries / tables? Also, would this mean that I have to use transactions in my queries?

    I was looking at GET_LOCK() and wondering if this would be another possible option to use.

    ---

    So, from what I understand so far, this is how part of the page might look like:

    update.php
    Code:
    function process_form($form_values) {
      if(secure_lock($dbhandle) == TRUE) {
        if($compare_time($values['last_modified']), $values['row_id'],$dbhandle) {
             // update record
             // update last_modified field
        } else {
             echo "This record has been modified. Please refresh the page.";
        }
      } else {
        echo "Someone is updating the record";
        // ???
      }
    }
    
    function compare_time($input, $row_id, $dbhandle) {
      $q = "SELECT last_modified FROM tbl WHERE row_id = $row_id"; 
      $r = mysql_query($q, $dbhandle) or die("error");
      $row = mysql_fetch_assoc($r);
      return (strtotime($input) < strtotime($row['last_modified']) ? TRUE : FALSE;
    }
    
    function secure_lock($dbhandle) {
      // Secure lock code
    }
    I'm still not sure how the LOCKs would work out.. Do they return a True/False value (or something that PHP can check) on whether its been secured?
    Last edited by kovi_rago; 08-03-06 at 14:24.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you attempt to read a table or row with a lock on it then MySQL will throw an error, the precise number is up to you, but could be examined using mysql_ErrNo

    You don't need to use transactions, as you are only updating the table once, but you do need to lock out other users once you are about to do your update

    A table lock will adversley affect concurrent usage, after all no user will be able to access the table untill the lock is removed

    I suspect that if you are concerned about concurrency issues then either MySQL isn't the db for you, or you will have to switch to InnoDB. If you do decide to make that switch I'd stronlgy recomend that at the same time you change to V5 MySQl so that you can take advantage of stored procedures which will probablky be a huge beneift to you, especially in dealing with lock out issues
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •