Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
Just for clarification, I tagged is as 'innodb' 19 hours ago, since InnoDB uses COMMIT/ROLLBACK. –  RolandoMySQLDBA Aug 3 '11 at 15:11
    
This gets a +1 for reminding Developers and DBAs to pay attention of transactional behavior, the correspongding application paradigms supporting transactions, and its consequences (good or bad). –  RolandoMySQLDBA Aug 3 '11 at 15:19
    
I answered your question with a comment under my answer. –  RolandoMySQLDBA Aug 4 '11 at 12:37

2 Answers 2

up vote 5 down vote accepted

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

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent.

So yes, by default, if you're just using INSERT, the records you insert will be committed, and there is no point trying to roll them back. (This is effectively the same as wrapping each statement between BEGIN and COMMIT.)

However, if you're dealing with transactions explicitly, you will have to use COMMIT to commit store the records, but you will also be able to use ROLLBACK.

You can start a transaction explicitly by using START TRANSACTION (or BEGIN). This is independent of the autocommit setting (on by default):

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

Alternatively, if autocommit=0, I think any statement following another end of transaction, will start a transaction (but you can still use START TRANSACTION explicitly); that's at least the way I interpret this:

The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See Section 12.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

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 COMMIT/ROLLBACK). I would suggest to use BEGIN or START TRANSACTION explicitly anyway even if autocommit=0, as it can make it clearer to see when the transaction starts or ends.

(How you start a transaction may depend on the way your application uses MySQL.)

share|improve this answer
1  
Deserves a +1 for fully defining transactional protocols. –  RolandoMySQLDBA Aug 5 '11 at 10:32
    
@Bruno, For MyISAM where "commit" and "rollback" don't work, wouldn't the inserts be half-committed? –  Pacerier Jan 9 at 9:01

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

[mysqld]
autocommit=0

OPTION 2 : Perform one of these in the open DB Conenction before beginning any meaningful SQL

SET autocommit = 0;
START TRANSACTION;

Under these two options, you would have to perform a manual COMMIT or a manual ROLLBACK.

CAVEAT

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.

share|improve this answer
    
+1 - a simple but comprehensive answer! –  Jack Douglas Aug 3 '11 at 9:24
    
For additional clarification, my answer addresses both InnoDB and MyISAM storage engines. –  RolandoMySQLDBA Aug 3 '11 at 15:12
    
Just in case Auto Commit is OFF in InnoDB and my application is firing Insert Queries, and I forget to execute the Commit, how soon are changes lost? –  RPK Aug 4 '11 at 9:53
1  
Regarding "If you use START TRANSACTION, all uncommited changes are committed." (in the context of DDLs, otherwise it would be rolled back), there's also an implicit commit before (the implicit commit after is from version 5.5.3 according to the documentation). –  Bruno Aug 4 '11 at 13:45
1  
"If you use START TRANSACTION, all uncommited changes are committed." - I got that idea from MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7) Page 418 which names START TRANSACTION, SET AUTOCOMMIT=1,LOCK TABLES,UNLOCK TABLES,TRUNCATE TABLE,RENAME TABLE, DROP INDEX,DROP TABLE,DROP DATABASE,CREATE INDEX,BEGIN, and ALTER TABLE under the headings "Under some circumstances, the current transaction may end implicitly: If you issue any of the following statements, InnoDB implicitly commits the preceding uncommitted statements of the current transaction and begins a new transaction". –  RolandoMySQLDBA Aug 4 '11 at 14:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.