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.
Ramblings
Ramblings which occasionally resemble reality. This is the blog of Stewart Smith.
wwdd = what would drizzle do?
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?
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.”
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.”
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.
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.”
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.
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.
Roland,
What about replacing START TRANSACTION with BEGIN? Oracle has BEGIN, right?
I guess it has really been far to long since I used Oracle.
I think BEGIN is probably only for declaring PL/SQL blocks.
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.
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
*/
oops ..
SHOW ENGINES
ENGINE Support
———————
PBXT NO
So the table was created as MyISAM.
OK .. got PBXT enabled. It behaves as InnoDB in this respect.
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.
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 :)
@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.
PostgreSQL gives you a warning, which makes complete sense. I’m following that model for Drizzle.