What is the benefit of using "begin_transaction" method in MySQLi?

Question

I'm looking into rollback management with MySQLi, and I'm curious what is the benefit of begin_transaction() method. Many examples I look at skip it entirely 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 explicitly declaring a transaction, but is there a value to begin_transaction() other than in readability? What real work does it do?

What do you mean by the "value" of that method? It starts a transaction... — Nico Haase, Feb 15 '18 at 17:13
Yes, but it doesn't seem to be necessary to start the work a transaction does. What value does it add to the code for me to use a begin_transaction instead of just turning autocommit off and running a few queries? Either way, it seems I have to check the return values of each query to determine the success of the whole transaction, so I don't know what extra functionality I get if I start the process with a begin_transaction versus just turning autocommit off and proceeding ... — Elron Steele, Feb 15 '18 at 17:24
Well if autocommit is turned on, I can start a transaction for batch queries and commit or rollback if necessary. But when I want to execute a single query I rely on the autocommit. But with autocommit off I will need to explicitly commit .I do think it is just a matter of preference and per case basis — frz3993, Feb 15 '18 at 17:51

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

As already mentioned in other answers, begin_transaction() starts a transaction, without affecting the value of autocommit. The queries are not commited into the database until you call commit() or trigger an implicit commit.

As described in MySQL docs:

A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with a COMMIT or ROLLBACK statement. See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.

If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.

This means that the main difference between begin_transaction() and autocommit(FALSE); is whether you want a one-time transaction or whether you want contiguous transactions.

A simple one-time transaction using begin_transaction() would look like this:

<?php

// Switch on error reporting with exception mode
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'username', 'password', 'db_name');
$mysqli->set_charset('utf8mb4');

try {
    // Start transaction
    $mysqli->begin_transaction();

    $mysqli->query('INSERT INTO some_table(col2) VALUE(4)');
    $mysqli->query('INSERT INTO some_table(col2) VALUE(4');

    // Commit changes
    $mysqli->commit();
} catch (\Throwable $e) {
    // Something went wrong. Rollback
    $mysqli->rollback();
    throw $e;
}

Such approach is clearer than switching the autocommit mode off altogether. .

score 0 · Answer 2 · answered Feb 15 '18 at 21:30

0

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.

answered Feb 15 '18 at 21:30

Kazz

1,030
8
16

David Tran · Answer 3 · 2018-09-09T09:43:26.070

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.

What is the benefit of using "begin_transaction" method in MySQLi?

3 Answers3

Linked