CODEWITHSUNDEEP
phpmysqltransactions
Arxeiss
Arxeiss

Reputation: 1046

MySQL transaction is committed even when error occurred

In my project, I delete and update lots of rows across many tables, so I decided to use transaction. But transaction is committed even, when error occurred and script is ending.

I use only mysql_ not mysqli_ or PDO because on that server it isn't supported.

mysql_query("START TRANSACTION");
$res = mysql_query("some insert...");
if($res === false){
  //this save error log and exit script with die() or exit()
  trigger_error(mysql_errno()."\n".mysql_error()); 
}
$res = mysql_query("some delete...");
if($res === false){
  trigger_error(mysql_errno()."\n".mysql_error()); 
}
mysql_query("COMMIT");

In my project I have for mysql my class so I don't type it like this, but this way it works.

When after first query error happen, error log is saved end script is ended. But inserted data stay in DB. I though, when COMMIT isn't executed and connection is closed, automatically ROLLBACK happen.

P.S. I know, that using mysqli should be better, but this is the same for mysql_ and mysqli_, isn't it?

Upvotes: 0

Views: 5478

Answers (2)

broadband
broadband

Reputation: 3488

Are you sure the data stays in the database / is commited without executing commit query ?

MySQL documentation:

To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summary=@A WHERE type=1;

COMMIT;

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

I tested this code using MySQL 5.5 and PHP 5.3:

sql> create table t10 (id integer auto_increment primary key, status char(32))

<?php

  if( ($link = mysql_connect("localhost", "db_user", "*****")) === false )
    exit(1);

  mysql_select_db("test", $link);

  var_dump(mysql_query("start transaction;"));
  var_dump(mysql_query("insert into t10 (status) values (\"foo1\"), (\"foo2\");"));
  exit();
  //var_dump(mysql_query("commit;"));
  var_dump(mysql_close());
?>

Values foo1 and foo2 are not inserted in database table t10. Also keep in mind that MySQL transactions are not fully atomic in the sense of the “all or nothing” rule. You can commit a transaction with half of the statements done, and the other half failed (source: Kouber Saparev on December 3 2010 11:01am at MySQL page http://dev.mysql.com/doc/refman/5.5/en/ansi-diff-transactions.html).

Test case:

mysql> create table t7 (id integer primary key auto_increment, name text, lastname text);
Query OK, 0 rows affected (0.04 sec)

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

mysql> insert into t7 (name, lastname) values('name1', 'lastname1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t7 (name, lastname) values('name1', 'lastname1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t7 (name, lastname) ('name1', 'lastname1');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ''name
1', 'lastname1')' at line 1
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t7;
+----+-------+-----------+
| id | name  | lastname  |
+----+-------+-----------+
|  1 | name1 | lastname1 |
|  2 | name1 | lastname1 |
+----+-------+-----------+
2 rows in set (0.00 sec)

We see that failed insert query did not count even transaction was successfully commited.

Upvotes: 1

Noam Rathaus
Noam Rathaus

Reputation: 5598

I suggest to read: http://dev.mysql.com/doc/refman/5.0/en/commit.html, specifically about SET autocommit=0. I believe that if you want it to NOT autocommit you need to say it, and only then will not calling COMMIT automatically ROLLBACK your statement.

It is best though to have a handler code that does the call to ROLLBACK otherwise you are in danger of trusting MySQL to do all the work for you and any future changes to MySQL related to TRANSACTIONS might not get properly handled by your code.

Upvotes: 2

Related Questions