MySQL: automatic rollback on transaction failure

Is there any way to set MySQL to rollback any transaction on first error/warning automatically?

Now开发者_开发问答 if everything goes well, it commits, but on failure it leaves transaction open and on another start of transaction it commits incomplete changes from failed transaction. So i need to rollback automatically those failed transactions..

(I'm executing queries from php, but i don't want to check in php for failure, as it would make more calls between mysql server and webserver.)

Thank you

I don't know of such feature, but I also don't see how checking for failure would mean more calls:

try:
     <my code>
except:
     transaction.rollback()
     raise
else:
     transaction.commit()

-- it's in Python/Django, but it should directly transpose to PHP - and it takes exactly the same amount of code to start new transaction, no matter if there is a problem (exception) or not.

Sorry, You are going to need to do this on your own. I am not a PHP person, but in SQL: If you create a transaction and do several MySQL operations within the transaction, if you rollback the transaction everything will be rolled back. NOTE: You need to be using a transactional storage engine and autocommit must be set to off.

If all you are concerned about is the transaction coordination traffic then you could create a stored procedure and simply call it.

The reason the database does not automatically commit or rollback is because it does not know what you are trying to do, committing some data and rolling back other data might be acceptable in an app.

the key point to to set autocommit to false.

<?php
$database= new mysqli("sever", "user", "key", "database");
$database->autocommit(FALSE);
$error=0;

//asumming we want to delete a users infomation from two table
$database->query("delete from `pay` where `user`=1 ")?NULL:$error=1;
$database->query("delete from `users` where `id`=1 ")?NULL:$error=1;

if($error=0){
    $database->commit();
}  else {
    $database->rollback();
}
$database->close();
?>