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

You may use [`mysqli::begin_transaction`](http://php.net/manual/de/mysqli.begin-transaction.php) — TiMESPLiNTER, Sep 25 '14 at 08:46
@TiMESPLiNTER do it have any difference from mysqli_autocommit()? — Leon Armstrong, Sep 25 '14 at 08:48
`mysqli_autocommit()` just defines if the queries you execute are commited immediately after executing them. So you have to turn that off (as you did it already in your code above) to use transactions. — TiMESPLiNTER, Sep 25 '14 at 08:52
do not down vote in silent , drop down a comment let me know how to improve the question — Leon Armstrong, Oct 24 '14 at 06:11

TiMESPLiNTER · Answer 1 · 2014-09-25T10:51:06.280

I don't know which kind of transactions you're talking about but with the mysqli extension I use the following methods to work with transactions:

Then the process is like:

Starting a new transaction with mysqli::begin_transaction
Execute your SQL queries
On success use mysqli::commit to confirm changes done by your queries in step 2 OR on error during execution of your queries in step 2 use mysqli::rollback to revert changes done by them.

You can think of transactions like a temporary cache for your queries. It's someway similar to output caching in PHP with ob_* functions. As long as you didn't have flushed the cached data, nothing happens on screen. Same with transactions: as long as you didn't have commited anything (and autocommit is turned off) nothing happens in the database.

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 24 '14 at 06:15

Leon Armstrong

1,285
3
16
41

score 0 · Answer 3 · answered Oct 26 '14 at 22:58

Looks like a typical example of race condition. You execute two concurrent scripts modifying data in parallel. Probably your first script successfully inserts records and commits the transaction, and the second script successfully deletes records afterwards. I'm not sure what you mean by "the query performed at the same time in other script by other user" though.

score 0 · Answer 4 · answered Oct 29 '14 at 16:47

You will have to do this this way:

mysqli_autocommit($dbc,FALSE);

$dbc->begin_transaction();

$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);
}

you can use the object oriented or the procedural style when calling begin_transaction (I prefer the object oriented).

Record deleted during a transaction

4 Answers4