PHP +锁定MySQL表失败

Question

I have a table that needs to be locked from being inserted but it also needs to be able to be updated while inserts are prevented.

function myfunction() {
  $locked = mysql_result(mysql_query("SELECT locked FROM mylock"),0,0);
  if ( $locked ) return false;
  mysql_query("LOCK TABLES mylock WRITE");
  mysql_query("UPDATE mylock SET locked=1");
  mysql_query("UNLOCK TABLES");

  /* I'm checking another table to see if a record doesn't exist already */
  /* If it doesn't exist then I'm inserting that record */

  mysql_query("LOCK TABLES mylock WRITE");
  mysql_query("UPDATE mylock SET locked=0");
  mysql_query("UNLOCK TABLES");  
}

But this isn't enough, the function is called again from another script and simultaneously inserts are happening from the 2 calls to the function, and I can't have that because it's causing duplicate records.

This is urgent please help. I thought of using UNIQUE on the fields but there are 2 fields (player1, player2), and NEITHER cannot contain a duplicate of a player ID.

Unwanted behavior: Record A = ( Player1: 123 Player2: 456 ) Record B = ( Player1: 456 Player2: 123 )

Answer 1

I suggest not using locks at all. Instead, when insterting the data, do like this:

mysql_query("INSERT IGNORE INTO my_table VALUES(<some values here>)");
if(mysql_affected_rows()>0)
{
    // the data was inserted without error
    $last_id = mysql_insert_id();
    // add what you need here
}
else
{
    // the data could not be inserted (because it already exists in the table)
    // query the table to retrieve the data
    mysql_query("SELECT * FROM my_table WHERE <some_condition>");
    // add what you need here
}

When adding the IGNORE keyword to an INSERT statement, MySQL will attempt to insert the data. If it doesn't work because a record with the same primary key is already in the table, it will fail silently. mysql_affected_rows is used to know the number of inserted records and decide what to do.