Record deleted during a transaction

Question

I have a system that handles many queries per second. I code my system with mysql and PHP.

My problem is mysqli transaction still commit the transaction even the record is deleted by other user at the same time , all my table are using InnoDB.

This is how I code my transaction with mysqli:

mysqli_autocommit($dbc,FALSE);
$all_query_ok=true;

$q="INSERT INTO Transaction() VALUES()";
mysqli_query ($dbc,$q)?null:$all_query_ok=false;

$q="INSERT INTO Statement() VALUES()";
mysqli_query ($dbc,$q)?null:$all_query_ok=false;

if($all_query_ok==true){
    //all success
    mysqli_commit($dbc);
}else{
    //one of it failed , rollback everything.
    mysqli_rollback($dbc);
}

Below are the query performed at the same time in other script by another user and then end up messing the expected system behaviour,

$q="DELETE FROM Transaction...";
mysqli_query ($dbc,$q)?null:$all_query_ok=false;

Please advice , did I implement the transaction wrongly? I have read about row-level locking and believe that innoDB does lock the record during a transaction

Answer 1

I did some research on row level locking which can lock record from delete or update

FOR UPDATE

Official Documentation

Right after the begin transaction I have to select those record I wanted to lock like below

SELECT * FROM Transaction WHERE id=1 FOR UPDATE

So that the record will be lock until transaction end.

This method doesn't work on MyISAM type table