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
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.
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");}
<?php// trans.phpfunction begin(){ mysql_query("BEGIN");}function commit(){ mysql_query("COMMIT");}function rollback(){ mysql_query("ROLLBACK");}mysql_connect("localhost","Dude1", "SuperSecret") or die(mysql_error());mysql_select_db("bedrock") or die(mysql_error());$query = "INSERT INTO employee (ssn,name,phone) values ('123-45-6789','Matt','1-800-555-1212')";begin(); // transaction begins$result = mysql_query($query);if(!$result){ rollback(); // transaction rolls back echo "transaction rolled back"; exit;}else{ commit(); // transaction is committed echo "Database transaction was successful";}?>