0

I use this code to insert data in 2 tables, 

try {
    array(PDO::ATTR_PERSISTENT => true);  
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->beginTransaction();
    // 1st insert
    $db->exec("insert into users (id, username, password,permission) values (18, 'raman', 2002,3)");
    //2nd insert 
    $db->exec("insert into table_11 (id,user_id1,amount) values (14, '130', '11300')"); 

    $db->commit();
} catch (Exception $e) {
    $db->rollBack();
    echo "Failed  : " . $e->getMessage();
}

This code work fine however when i test the transaction by make 2nd insert error as i changed table name to wrong table name, i found 1st insert still work and able to add new data even 2nd insert not work, My question why 1st insert worked ? I use MySQL.

  • 1
    What database are you using? – deceze May 02 '18 at 12:20
  • 3
    What storage engine are you using? Some (e.g. [MyISAM](https://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html)) don't support transactions so the first insert will happen regardless of what happens with the second. – Nick May 02 '18 at 12:46
  • This line `array(PDO::ATTR_PERSISTENT => true);` does not perform any action. If you are intending to set persistent connections, that array should be passed as the options argument to `new PDO()` – Michael Berkowski May 02 '18 at 21:39
  • i use MySQL, this code use PHP –  May 02 '18 at 21:47
  • They are not talking about what type of db you are using, they are asking about the storage engine, see here https://stackoverflow.com/questions/4233816/what-are-mysql-database-engines – andrew May 02 '18 at 21:57

1 Answers1

0

I found why this code not work as expected, i should be use InnoDB when i create the tables,so i removed current tables and create new one with InnoDB storage engine, now it work fine.