Bug #6861 begin after begin calls commit
Submitted: 28 Nov 2004 15:03 Modified: 29 May 2005 10:59
Reporter: Sergey Frolovichev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:4.0.22-standard-log OS:Linux (SuSe 9.1)
Assigned to: CPU Architecture:Any

[28 Nov 2004 15:03] Sergey Frolovichev
Description:
I think this may be a feature but I didn't find any info on it using search.

I have a simple innodb table and AUTOCOMMIT=0
Now let's make a simple thing - just run begin;update row with changes;begin;
This will commit my update and I don't think this is correct, it will be better if I can choose in config - rollback or commit must be run after "begin after begin".

How to repeat:
# create a table
create table t(id int(10) unsigned not null default 0);
# set AUTOCOMMIT to off, if default is not true
set AUTOCOMMIT=0;
# start transaction
begin;
# insert record
insert into t (id) values (1);
# begin next transaction
begin;
[28 Nov 2004 15:08] Sergey Frolovichev
Table must be innodb of course:
-------------------
# create a table
create table t(id int(10) unsigned not null default 0) type=InnoDB;
# set AUTOCOMMIT to off, if default is not true
set AUTOCOMMIT=0;
# start transaction
begin;
# insert record
insert into t (id) values (1);
# begin next transaction
begin;
----------------
[28 Nov 2004 22:07] Heikki Tuuri
Hi!

http://dev.mysql.com/doc/mysql/en/InnoDB_implicit_command_or_rollback.html

A call of BEGIN causes the commit of the ongoing transaction. One can discuss if we should give an error in that case. Calling BEGIN in the middle of a transaction does not make sense.

Best regards,

Heikki
[29 May 2005 8:58] Sergey Frolovichev
I'd like to make an option so that begin after begin makes rollback - because in Oracle and other databases stack of transaction exists and in mysql - doesn't exists! SO I think this mode must be present (may be in my.cnf?)
[29 May 2005 10:58] Sergei Golubchik
Note that according to the SQL:2003,

    1) 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"