try{
//Assuming my insert statement is inserting new record to the table ,and it's id is auto incrmented
$cmd = $this->connection->prepare('INSERT STATEMENT');
$cmd->execute(array(parameters here));
/*This insert statement here is failing due to duplicate key.,it means no records is inserted.
* now I want to reset the autoincremented where it is last id that is being used in my first insert statement
* and delete that record in my first table.
*
*/
$cmd = $this->connection->prepare('INSERT STATEMENT');
$cmd->execute(array(parameter here));
$cmd = null;
return "Inserted";
} catch (PDOException $ex) {
return $ex->getMessage();
}
This depends on your database definitions. Disable the autocommit from your database client (‘PHPMyAdmin’ for example). Or prepare a statement in your PHP script to disable it (such as ‘SET autocommit=0’ in MySQL).
MyISAM sucks. Back in the day it had a speed advantage over InnoDB, but that advantage is now to narrow to be of consequence on most sites. On the whole there are multiple data integrity features missing from that engine in the name of speed, and if you use it you’re always just one borked query from nuking the whole thing and having to rebuild the table.
I agree, The first thing I do on the rare occasions I use a third party script is to make sure any myisam references are replaced with innodb. Even where the script obviously doesn’t use any of the additional database features it is still worthwhile to use a superior engine.
I use innodb unless I have a specific reason for using another engine, such as heap for dbsessions. Myisam is more or less a legacy engine having no advantages.
If you’re just starting out use innodb. Db engine choices are an optimization issue, something that should be left towards the project’s end. (Although engine choice can tie your hands, like an engine that lacks transactional support).
Heap is the older term, they are more usually called memory tables. Since they exist in server memory only they are very fast, but all the data is lost on server power down.
They are suitable for database implementations of sessions. You would want to do this on larger server farms with multiple front end servers, a load balancer and one database. In smaller sites using the inbuilt session system is sufficient.
In regards to the database engines, you want to make sure you run as new version of MySQL as possible, at least 5.6 to take advantage of the optimization they are doing to the InnoDB engine. If you have the ability, I would recommend using Percona instead of basic MySQL as well.
I would not recommend using HEAP/Memory for sessions.
The main downside with this engine is the same as MyISAM, i.e. table locking instead of row locking on updates. In addition it also have restrictions on the column size (no text/blob), which put a limit on what you can store in the sessions.
If you run a busy website, using InnoDB for the sessions can be faster due to row locking. And if you store more in the session than you can save in a varchar column (with the session serialization included) you need to use another engine as well.
Memory tables have their uses, and we have found them very effective when doing massive batch updates, and you want to keep the data for a final verification (excluding using temporary tables). First run a query, inserting into the memory table every records that has to be updated, then manipulate the data, before running another query updating the original records. You can easily update millions of records in a fraction of a second this way. (Though, be aware that by default Memory tables have a limit of 16mb, so if you intend to use them as described here, that setting need to be updated as you fast eat up 16mb of data).