How to Manage MySQL Transactions Effectively in PHP?

PHP MySQL Transaction Examples

Transactions in PHP and MySQL allow you to group a set of queries as a single atomic unit of work. This means that if one of the queries fails, all of the changes made by the transaction are rolled back.

How to Use Transactions

To use transactions in PHP and MySQL, you will need to:

1. Start a transaction by calling mysql_query("SET AUTOCOMMIT=0") and mysql_query("START TRANSACTION").
2. Execute your queries.
3. If all of the queries are successful, commit the transaction by calling mysql_query("COMMIT").
4. If any of the queries fail, rollback the transaction by calling mysql_query("ROLLBACK").

Example

Here is a simple example of how to use transactions in PHP and MySQL:

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");
}

In this example, the COMMIT statement is only executed if both $a1 and $a2 are successful. If either of the queries fails, the ROLLBACK statement is executed.

Using Transactions with PDO

The PDO class provides a more modern and object-oriented way to work with MySQL transactions. To use transactions with PDO, you will need to:

1. Create a PDO object and set the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION.
2. Start a transaction by calling $pdo->beginTransaction().
3. Execute your queries.
4. If all of the queries are successful, commit the transaction by calling $pdo->commit().
5. If any of the queries fail, rollback the transaction by calling $pdo->rollback().

Example

Here is an example of how to use transactions with PDO:

try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->beginTransaction();

    $pdo->exec("INSERT INTO rarara (l_id) VALUES('1')");
    $pdo->exec("INSERT INTO rarara (l_id) VALUES('2')");

    $pdo->commit();
} catch (\PDOException $e) {
    $pdo->rollback();
    throw $e;
}

In this example, the commit() method is only called if both queries are successful. If either of the queries fails, the rollback() method is called and an exception is thrown.

The above is the detailed content of How to Manage MySQL Transactions Effectively in PHP?. For more information, please follow other related articles on the PHP Chinese website!