In my PHP code I'm trying to make an innoDB database transaction be ignored if another thread is already performing the transaction on the row. Here's some code as an example:
$db = connect_db();
try{
$db->beginTransaction();
$query = "SELECT val FROM numbers WHERE id=1 FOR UPDATE"; //I want this to throw an exception if row is already selected for update
make_query($db,$query); //function I use to make queries with PDO
sleep(5); //Added to delay the transaction
$num = rand(1,100);
$query = "UPDATE numbers SET val='$num' WHERE id=1";
make_query($db,$query);
$db->commit();
echo $num;
}
catch (PDOException $e){
echo $e;
}
When it makes the SELECT val FROM numbers WHERE id=1 FOR UPDATE
query I need some way of knowing through php if the thread is waiting for another thread to finish it's transaction and release the lock. What ends up happening is the first thread finishes the transaction and the second thread overwrites its changes immediately afterwards. Instead I want the first transaction to finish and the second transaction to rollback or commit without making any changes.