【Mysql】事务

事务

 

常用场景: 转账

insert into user(name, balance)

valus

(‘wsb’, 1000),

(‘egon’, 1000),

(‘ysb’, 1000);

 

# 开始操作

START TRANSATION;  # 事务标志

update user set balance=900 where name=’wsb’;

update user set balance=1010 where name=’egon’;

update user set balance=1090 where name=’ysb’;

COMMIT;

 

# 异常操作

start transaction;

update user set balance=900 where name=’wsb’;

update user set balance=1010 where name=’egon’;

update user set balance=1090 where name=’ysb’;

ROLLBACK;  # 未commit之前可以随时rollback

commit;

 

# 实现

delimiter //

create PROCEDURE p5(

       OUT p_return_code TINYINT

)

BEGIN

       DECLARE exit handler for sqlexception

       BEGIN

              -- ERROR

              SET p_return_code = 1;

              rollback;

       END;

      

       START TRANSACTION;

              DELETE FROM tb1;

              INSERT INTO blog(name, sub_time) values(‘yyy’, now());

       COMMIT;

 

       -- SUCCESS

       SET p_return_code = 0;

END //

DELIMITER ;