PHP + MySQL transactions examples

Question

I really haven't found normal example of PHP file where MySQL transactions are being used. Can you show me simple example of that?

And one more question. I've already done a lot of programming and didn't use transactions. Can I put a PHP function or something in header.php that if one mysql_query fails, then the others fail too?

---

I think I have figured it out, is it right?:

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

$a1 = mysql_query("INSERT INTO rarara (l_id) VALUES('1')");
$a2 = mysql_query("INSERT INTO rarara (l_id) VALUES('2')");

if ($a1 and $a2) {
    mysql_query("COMMIT");
} else {        
    mysql_query("ROLLBACK");
}

Hippomenes

You can use mysql_query("BEGIN"); instead of sequence mysql_query("SET AUTOCOMMIT=0"); mysql_query("START TRANSACTION"); – Stricklan

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. – Illuminance

@Stricklan it didn't work for me. You need to call SET AUTOCOMMIT=0 for BEGIN too. – Zecchino

Does "mysql_query("SET AUTOCOMMIT=0");" set all connections to wait for commit function or it's just for its related connection? – Burks

@Neal, Actually mysql wun die despite being deprecated, it will be available in PECL forever. – Hibbler

@Hibbler Things that get deprecated don't "die". They are held officially for legacy software but cease to be maintained and stricken from any recommended practices for new software. The fact remains, don't use mysql – Grandaunt

@Grandaunt PHP website says it will die. "It is not recommended to use the old mysql extension for new development, as it has been deprecated as of PHP 5.5.0 and will be removed in the future." (php.net/manual/en/mysqlinfo.api.choosing.php) – Steppe

@waste Fine, it will die. But that is not synonymous with being deprecated. Thus, my comment stands. – Grandaunt

Answer 1

The idea I generally use when working with transactions looks like this (semi-pseudo-code):

try {
    // First of all, let's begin a transaction
    $db->beginTransaction();
    
    // A set of queries; if one fails, an exception should be thrown
    $db->query('first query');
    $db->query('second query');
    $db->query('third query');
    
    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $db->commit();
} catch (\Throwable $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
    throw $e; // but the error must be handled anyway
}

Note that, with this idea, if a query fails, an Exception must be thrown:

• PDO can do that, depending on how you configure it
  • See PDO::setAttribute
  • and PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION
• else, with some other API, you might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you'll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commit or rollback) and you'll want those queries executed no matter what happened (or not) in the transaction.

Chee

Be careful if you are doing operations that may throw exceptions other than db ones. If so, an exception from a non-db statement may cause a rollback inadvertently (even if all the db calls are successful). Normally, you'd think rolling back is a good idea even if the error was not on the db side, but there are times 3rd party/non-critical code may cause not-so-important exceptions, and you still want to continue with the transaction. – Verminous

What is the $db type here? mysqli? – Presumption

