PHP + MySQL transactions examples
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");
}
Solution 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
andPDO::ERRMODE_EXCEPTION
- See
- 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.
Solution 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");
}