Simple mysql transaction with rollback

transaction-mysql.sql

drop table if exists test_table;

create table test_table (
    id   int default 0 not null primary key,
    name text          null
);


insert into test_table (id, name)
values (1, 'First');

select * from test_table;
# +--+-----+
# |id|name |
# +--+-----+
# |1 |First|
# +--+-----+

drop procedure if exists sp_my_transaction;
delimiter $$
create procedure sp_my_transaction()
begin
    declare continue handler for sqlexception
        begin
            rollback;
        end;

    start transaction;
    insert into test_table (id, name) values (2, 'Second');
    insert into test_table (id, name) values (3, 'Third');

    select * from test_table;
#     +--+------+
#     |id|name  |
#     +--+------+
#     |1 |First |
#     |2 |Second|
#     |3 |Third |
#     +--+------+


    insert into test_table (id, name) values (1, 'First'); # DUPLICATE ENTRY ERROR
    commit;
end $$
delimiter ;

call sp_my_transaction();

select * from test_table;
# +--+-----+
# |id|name |
# +--+-----+
# |1 |First|
# +--+-----+