CREATE TABLE t1 (a int);
START TRANSACTION;
INSERT INTO t1 (a) VALUES (1);
START TRANSACTION;
INSERT INTO t1 (a) VALUES (2);
ROLLBACK;
SELECT * FROM t1;

18 thoughts on “Implicit COMMIT considered harmful.”

1. wwdd = what would drizzle do?

2. I would expect one row in the table. Are you suggesting the table should be empty? BEGIN/START TRANSACTION/SET AUTOCOMMIT will commit any outstanding work. Isn’t that the SQL standard?

3. Actually, in MySQL the result is exactly as I expect it: second start transaction implictly commits the pending one, final result is: we get just one row.

   However, it is not standard behaviour, at least iso/iec 9075-2: 2003, p886 reads:

   “If a statement is executed when an SQL-transaction is currently active, then
   an exception condition is raised: invalid transaction state — active SQL-transaction.”

4. mm, this blog doesn’t escape < and >….second attempt:

   “If a <start transaction statement> statement is executed when an SQL-transaction is currently active, then
   an exception condition is raised: invalid transaction state — active SQL-transaction.”

5. Interesting info Roland. As I recall, Oracle will have the same behavior as MySQL, but I don’t have an Oracle database around to test that.

6. Justin,

   Oracle doesn’t have START TRANSACTION (see: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_1001.htm#sthref2334)

   it does have SET TRANSACTION but docs don’t say what that means when there’s a pending transaction. If you try, you get:

   ERROR at line 1:
   ORA-01453: SET TRANSACTION must be first statement of transaction

   Maybe you were thinking about this:

   “Oracle Database implicitly commits the current transaction before and after executing a data definition language (DDL) statement.”

7. But if I replace it with this:
   CREATE TABLE t1 (a int);
   START TRANSACTION;
   INSERT INTO t1 (a) VALUES (1);
   SAVEPOINT a;
   INSERT INTO t1 (a) VALUES (2);
   ROLLBACK;
   SELECT * FROM t1;

   it works as expected.

8. For the record, I’ve changed Drizzle so that we do the same as PostgreSQL – warn that there was already a transaction open and NOT do an implicit commit.

9. Roland,

   What about replacing START TRANSACTION with BEGIN? Oracle has BEGIN, right?

10. I guess it has really been far to long since I used Oracle.

    I think BEGIN is probably only for declaring PL/SQL blocks.

11. Justin: that’s right, in Oracle BEGIN initiates a “PL/SQL block”. If it appears outside a stored routine declaration, it’s called an anonymous block, which is sent and executed as a whole. It still runs within the context of the current transaction though.

12. I think it is not quite correct that ‘*MySQL* does this’. This is handled by the ENGINE I believe – so it is rather *INNODB* that does.

    And BTW PBXT-Engine seems to get confused:

    CREATE TABLE t1 (a INT) ENGINE = PBXT;
    START TRANSACTION;
    INSERT INTO t1 (a) VALUES (1);
    START TRANSACTION;
    INSERT INTO t1 (a) VALUES (2);
    ROLLBACK;
    SELECT * FROM t1;

    /* returns

    a
    ——
    1
    2
    */

13. oops ..

    SHOW ENGINES

    ENGINE Support
    ———————
    PBXT NO

    So the table was created as MyISAM.

14. OK .. got PBXT enabled. It behaves as InnoDB in this respect.

15. For the curious, this is what DB2 does:

    Important:
    Issue the BEGIN WORK statement only if a transaction is not in progress. If you issue a BEGIN WORK statement while you are in a transaction, the database server returns an error.

16. Peter:

    I always suggest the following in the my.cnf for InnoDB users:

    set SQL_MODE=NO_ENGINE_SUBSTITUTION
    default_storage_engine=INNODB
    innodb=force

    That prevents that sinking feeling when you realize someone accidentally created a myisam file three months ago full of important data which is now trashed after a crash :)

17. @Peter: add a NO_ENGINE_SUBSTITUTION to your sql_mode, it’s one of the few sql_mode options that make sense in all practical cases.

    Concerning whether innodb or Mysql is responsible: interesting point. I would argue though that if there is a difference in behaviour, MySQL should actually make sure the BEGIN TRANSACTION statement is communicated down to the engines in such a way that the result is the same.

    @Justin: ok – thanks! so that’s standard behaviour then.

    I just tried sqlite:

    sqlite> begin transaction
    …> ;
    sqlite> begin transaction;
    SQL error: cannot start a transaction within a transaction
    sqlite>

    so MySQL’s behavior certainly seems to be the exception.

18. PostgreSQL gives you a warning, which makes complete sense. I’m following that model for Drizzle.

This site uses Akismet to reduce spam. Learn how your comment data is processed.