How to prevent mysql implicit commit

2
votes

The mysql documentation states that certain statements will cause an implicit commit during a transaction. For example:

CREATE TABLE foo (bar INT);
START TRANSACTION;
INSERT INTO foo VALUES (1);
CREATE TEMPORARY TABLE mumble like foo;
ALTER TABLE mumble modify bar INT UNSIGNED;
ROLLBACK;
SELECT * FROM foo;

after the rollback, I get a single row back from foo -- the documentation actually says that an alter table shouldn't cause an implicit commit if you use the temporary keyword, but ALTER TEMPORARY TABLE is not valid syntax, and dropping a temporary table doesn't cause an implicit commit, so I suspect there's just a bug (at least as of 5.5.29)

In any case, what I'd like to do is to tell mysql to never implicitly commit, but instead to fail / rollback if a command is given which would cause an implicit commit.

I suspect there is no way to do this, having looked around a fair bit, but I'm hoping I'm wrong. Hopefully someone on here knows :)

2
mysqltransactions

2 Answers

1
votes

Another hacky approach that I just tried successfully is to get the create table DDL via the mysql specific command

SHOW CREATE TABLE `tableName`

Then make some regexp magic and craft a new DDL query that will create a temporary table based on the original table, with all the alter table changes incorporated into the create table.

In my PHP based project, I did the following to add a unique index to a temporary table. It did the trick and no more implicit commit occurred midst the transaction.

$createDDL = ... get from SHOW CREATE TABLE `tableName`
$nr = 0;
$createDDL = preg_replace("/CREATE TABLE `$tableName` \(/", "CREATE TEMPORARY TABLE `$tmpName` (\nUNIQUE `ukey-1` ($uniqCols),", $createDDL, -1, $nr);
if (!$nr)
  throw new Exception("CREATE TABLE replacement error. No reps made.");
mysqli_query($con, $createDDL);

EDIT By the way here are some bug (feature) reports (since many years). In the first you can see a response (dating to 2006) that states: since this behavior is the same as in oracle db, this is the consistent one...

Maybe a feature request "spamming campaign" should be initiated for this feature request to revive!

0
votes

At least with MySQL 5.5.19, you are right. The documentation claims ALTER TEMPORARY TABLE will not do an explicit commit, but MySQL chokes on the TEMPORARY keyword.

I can think of two possible workarounds:

Workaround 1:

Use a new temporary table in place of the ALTER:

CREATE TABLE foo (bar INT);
START TRANSACTION;
INSERT INTO foo VALUES (1);
CREATE TEMPORARY TABLE mumble LIKE foo;
CREATE TEMPORARY TABLE mumble2 (bar INT UNSIGNED) SELECT * FROM mumble;
ROLLBACK;
SELECT * FROM foo;

The last statement produces the following output:

Empty set (0.00 sec)

Unfortunately, there is no way to rename mumble2 back to mumble, as RENAME is not allowed for temporary tables, and ALTER mumble2 RENAME mumble commits implicitly.

Workaround 2:

Don't use temporary tables, but instead spawn a subprocess to run the commands that would cause the implicit commit:

Process 1:

CREATE TABLE foo (bar INT);
START TRANSACTION;
INSERT INTO foo VALUES (1);

Process 2 (and using a different connection to MySQL):

CREATE TABLE mumble LIKE foo;
ALTER TABLE mumble modify bar INT UNSIGNED;

Process 1:

ROLLBACK;
SELECT * FROM foo;
DROP TABLE IF EXISTS mumble;