Bug #70981 Implement optional SQL_MODE that makes queries triggering implicit commit fail

• Submitted: 22 Nov 2013 12:56
• Reporter: - -
• Status: Open
• Category: MySQL Server: Locking
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[22 Nov 2013 12:56] - -

Description:
I suggest implementing optional SQL_MODE that makes queries triggering implicit commit fail. 

As far as I know in MySQL there is no way to test whether you are in transaction or not. 

Let's assume there is code that does some function including query that triggers implicit commit. This code itself, separately, may be totally ok, it may do exactly what it is intended for. 

Now let's assume you start calling this code as a black box from another code sharing database connection. This is quite possible in systems with a lot of business logic. 

If caller starts a transaction before calling, you are doomed. This transaction gets implicitly committed and you have no way to know about it. If you do a DML query before or after calling, you expect it to be transactional and rollbackable, but it is not. Your data is spoiled. 

If database is able to notify you about implicit commit at least callee will be able to fail and caller will be able to rollback everything without spoiling the data. 

Optionally this or one more SQL_MODE may make ROLLBACK or COMMIT outside of transaction fail as well.

How to repeat:
mysql> CREATE TABLE test(id INT);
Query OK, 0 rows affected (0,02 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT test VALUES(1);
Query OK, 1 row affected (0,00 sec)

mysql> TRUNCATE test;
Query OK, 0 rows affected (0,07 sec)

mysql> INSERT test VALUES(2);
Query OK, 1 row affected (0,00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT * FROM test;
+------+
| id   |
+------+
|    2 | 
+------+
1 row in set (0,00 sec)

Suggested fix:
mysql> CREATE TABLE test(id INT);
Query OK, 0 rows affected (0,02 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT test VALUES(1);
Query OK, 1 row affected (0,00 sec)

mysql> TRUNCATE test;
Error

mysql> INSERT test VALUES(2);
Query OK, 1 row affected (0,00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT * FROM test;
Empty set (0,00 sec)