Implicit COMMIT considered harmful.

If you execute the following, what does your RDBMS do?

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;

The answer may surprise you.

18 thoughts on “Implicit COMMIT considered harmful.

  1. 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?

  2. 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.”

  3. 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.”

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

  5. 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.”

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

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

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

  9. 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
    */

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

  11. 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 :)

  12. @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.

Leave a Reply

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