How to Start and End Transaction in MySQLi

How to start and end transaction in mysqli?

Update Novembre 2020: @Dharman gave a better answer with more details about transactions in mysqli, just check it instead: https://stackoverflow.com/a/63764001/569101 /p>

Well according to the php doc, you're right.

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

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

$mysqli->query("CREATE TABLE Language LIKE CountryLanguage");

/* set autocommit to off */
$mysqli->autocommit(FALSE);

/* Insert some values */
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

/* commit transaction */
$mysqli->commit();

/* drop table */
$mysqli->query("DROP TABLE Language");

/* close connection */
$mysqli->close();
?>

In the example above:

  • the CREATE TABLE is auto committed because it's the default behaviour.
  • the INSERT INTO aren't auto committed because of the autocommit(FALSE).
  • the DROP TABLE is auto committed because the autocommit(FALSE) was reset by the ->commit();.

What happens to a mysqli transaction when the connection is closed without a commit or rollback?

When you call begin_transaction() or set the autocommit value to 0 then you are effectively telling MySQL server "don't commit the data until I explicitly tell you to". When you write code that never calls commit then the data on the server will never be committed.

When you call mysqli::close() or if the PHP script ends, then mysqlnd (or libmysql) will send COM_QUIT command to the server. The server will then close the session and discard any data related to it, including open transactions, locks, or prepared statement handles. The MySQL server should also honour the wait_timeout setting in case the PHP script crashes and the command is never sent.

One thing that would be specific to mysqli is persistent connections. These connection are reused between PHP executions. They are commonly regarded as a good way to shoot yourself in the foot, which is why mysqli has some logic to help with that. You can use the INI setting called rollback_on_cached_plink to instruct mysqli to clean up the persistent connection whenever the script ends by issuing a rollback command.

Mysqli transaction query over normal php mysqli query

As @raymond-nijland pointed out, PHP client does not support multiple SQL statements separated with semicolon.

But this is half-true.
You can use multi_query:

$sql = "START TRANSACTION; INSERT INTO songs (title, disco, deleted) VALUES ('".$titol."', '".$codi."', '0'); SET @last_id = LAST_INSERT_ID(); INSERT INTO lyrics (`lyricsOri`, `lyricsTra`, `song`, `deleted`) VALUES ('".$lyricsO."', '".$lyricsT."', @last_id, 0); COMMIT;";

connection()->multi_query($sql);

Or, as you wrote, using a php transaction, which will takes you to the same result:

    connection()->begin_transaction(MYSQLI_TRANS_START_READ_ONLY);
    connection()->query("START TRANSACTION;");
    connection()->query("INSERT INTO songs (title, disco, deleted) VALUES ('".$titol."', '".$codi."', '0');");
    connection()->query("SET @last_id = LAST_INSERT_ID();");
    connection()->query("INSERT INTO lyrics (`lyricsOri`, `lyricsTra`, `song`, `deleted`) VALUES ('".$lyricsO."', '".$lyricsT."', @last_id, 0);");
    connection()->query("COMMIT;");
    connection()->commit();
    connection()->close();

Hope it helps!

best way to start a mysql transaction in php mysqli and PDO

Your question isn't clear still I'm answering it. It is straightforward to begin and commit a transaction. In PDO, do something like this:

<?php
    $pdo = // connect to database with PDO
    $pdo->beginTransaction();

    // query database here

    $result = // query result
    if($result) {
        // if result is okay
        $pdo->commit();
    } else {
        $pdo->rollBack();
    }
?>

**Explanation: **

  1. First of all, you start a transaction
  2. Next, you query database
  3. If result is okay, commit the transaction
  4. If result is not okay, rollBack and the query will not execute

Reference: http://php.net/manual/en/pdo.transactions.php

About Auto-commit: In auto-commit mode, each query is a complete transaction and it is executed instantly. By default, PDO auto-commits each query. Turning off the auto-commit will need you to commit the query manually. In general scenario, you should not turn it off.

When you need to execute an important query, i.e multi-part queries that depend on each other for their final result like an amount transfer between two accounts (where you need to deduct amount from one table and to add it to another table), simply begin a transaction. PDO will not execute any query until you commit the transaction. If something goes wrong, everything will be rolled back to its previous state.

Lastly, there isn't any big difference between turning off auto-commit and beginning a transaction. By beginning transactions, you can simplify the tasks otherwise you will need to manually commit each query regardless of its nature.

I hope it answers your question.

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.

How to Rollback and Commit using MySQLi Transactions?

You've to start transaction before doing this.

Because You must tell to database that You're going to start transaction.

You've to put: $db->begin_transaction(); after autocommit(FALSE);

Please read documentation: mysqli::begin_transaction




P.S. Please keep in mind it cannot be done with tables that engine does not support transactions. So if after adding begin_transaction statement rollback() did not work, check Your tables engine that it's set to engine with transaction support.

To check Your tables engine call query in mysql terminal:

SHOW TABLE STATUS FROM database_name_goes_here;

You'll get list of tables in Your database with engines defined.

To get list of transaction-safe engines You can do by calling query in mysql terminal (find Transactions: YES):

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
...

PHP prepared statements and transactions in a loop

Your loop can be optimized by pulling the prepare and bind_param statements out of the loop.

$value = null;
$mysqli->autocommit(FALSE);
$sql  = "INSERT INTO temp (`fund_id`) VALUES (?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $value);
foreach ($pdata as $value) {
    $stmt->execute();
}
$mysqli->commit();

You have turned off autocommit with your autocommit(FALSE) line and therefore don't need to use the START TRANSACTION statement.


Related Topics

Weird PHP Error: 'Can't Use Function Return Value in Write Context'

What Are Namespaces

Return a PHP Page as an Image

Remove Index.PHP from Url - Codeigniter 2

Get Specific Columns Using "With()" Function in Laravel Eloquent

How to Loop Through This Array in PHP

Send File Attachment from Form Using PHPmailer and PHP

How to Push Both Value and Key into PHP Array

Can You "Compile" PHP Code and Upload a Binary-Ish File, Which Will Just Be Run by the Byte Code Interpreter

Processing Large Json Files in PHP

How to Define a Class Property Value Dynamically in PHP

PHP - Insert a Variable in an Echo String

How to Declare a Global Variable in PHP

Basic PHP and Ajax

Sorting a PHP Array of Arrays by Custom Order

Remove All Attributes from HTML Tags

PHP & MySQL: MySQLi_Num_Rows() Expects Parameter 1 to Be MySQLi_Result, Boolean Given

PHP Include Relative Path


Leave a reply



Submit