PHP + MySQL Transactions Examples

PHP + MySQL transactions examples

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.

PHP Mysql - transactions using group by

Im just not sure this should be the single source of truth,

There can be only one source of truth about any given fact, by definition.

Attempting to create a second source of truth means you have no source of truth, because it relegates both to mere sources of potentially inconsistent opinions.

Keeping a separate table with balances is delicate territory. It's easy to make naïve assumptions that overlook this.

In spite of this, it could make sense, for example, to have a table with static balances so that you have a lightweight option for fetching the customer's balance for display at the top of each page... but this table has to be treated like the untrustworthy villain that it is (or can become).

Tables like this are particularly prone to novice mistakes that lead to race conditions and anomalies, such as reading the balance into a variable, programmatically adjusting its value, and writing the new value back to the database. That's simply not how you do it. Yet, I've seen it done that way so many times (particularly by people who still fail to understand that there is no such thing as a good ORM, because ORMs are an intrinsically flawed concept). Such a table should be maintained by triggers, not by application code, and updates should be atomic.

You did show an atomic update in your example...

UPDATE account SET balance = balance - ? WHERE id = ? AND balance >= ?

...although you need to use caution, here, because this will successfully update 0 rows if balance >= ? is not true... and, much worse, a bug in your code where the first placeholder value is null, SET balance = balance - NULL will set the balance to NULL, since a null operand (correctly) causes most operations to evaluate to null.

If you want this table as an optimization, then safe design would always ensure consistency by auditing the balance with a calculation from the transactions before doing anything important (like a withdrawal or transfer), and blatantly refuse to proceed if a discrepancy is found, explaining to the user that the site is "having problems" and creating an internal support incident for you... because this means you have a bug that is allowing the values in the balance table to diverge from the transaction table, which is a sign of Very Bad Things™.

Much of this of course is mediated by using database transactions correctly, understanding isolation levels and using locking reads in critical sections. If the balance table is only modified by a BEFORE INSERT trigger on the transaction table, and not by your code, then there few opportunities for the balance to drift.

Sanity check are still critical, and in spite of all the hand wringing and arm waving you'll get from people who insist that triggers have excessive performance penalties and should be avoided... they have absolutely no idea what they are talking about. Yes, technically speaking, the mass of the fuel in your car's fuel tank has a negative impact on your fuel economy, but it's a critical part of the operation. You don't give it any thought. You don't drive around with a mostly-empty tank. The cost of firing triggers is negligible, particularly in light of the way they help a database look after itself. Don't give the cost of triggers a second thought.

But the scalability concern for a table like this is largely taken care of by proper indexes. An index in the transaction table on (account_id, created_on), for example, means the database has a way of instantly locating all of the transactions for one specific account, already sorted by the order in which they occurred, without scanning the table. You should find that even among hundreds of millions of rows, finding the transactions for a given account is speedy, with a proper index.

SELECT sum(amount) implies that the transaction amounts are signed, which means the type column is somewhat superfluous, though you might want to keep it and trap it with a trigger requiring credits to be > 0 and debits to be < 0. Sanity checks inside the database, regardless of how seemingly-obvious/unnecessary are, are rarely a bad idea when money is involved.

You should probably also have BEFORE DELETE and BEFORE UPDATE triggers that deny any changes to the transaction table. Transactions are historical facts that do not change. An incorrect transaction is properly undone by creating a second, offsetting transaction.

A simple example of preventing all deletes on a given table in MySQL Server 5.5 and later looks like thjs:

mysql> CREATE TRIGGER transaction_bd
       BEFORE DELETE ON transaction
       FOR EACH ROW
       SIGNAL SQLSTATE '45000'
       SET MESSAGE_TEXT = 'the transaction table does not support DELETE.';