@Presumption See my answer for an example that uses mysqli (similar in style to Pascal's approach). – Eous

it can easily be modified to catch PDOException and even check exception values if needed. us2.php.net/PDOException – Stump

what happens if you omit the $db->rollback(); and an exception is thrown? – Shuster

@Shuster all hell breaks loose... the queries executed before the exception are done. – Ahner

What about setting up the transaction code from within a stored procedure? Why is that frowned upon? – Malone

Can you just write it as a string and send it as one query? i.e. $MySQL_string = "START TRANSACTION; "; $MySQL_string .= "UPDATE MyGuests SET firstname = 'Dave', reg_date = NOW() WHERE id = 1 LIMIT 1; "; $MySQL_string .= "UPDATE MyGuests SET firstname = 'Sam', reg_date = NOW() WHERE id = 2 LIMIT 1; "; $MySQL_string .= "COMMIT; "; $DB->mysqli->query($MySQL_string) – Gospel

Also - is it ok to run your $db->query('first query'); etc in a for loop? – Gospel

$db is the PDO object (connection). Ref: php.net/manual/en/pdo.connections.php – Inlay

To expand on @HalilÖzgür 's comment above, if you use mysqli, you might want to catch mysqli_sql_exception instead of simply Throwable. Or use separate catch blocks in case you have non-DB exceptions you might want to handle. – Germin

Answer 2

I think I have figured it out, is it right?:

mysql_query("START TRANSACTION");

$a1 = mysql_query("INSERT INTO rarara (l_id) VALUES('1')");
$a2 = mysql_query("INSERT INTO rarara (l_id) VALUES('2')");

if ($a1 and $a2) {
    mysql_query("COMMIT");
} else {        
    mysql_query("ROLLBACK");
}

Hippomenes

Engine store should be InnoDB, not MyISAM! – Handpick

Answer 3

<?php

// trans.php
function begin(){
    mysql_query("BEGIN");
}

function commit(){
    mysql_query("COMMIT");
}

function rollback(){
    mysql_query("ROLLBACK");
}

mysql_connect("localhost","Dude1", "SuperSecret") or die(mysql_error());

mysql_select_db("bedrock") or die(mysql_error());

$query = "INSERT INTO employee (ssn,name,phone) values ('123-45-6789','Matt','1-800-555-1212')";

begin(); // transaction begins

$result = mysql_query($query);

if(!$result){
    rollback(); // transaction rolls back
    echo "transaction rolled back";
    exit;
}else{
    commit(); // transaction is committed
    echo "Database transaction was successful";
}

?>

Maisey

For a broad and high profile question like this, it would be great if the answers also reflected that. Your code sample is great, but can you elaborate more? Explain about transactions, why, when and where? Finally, link the code with your explanation. – Transfiguration

Welcome on StackOverflow. Please always write some describing text to your answer. – Parasitology

sorry im begginer, and my bad english, its very easy examle of code - for begginers - commit() rollback() begin() put in class DB (for example), $query - not once - maybe $query0 $query1 - then chek them - i use this code, this very easy to understand =) – Maisey

His comments make the example pretty clear. Good code shouldn't need describing text. Also the question asks for a simple example. I like this answer. – Marga

@GedzbergAlex for a single query there is no need of transaction, simply it confuses about the transaction, Is there a reason to use the transaction for a single query? – Abeyta

Answer 4

As this is the first result on google for "php mysql transaction", I thought I'd add an answer that explicitly demonstrates how to do this with mysqli (as the original author wanted examples). Here's a simplified example of transactions with PHP/mysqli:

Note: this is meant for InnoDB tables. Won't work with MyISAM tables.

// let's pretend that a user wants to create a new "group". we will do so
// while at the same time creating a "membership" for the group which
// consists solely of the user themselves (at first). accordingly, the group
// and membership records should be created together, or not at all.
// this sounds like a job for: TRANSACTIONS! (*cue music*)

$group_name = "The Thursday Thumpers";
$member_name = "EleventyOne";

// tell mysqli to throw exception in case of error
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// connect
$conn = new mysqli($db_host, $db_user, $db_passwd, $db_name);

try {

    $conn->begin_transaction();

    // assume that the TABLE groups has an auto_increment id field
    $query = "INSERT INTO groups (name) VALUES (?)";
    $conn->execute_query($query, [$group_name]);
    $group_id = $conn->insert_id; // last auto_inc id from *this* connection

    $query = "INSERT INTO group_membership (group_id,name) VALUES (?,?)";
    $conn->execute_query($query, [$group_id,$member_name]);

    // our SQL queries have been successful. commit them
    $conn->commit();
}
catch ( \Throwable $e ) {
    $conn->rollback(); 
    throw $e;
}

Eous

On a related note, I just discovered that if you're working with InnoDB tables, it IS possible to lock/unlock tables when using the autocomitt() approach to transactions, but it is NOT possible when using the begin_transaction() approach: MySQL documentation – Eous

+1 for detailed (and commented) example with actual mysqli code. Thanks for this. And your point about locking/transactions is very interesting indeed. – Brittain

Does "autocommit(FALSE)" will affect to another connection in same database/table ? I mean if we open two pages that one of them set its connection to "autocommit(FALSE)" but other one left the autocommit function, does it wait for commit function or not. I want to know if autocommit is an attribute for connections and not for database/table. Thanks – Burks

