Is it possible to do an sql transaction across multiple function into one?

Question

I am using a few functions inside another function that updates certain things, deletes some and inserts, now my problem, if 2 above are successful or 1 and the rest aren't it could cause catastrophic outcomes. So is it possible to use 1 transaction for all 3 functions... for example:

public static function do_stuff()
{
    //run sql in function
    SELF::function_sql_one_insert();

    SELF::function_sql_two_update();

    SELF::function_sql_three_delete();
}

Like so:

public static function test()
{
    SELF::function_sql_one_insert();

    SELF::function_sql_two_update();

    SELF::function_sql_three_delete();
}

public static function function_sql_one_insert()
{
    //sql to run
    $sql = "INSERT INTO table
            (
                fake_row_one,
                fake_row_two
            )
            VALUES
            (
                ?,
                ?
            )";

    //run sql
    $fake_insert = $database->prepare($sql);
    $fake_insert->execute("yeah", "okay");
}

public static function function_sql_two_update()
{
    //sql to run
    $sql = "UPDATE table
            SET fake_row_one = ?
            WHERE fake_row_two = ?";

    //run sql
    $fake_update = $database->prepare($sql);
    $fake_update->execute("blahblah", "okay");
}

public static function function_sql_three_delete()
{
    //sql to run
    $sql = "DELETE FROM TABLE
            WHERE fake_row_two = ?";

    //run sql
    $fake_delete = $database->prepare($sql);
    $fake_delete->execute("okay");
}

What I am trying to acomplish is if one fails revert all of them back. Is this possible? If not what can I do instead, if so, is there any cons to this?

Have you read http://php.net/manual/en/pdo.begintransaction.php ? — Mike, May 06 '16 at 22:56
Yeah, you should be able to start a transaction before running the functions and commit afterward. You could make the inner functions return boolean success indicators and roll back if needed based on those. — Don't Panic, May 06 '16 at 22:57
Should I begin the transaction in the main function or the three where I am doing the sql stuff? — 4334738290, May 06 '16 at 23:01
There's no general answer to that, it depends on what you need. For instance, is it possible that you might want to call `test()` as a part of some larger transaction that was already started? Then you shouldn't start the transaction there. — Barmar, May 06 '16 at 23:02
This is just my opinion, but if catastrophic outcomes are possible from not executing all three, I would not want to create any function that would execute just one of them to begin with. — Don't Panic, May 06 '16 at 23:04
I think it's worth mentioning that it's also possible to check if a transaction has already been started. http://php.net/manual/en/pdo.intransaction.php — Don't Panic, May 06 '16 at 23:07

score 1 · Answer 1 · answered May 07 '16 at 00:02

Php functions has absolutely nothing to do with database transactions. It's just irrelevant matters.

A database transaction is bound to database connection only. Thetefore, as long as all your functions use the same connection, there is no problem to run all three in a transaction.

score 0 · Answer 2 · edited May 23 '17 at 11:59

You should use database transactions for this.

Essentially,

you start a transaction,
you do your SQL queries
if it fails somewhere, you do a rollback and otherwise you do a commit

But there are a few gotcha's such as certain sql statements that commit on their own, so read all the official docs for the database you're using.

More info: http://php.net/manual/en/pdo.begintransaction.php

But remember that not all databases support transactions to start with. E.g. MyISAM tables in MySQL do not support transactions.

You might want to convert those tables to InnoDB see here e.g.: How to convert all tables from MyISAM into InnoDB?

score 0 · Answer 3 · edited Dec 19 '17 at 19:32

I use this way:

class Teste {
public $mysqli;
public $erro = array();

        public function __construct(){
        $this->mysqli = new \mysqli(DB_HOST,DB_USERNAME,DB_PASSWORD,DATABASE);
        $this->mysqli->set_charset("utf8");
        }

        public function start_trans(){
             $this->mysqli->autocommit(false);
        }

        public function end_trans(){

            if(count($this->erro) == 0){
            $this->mysqli->commit();
            echo "success";
            } else {
            $this->mysqli->rollback();
            echo "error";
            }

        }


        public function example1(){

            $stmt = $this->mysqli->query("insert into veiculos (placa, modelo) values (1,1)");

            if(!$stmt){
            $this->erro[] = "Erro #143309082017 <code>" . $this->mysqli->error . "</code>";
            }

            return (count($this->erro) < 1)? true : false;

        }


        public function example2(){

            $stmt = $this->mysqli->query("insert into veiculos (placa, modelo) values (2,2)");

            if(!$stmt){
            $this->erro[] = "Erro #143309082017 <code>" . $this->mysqli->error . "</code>";
            }

            return (count($this->erro) < 1)? true : false;

        }


        public function example3(){

            $this->mysqli->autocommit(false);

            $stmt = $this->mysqli->query("insert into veiculos (placa, modelo) values (3,3)");

            if(!$stmt){
            $this->erro[] = "Erro #143309082017 <code>" . $this->mysqli->error . "</code>";
            }

            return (count($this->erro) < 1)? true : false;

        }
}


        $action = new Teste;

        $action->start_trans();

        $action->example1();
        $action->example2();
        $action->example3();

        $action->end_trans();

score -1 · Answer 4 · answered May 06 '16 at 23:06

-1

Maybe you can try something like this:

$working = true;
try
{
     SELF::function_sql_one_insert();
} catch (Exception $e)
{
    if($e != "")
        $working = false;
}

Try this for all functions. If $working is true, you can execute all commands.

answered May 06 '16 at 23:06

Tom291

509
1
4
14

1

I don't think `try...catch` is a useful substitute for a database transaction. – Don't Panic May 06 '16 at 23:23

Is it possible to do an sql transaction across multiple function into one?

4 Answers4