Execute Stored Procedure with Transaction inside MySQLWorkbench alway commit automatically?

Question

I have this STP:

CREATE DEFINER=`user1`@`%` PROCEDURE `test`(
    OUT result TINYINT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
        BEGIN
            SET result = -1;
            ROLLBACK;
        END;

    START TRANSACTION;
        INSERT INTO testtable (field1, field2) VALUES (11, 22);
        SET result = 1;
END

After I execute it inside MySQLWorkbench (6.3 64b), testtable have new record. I though data must be not commited because there is no commit statement.

Then I try to call that STP again by C# client, and this time, new data is not commited.

Please help me to explain this problem, I dont understand what is the different between calling STP inside Workbench and calling STP by another client.

Thanks.

Answer 1

The difference is that when you query the table again inside mysql workbench you are inside the same session. Even though your changes have not been committed, even though your changes are not committed you can still see them because the same client session is allowed to see uncommited changes.

If however you start up a new session of mysql workbench or the mysql shell you will not see the changes that you have made through your existing workbench session.