Transaction and commit

Question

Which code is right for transaction? do i need to check query result for commit?

this code result nothing

mysql_query("BEGIN");
$strSQL = "INSERT INTO table values";
$strSQL .="('','a')";
$objQuery1 = mysql_query($strSQL);
$strSQL = "INSERT INTO table values";
$strSQL .="('','a','a')";
$objQuery2 = mysql_query($strSQL);
if(($objQuery1) and ($objQuery2))
{
mysql_query("COMMIT");
echo "Save Done.";
}
else
{
mysql_query("ROLLBACK");
}
?>

or

this code rusult 1 insert. why?wont commit recognize the error?

  <?php
    mysql_query("BEGIN");
    $strSQL = "INSERT INTO table values";
    $strSQL .="('','a')";
    $objQuery1 = mysql_query($strSQL);
    $strSQL = "INSERT INTO table values";
    $strSQL .="('','a','a')";
    $objQuery2 = mysql_query($strSQL);
    mysql_query("COMMIT");
    ?>

Answer 1

What might be confusing you is that issuing a commit in MySQL does not translate to rollback [everything] on error. It instead translates to: commit stuff that had no errors.

mysql> create table test (id int unique);
Query OK, 0 rows affected (0.10 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (1);
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

In Postgres, by contrast:

test=# create table test (id int unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_id_key" for table "test"
CREATE TABLE
test=# begin;
BEGIN
test=# insert into test values (1);
INSERT 0 1
test=# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_id_key"
DETAIL:  Key (id)=(1) already exists.
test=# commit;
ROLLBACK
test=# select * from test;
 id 
----
(0 rows)

On a separate note, consider using mysqli instead. It supports this kind of stuff directly:

http://www.php.net/manual/en/mysqli.commit.php

Or PDO:

http://php.net/manual/en/pdo.commit.php

Using PDO, the correct sequence will look like this:

try {
  # begin transaction
  # do stuff
  # commit
} catch (Exception $e) {
  # rollback
}

Using MySQLi, you can make it behave like the above too with the or operator:

try {
  # begin transaction
  # do stuff or throw new Exception;
  # commit
} catch (Exception $e) {
  # rollback
}

Answer 2

I think the calling mysql_query("COMMIT") needs to be determined by the success of previous queries. Thus, in the above code nothing is commited to db because one of the 2 previous queries fail.