I'm looking into rollback management with MySQLi, and I'm curious what the value of "begin_transaction" method is. Many examples I look at skip it entirley by turning autocommit off, then executing some queries with success value returned, and testing a compound Boolean based on the return values to commit or rollback the multiple statements.
It doesn't seem like the "begin_transaction" method actually does any useful work in a scenario where we are looking to commit or rollback a group of queries based on the success of all of them. I can see that it adds readability to the code perhaps by explicitely declaring a transaction, but is there a value to "begin_transaction" other than in readability? What real work does it do?
Only thing that begin_transaction
does in contrast with autocommit off
is that it does not mess up with autocommit
value, so after you commit/rollback
transaction stared with begin_transaction
the autocommit
will be the same as it was before.
I always get tripped up using mysqli transactions in PHP because many examples that cover this topic throw in the method of turning off autocommit. While that works, it's not the same as using mysqli transactions. At least, not in the traditional sense where one would normally key in 'START TRANSACTION' in the query. Here is an example of using a mysqli transaction in PHP without messing with the autocommit setting. If anything, this is a reminder for myself if I ever forget how to do this.
$dbo->begin_transaction();
//Below are the 2 sample queries we need to run; each one will insert a row into a separate table
$result1 = $dbo->query($query1);
$result2 = $dbo->query($query2);
//If both queries were successful, commit
if ($result1 && $result2)
{
$dbo->commit();
}
//Else, rollback and throw error
else
{
$dbo->rollback();
echo 'Writing to DB failed.';
}
As others mention, it is somewhat influenced by your preference. With begin_transaction, you don't have to deal with toggling autocommit before and after your queries.