SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Addict Raioo's Avatar
    Join Date
    Jul 2000
    0 Post(s)
    0 Thread(s)

    Arrow MySQL ----> lock issues :(


    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 19:14.
    I appreciate the help of everyone at

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    buried in the database shell (Washington, DC)
    0 Post(s)
    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):
      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:
    if( $rowid ) {
    mysql_query"SELECT lock_userid,
                        FROM bob
                       WHERE rowid = 

    $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 = 
                               lock_time   = now( ) 
                         WHERE rowid = 
    "You can edit the row now";
    mysql_query"SELECT *
                          FROM bob
                         WHERE rowid = 
        } else {
    "Sorry, someone else has the row locked.";
    // end if
    // end if


Posting Permissions

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