学习笔记(50):mySQL数据库开发教程-在存储过程中使用事务

立即学习:https://edu.csdn.net/course/play/4364/77197?utm_source=blogtoedu

表级锁，对于不支持事务的引擎，为了实现隔离手动添加表级锁；

对于支持事务的引擎上的表，会根据操作自动加锁。

create table ta
(
n char(1) not null primary key,
i int
) engine=innodb ---------指定数据库引擎，因为MYISAM不支持事务

insert into ta values('a', 10), ('b', 20)

create table tb
(
n char(1) not null primary key,
i int
) engine=innodb

insert into tb values('a', 10), ('b', 20), ('c', 30), ('d', 40)

# 
create procedure move(nn char(1))
begin
declare t_error int default 0;
declare continue handler for SQLEXCEPTION set t_error=1;
start transaction;
insert into ta select * from tb where n==nn;
delete from tb where n=nn;
if t_error = 1 then 
    rollback;
    else
    commit;
end if;
end

call move('a')

# 转账b->a
create procedure btoa(nn char(1), m int)
begin
declare mm int;
start transaction;
update ta set i=i+m where n = nn;
update tb set i=i-m where n=nn;
select i into mm from tb where n=nn;
if mm<0 then 
    rollback;
    else
    commit;
end if;
end