Description:
Add a SERVER level config option such as:
FORBID_IMPLICIT_COMMIT = 1;
that when enabled, prevents MySQL from performing an implicit commit inside a declared transaction.
If auto commit is disabled, and a statement that would cause an implicit commit is run, instead of executing the implicit commit, first check if FORBID_IMPLICIT_COMMIT is enabled, and if it is, do a ROLLBACK and fail instead of doing the commit.
i.e.
START TRANSACTION;
select 1;
update mytable set x = 1;
drop table if exists othertable;
/* many more insert/update commands */
COMMIT;
( the "drop table" command never executes, TRANSACTION rollback is triggered )
( SQLException is raised and does NOT wait for the rollback to apply before returning the errors to the client )
The other statements that should have run after the "Drop table" command but before the COMMIT command are never run
Errors and bad/inconsistent data due to Implicit commits are difficult 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 committed 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 do code scans for common code that would trigger implicit commits a few times a year 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, preserves backward compatibility, and would provide incredible value.
How to repeat:
CREATE TABLE IF NOT EXISTS mytable123456(
ID INT(11)
);
CREATE TABLE IF NOT EXISTS mytable654321(
ID INT(11)
);
CREATE TABLE IF NOT EXISTS mytable555555(
ID INT(11)
);
START TRANSACTION;
SELECT 1;
insert into mytable123456(ID) values(10);
DROP TABLE IF EXISTS mytable654321; /* MySQL should throw an error and rollback before executing this statement */
insert into mytable555555(ID) values(10);
update mytable123456 SET ID = ID + 10;
COMMIT;
/*
DROP TABLE IF EXISTS mytable123456;
DROP TABLE IF EXISTS mytable654321;
DROP TABLE IF EXISTS mytable555555;
*/
Suggested fix:
Add a SERVER level config option such as:
FORBID_IMPLICIT_COMMIT = 1;
That causes MySQL to throw an error and rollback before an implicit commit occurs