(The usual DELIMITER declarations aren't needed for a trigger with a non-complex body.)

PHP PDO MySQL and how does it really deal with MySQL transactions?

https://dev.mysql.com/doc/refman/5.7/en/innodb-autocommit-commit-rollback.html says:

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.

So when you set autocommit=0 in a session (call it session 1), this implicitly opens a transaction, and leaves it open indefinitely.

The default transaction isolation level is REPEATABLE-READ. So your session will not see a refreshed view of committed changes from other sessions' work until session 1 explicitly commits or rolls back.

Your LOCK TABLES in another session 2 does cause an implicit commit, but session 1 doesn't see the result because it's still only able to see an isolated view of the data because of its own transaction snapshot.

How to use transaction in php/mysql

Simply issue mysql_query('START TRANSACTION'); and check for errors at every one of your inserts. If one of them doesn't succeed issue a ROLLBACK immediately without doing any of the remaining queries. If everything goes fine with all of them issue a COMMIT.

It may be easier to put them in a try-catch block to avoid using too many levels of nesting with if-else.

// START TRANSACTION
try{
    // INSERT 1
    if(failed)
        throw new Exception();

    // INSERT 2
    if(failed)
        throw new Exception();

    // INSERT 3
    if(failed)
        throw new Exception();

    // COMMIT
}
catch(Exception $e){
    // ROLLBACK
}

You may also want to take a look into PHP's PDO extension. Transactions are part of its features.

PHP & mySQL: Simple code to implement Transaction - Commit & Rollback

You need to use the mysqli extension to use this functionality.

See: autocommit(), commit(), and rollback()

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* disable autocommit */
mysqli_autocommit($link, FALSE);

mysqli_query($link, "CREATE TABLE myCity LIKE City");
mysqli_query($link, "ALTER TABLE myCity Type=InnoDB");
mysqli_query($link, "INSERT INTO myCity SELECT * FROM City LIMIT 50");

/* commit insert */
mysqli_commit($link);

/* delete all rows */
mysqli_query($link, "DELETE FROM myCity");

if ($result = mysqli_query($link, "SELECT COUNT(*) FROM myCity")) {
    $row = mysqli_fetch_row($result);
    printf("%d rows in table myCity.\n", $row[0]);
    /* Free result */
    mysqli_free_result($result);
}

/* Rollback */
mysqli_rollback($link);

if ($result = mysqli_query($link, "SELECT COUNT(*) FROM myCity")) {
    $row = mysqli_fetch_row($result);
    printf("%d rows in table myCity (after rollback).\n", $row[0]);
    /* Free result */
    mysqli_free_result($result);
}

/* Drop table myCity */
mysqli_query($link, "DROP TABLE myCity");

mysqli_close($link);
?>

PHP/MySQL - How to use MySQL BEGIN/COMMIT with PDO

Transaction syntax:

START TRANSACTION
[transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic:
WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY

BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK
[WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1}

Transaction example:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Taken from here.

You intend to create a transaction via PDO. That is not really a problem. You can do it by generating the query text accordingly:

$query = $db -> prepare 
                        ("
                        START TRANSACTION;
                        INSERT INTO chat (chat_id,msg,datetime) 
                        VALUES (:cid,:msg,:datetime)
                        INSERT INTO chat_connect (chat_id,sender_id,receiver_id)
                        VALUES (:cid2,:sender_id,:receiver_id);
                        COMMIT;
                        ");
$query -> execute(array(
            "cid" => $cid,
            "msg" => $msg,
            "datetime" => $datetime,
            "sender_id" => $getid,
            "receiver_id" => $frid,
            "cid2" => $cid
            ));

Here you can see how you can write a bullet-proof transaction.

Mysql transactions within transactions

This page of the manual might interest you : 12.3.3. Statements That Cause an Implicit Commit; quoting a few sentences :

The statements listed in this section
(and any synonyms for them) implicitly
end a transaction, as if you had done
a COMMIT before executing the
statement.

And, a bit farther in the page :

Transaction-control and locking
statements. BEGIN, LOCK TABLES,
SET autocommit = 1 (if the value is
not already 1), START TRANSACTION,
UNLOCK TABLES.

PHP + MySQL Transactions Examples