MySQL Bugs: #22857: add mode for stopping implicit commit

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

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.

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"...

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.

Thank you for the reasonable feature request.

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?