Simple Read/Modify/Write lock using a DateTime column

ModifyLocked is our lock column, defined as datetime, default Null.

  //----- TRY TO GET MODIFY LOCK ON THIS RECORDS ROW -----
  //Ensure the row exists (create new row if not previously stored)
  $stmt = $maindb->prepare("
    INSERT IGNORE INTO my_table
    (
      MyUniqueColumn,
      ModifyLocked
    ) VALUES (
      ?,
      NULL
    )");
  $stmt->bind_param("i", $MyUniqueColumn);
  $stmt->execute();

  //Try and lock the row
  $stmt = $maindb->prepare("
    UPDATE my_table SET
      ModifyLocked = DATE_ADD(now(), INTERVAL 90 SECOND)
    WHERE 
      MyUniqueColumn = ? AND
      ( (ModifyLocked IS NULL) OR (ModifyLocked < DATE_SUB(now(), INTERVAL 90 SECOND)) )
  ");
  $stmt->bind_param("i", $MyUniqueColumn);
  $stmt->execute();
  if ($stmt->affected_rows <= 0)
  {
    //---- ROW IS LOCKED FOR MODIFYING BY SOME OTHER PROCESS -----
    
    //Can't access it
    
  }
  else
  {
    //----- WE HAVE LOCKED THE ROW READY FOR US TO MODIFY -----
    $Locked = 'Y';


    //Change values as needed here


    //Write the row and remove the lock
    $stmt = $maindb->prepare("
      UPDATE my_table SET
        ModifyLocked = NULL
      WHERE 
        MyUniqueColumn = ?
    ");
    $stmt->bind_param("i", $MyUniqueColumn);
    $stmt->execute();
  }
Feel free to comment if you can add help to this page or point out issues and solutions you have found. I do not provide support on this site, if you need help with a problem head over to stack overflow.

Comments

Your email address will not be published.