MySQL Bugs: #28353: begin does not cause implicit commit

Bug #28353	begin does not cause implicit commit
Submitted:	10 May 2007 14:27	Modified:	10 May 2007 16:39
Reporter:	Mattia Merzi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In this page:
http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html
"Statements That Cause an Implicit Commit"
why is "BEGIN" considered as a statement that cause
implicit commit ?

START TRANSACTION;
BEGIN
  INSERT INTO BLA,BLA,BLA;
END;
ROLLBACK;

...and transaction is correctly rolled-back! 

How to repeat:
go there and have a look with your own eyes ! :)

The START TRANSACTION and BEGIN statement begin a new transaction. *They are synonyms.* Both statements also cause an implicit commit.
BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for initiating a transaction. START TRANSACTION  is standard SQL syntax and is the recommended way to start an ad-hoc transaction.
http://dev.mysql.com/doc/refman/5.1/en/commit.html

this is the test I did, where am I wrong ?

DROP TABLE IF EXISTS TEST_TBL;
DROP PROCEDURE IF EXISTS TEST_PROC;

CREATE TABLE TEST_TBL (X INT,	Y INT);

CREATE PROCEDURE TEST_PROC ()
LANGUAGE SQL
BEGIN
	START TRANSACTION;
	INSERT INTO TEST_TBL(X,Y) VALUES (1111,1111);
	BEGIN /* here there should be a commit, if BEGIN implicitly does a commit */
		INSERT INTO TEST_TBL(X,Y) VALUES (9999,9999);
	END;
	ROLLBACK;
	SELECT X,Y FROM TEST_TBL;
END;

CALL TEST_PROC(); /* no records ... */

Thank you for the additional comment.

You use stored procedure. Please, read about BEGIN ... END Compound Statement Syntax in stored procedures at http://dev.mysql.com/doc/refman/5.0/en/begin-end.html

http://dev.mysql.com/doc/refman/5.1/en/commit.html
says:

The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN ... END compound statement. The latter does not begin a transaction. See Section 18.2.5, “BEGIN ... END Compound Statement Syntax”.

I will add a note about that to the implicit-commit section as well.