@Burks $conn->autocommit(FALSE), in the example above, is only affecting the individual connection - it has no effect on any other connections to the database. – Eous

NOTE: instead of if (!result), should do if (result === false), if the query is capable of returning a valid result that would evaluate to false or zero. – Timeless

@Eous It seems to me that this solution is insecure because it could be vulnerable to SQL injection. What would I need to do to this to use it with prepared statements? – Sibelius

Answer 5

Please check which storage engine you are using. If it is MyISAM, then Transaction('COMMIT','ROLLBACK') will not be supported because only the InnoDB storage engine, not MyISAM, supports transactions.

Scurry

Answer 6

When using PDO connection:

$pdo = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8', $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // this is important
]);

I often use the following code for transaction management:

function transaction(Closure $callback)
{
    global $pdo; // let's assume our PDO connection is in a global var

    // start the transaction outside of the try block, because
    // you don't want to rollback a transaction that failed to start
    $pdo->beginTransaction(); 
    try
    {
        $callback();
        $pdo->commit(); 
    }
    catch (Exception $e) // it's better to replace this with Throwable on PHP 7+
    {
        $pdo->rollBack();
        throw $e; // we still have to complain about the exception
    }
}

Usage example:

transaction(function()
{
    global $pdo;

    $pdo->query('first query');
    $pdo->query('second query');
    $pdo->query('third query');
});

This way the transaction-management code is not duplicated across the project. Which is a good thing, because, judging from other PDO-ralated answers in this thread, it's easy to make mistakes in it. The most common ones being forgetting to rethrow the exception and starting the transaction inside the try block.

Pry

Answer 7

I made a function to get a vector of queries and do a transaction, maybe someone will find out it useful:

function transaction ($con, $Q){
        mysqli_query($con, "START TRANSACTION");

        for ($i = 0; $i < count ($Q); $i++){
            if (!mysqli_query ($con, $Q[$i])){
                echo 'Error! Info: <' . mysqli_error ($con) . '> Query: <' . $Q[$i] . '>';
                break;
            }   
        }

        if ($i == count ($Q)){
            mysqli_query($con, "COMMIT");
            return 1;
        }
        else {
            mysqli_query($con, "ROLLBACK");
            return 0;
        }
    }

Landsknecht

Answer 8

I had this, but not sure if this is correct. Could try this out also.

mysql_query("START TRANSACTION");
$flag = true;
$query = "INSERT INTO testing (myid) VALUES ('test')";

$query2 = "INSERT INTO testing2 (myid2) VALUES ('test2')";

$result = mysql_query($query) or trigger_error(mysql_error(), E_USER_ERROR);
if (!$result) {
$flag = false;
}

$result = mysql_query($query2) or trigger_error(mysql_error(), E_USER_ERROR);
if (!$result) {
$flag = false;
}

if ($flag) {
mysql_query("COMMIT");
} else {        
mysql_query("ROLLBACK");
}

Idea from here: http://www.phpknowhow.com/mysql/transactions/

Hodeida

Not correct code. trigger_error will return true (unless you've screwed up your call), so $result will always be true, so this code will miss any failed query, and always attempt to commit. Equally troubling, you are using the old deprecated mysql_query, instead of using mysqli, even though you link to a tutorial that uses mysqli. IMHO, you should either delete this bad example, or replace it to use code as written in the phpknowhow tutorial. – Timeless

Answer 9

One more procedural style example with mysqli_multi_query, assumes $query is filled with semicolon-separated statements.

mysqli_begin_transaction ($link);

for (mysqli_multi_query ($link, $query);
    mysqli_more_results ($link);
    mysqli_next_result ($link) );

! mysqli_errno ($link) ?
    mysqli_commit ($link) : mysqli_rollback ($link);

Greybeard

Somewhat strange code, but at least it suggests the use of mysqli_multi_query. Anyone interested in that should google elsewhere for cleaner example. – Timeless

mysqli_multi_query() is not advisable if there is any user-supplied data in the queries. Prepared statements are not supported in this function. – Cioban