How to judge whether or not a transaction succeeded or failed?

Question

I'm using MySQL and PHP.

Running this transaction:

begin;
....
commit;

How should I judge whether it failed in PHP?

Answer 1

In general you should expect an error to be returned from the database if the transaction fails same as if you try and execute a single duff query.

Note that not all MySQL table types support transactions.

Answer 2

Check the result (mysql_query, PHP Manual):

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

Example (PHP4-style): EDIT: Improved example per johannes's comment

mysql_connect("localhost", "username", "password") or die( mysql_error() );

mysql_select_db("test") or die(mysql_error());

$query = "INSERT INTO ..."; //Query here

mysql_query("BEGIN"); // transaction begins

$result = mysql_query($query);

if(!$result)
{
    mysql_query("ROLLBACK"); //Transaction rolls back
    echo "Rolled Back.";
    exit;
}
else 
{
    $committed = mysql_query("COMMIT"); //Commit Transaction
    if(!$committed)
    {
        //Commit Failed, take appropriate action
    }
    else
    {
        echo "Successful Insert.";
    }
}

Or, for PDO usage, SEE http://stackoverflow.com/questions/272203/pdo-try-catch-usage-in-functions

Answer 3

After each executed SQL, check that the DB did not return an error. Use http://www.php.net/manual/en/function.mysql-errno.php to check for errors.

The DB may return errors at any point, and every SQL should always be checked anyways...