Bug #22857 add mode for stopping implicit commit
Submitted: 30 Sep 2006 9:45 Modified: 25 Feb 2011 23:00
Reporter: Maciej Pilichowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.24a OS:Linux (opensuse 10.0)
Assigned to: CPU Architecture:Any

[30 Sep 2006 9:45] Maciej Pilichowski
Description:
* Note: this is not about autocommit. *

Simply put -- it would be great to see something like this
FORBID_IMPLICIT_COMMIT = 1;

and then for example drop table would give me an error while done in transaction. It is not a problem that I can't rollback drop, it is a problem that I could by accident make a commit for previous commands.

So the main reason is -- safety first.

How to repeat:
This is a wish, not a bug.

Suggested fix:
This is a wish, not a bug.
[30 Sep 2006 12:14] Valeriy Kravchuk
Thank you for a feature request. What do you want to get when executing DROP TABLE having explicitely started transaction, in this your magic mode? Please, describe.

Personally I think that current implementation is simple and correct. Oracle works just in the same way. So, it is even "consistent"...
[30 Sep 2006 12:22] Maciej Pilichowski
Of course this is just en example:

FORBID_IMPLICIT_COMMIT = 1;

start transaction;
delete * from Books where id<1000; -- some query that changes data
drop table X;---> no commit, mysql gives error "you have transaction started"

Of course in such scenario there is no gain of it, but in longer it could protect user from making "silly" mistake.
[25 Feb 2011 23:00] Sveta Smirnova
Thank you for the reasonable feature request.
[18 Mar 21:42] Kurt Bo
Can we please revive this? I think it's a GREAT idea.

Errors and bad/inconsistent data due to Implicit commits are some of the MOST difficult code to track down and fix. Particularly because of call chains/nesting. If you simply forget to include the "temporary" keyword in your drop/create statement, boom, you've accidentally commited your transaction, and if anything after that command fails, it won't be rolled back as you expect.

We've encountered this issue enough times with painful effects from it that we have a monthly rotation amongst all the developers to do code scans for common code that would trigger implicit commits and trace them to make sure that an implicit commit shouldn't occur.

Despite being pretty disciplined, we still catch code from time to time that causes implicit commits. The time spent finding code that does NOT intend to perform commits is costly, as is fixing any data that is a victim of an implicit commit that couldn't be rolled back.

It seems like having the DB server OPTIONALLY perform this check for us wouldn't add much overhead and would provide incredible value.

Shooting this feature down because "It handles implicit commits the same way OracXX" does and "we think that's the right way" is the worst justification I've ever heard to dismiss a valuable feature.

If something provides value, is backward compatible, and is achievable; then why not progress the software further and provide the feature?