Leon Armstrong Leon Armstrong - 2 months ago 5
MySQL Question

Record deleted during a transaction

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

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