SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict Raioo's Avatar
    Join Date
    Jul 2000
    Location
    Boston
    Posts
    212
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow MySQL ----> lock issues :(

    Hello,

    I have a script that views the details of a meeting: subject, agenda (as text type).. and minutes (again as text type). Right next to the header: Agenda, I have two links (edit | remove). If you click on edit, the page refreshes and you get a form text area with the previous text already in for you to make your updates.

    Problem is, of course, if two people are clicking on edit at the same time, and if I am not issuing a LOCK on that table when the pages refreshes, then after the second person submits changes, data in the databse will not be actual.

    One solution, is to lock the table as the page refreshes meaning, the first person is in control of the Agenda text, while the 2nd person will get a database query error and will have to wait until the 1st person submits changes.

    Problem : what if the first person takes a long time to update the Agenda. And what if he just leaves the building...

    Pleas ehelp with a suggestion on how to tackle this lock problem. (I also noticed MySQL is not supporting locking ONE table only!!, it would lock the whole database for me...)

    Thanks a bunch!
    Last edited by Raioo; Apr 15, 2002 at 20:14.
    I appreciate the help of everyone at SitePoint.com

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You *can* do LOCK TABLE bob to lock bob.

    It might make sense to add a couple columns to the end of each row (or make another table):
    Code:
    CREATE TABLE bob
      rows ...
      lock_userid int null,
      lock_time   datetime null
    );
    Then whenever someone edits a particular row (I assume you have a single row that you edit at a time) simply do this:
    PHP Code:
    <?php
      
    if( $rowid ) {
        
    mysql_query"SELECT lock_userid,
                             lock_time
                        FROM bob
                       WHERE rowid = 
    $rowid);

        if( 
    $rowlock_userid ] == $userid
         
    or $rowlock_time   ] > now some_time ) {
          
    // if the current user has the row locked
          // or the row has been locked for a certain amount of time
          // go ahead and let the new user take it
          
    mysql_query"UPDATE bob
                           SET lock_userid = 
    $userid,
                               lock_time   = now( ) 
                         WHERE rowid = 
    $rowid);
          echo 
    "You can edit the row now";
          
    mysql_query"SELECT *
                          FROM bob
                         WHERE rowid = 
    $rowid);
        } else {
          echo 
    "Sorry, someone else has the row locked.";
        } 
    // end if
      
    // end if
    ?>


Bookmarks

Posting Permissions

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