Our application fires an INSERT query to the MySQL Database to add records. I want to know whether or not the records get auto-committed. If I run the ROLLBACK command, when does the database perform a rollback ? Is a ROLLBACK possible after a COMMIT ?
The answer to your question depends on whether-or-not you're within a transaction that will span over more than one statement. (You've tagged the question with InnoDB, the answer would be different with MyISAM.)
From the reference manual: http://dev.mysql.com/doc/refman/5.1/en/commit.html
So yes, by default, if you're just using
However, if you're dealing with transactions explicitly, you will have to use
You can start a transaction explicitly by using
More specifically "another way to begin a transaction" seems to imply that setting "autocommit=0" is enough to start a transaction (at least just before each statement at the start a session or that follows a
(How you start a transaction may depend on the way your application uses MySQL.)
By default, InnoDB is set to autocommit = 1 or ON. Once committed, they cannot be rolled back.
You would have to do one of two things to disable it going forward:
OPTION 1 : Add this to /etc/my.cnf and restart mysql
OPTION 2 : Perform one of these in the open DB Conenction before beginning any meaningful SQL
Under these two options, you would have to perform a manual COMMIT or a manual ROLLBACK.
If the table is MyISAM, then the explanation is simpler. Since there are no transactions for the MyISAM storage engine, all INSERTs, UPDATEs, and DELETEs executed are permanent. No rollbacks whatsoever.
|show 5 more comments|