MySQL事务与锁

定义一个事务向d_eams数据库的student表中插入3条记录，并检验若插入相同的学号，则回滚事务，既插入无效，否则成功提交

delimiter $$
create procedure tr_proc()
begindeclare continue handler for sqlstate '23000'beginrollback;end;#开始事务start transaction;insert into student values('201007001', '张文静', '女', '1991-2-1', '汉族', '共青团员');insert into student values('201007002', '刘海燕', '女', '1991-10-18', '汉族', '共青团员');insert into student values('201007003', '宋志强', '男', '1991-05-23', '汉族', '中共党员');#提交事务commit;
end$$
delimiter ;

验证结果为：

call tr_proc();
select * from student
where 学号='201007001';

定义一个事务向d_eams 数据库的sc表中插入记录，并设置保存点

use d_eams;
delimiter $$
create procedure tr_save_point()
begindeclare continue handler for 1026beginrollback to apoint;rollback;end;start transaction;insert into sc values('201007001','07001','89');savepoint apoint;insert into sc values('201007018','07005','67');commit;
end$$
delimiter ;

验证结果

call tr_save_point();
select * from sc;

储存事务的隔离级

select @@transaction_isolation;

查看表级锁定的信息

show status like 'table%';

查看行级锁定的信息

show status like 'innodb_row_lock%';

定义一个学生选课的事务

规定每人选课不能超过三门，向d_eams数据库的sc表中插入多条记录，并检验若插入相同的学号大于3，则回滚事务，即插入无效，否则提交成功

use d_eams;
delimiter $$create procedure tr_course(in xh char(9))
begindeclare num int;declare continue handler for 1062beginrollback;end;start transaction;insert into sc(学号, 课程号) values('201007001','07001');insert into sc(学号, 课程号) values('201007007','07005');insert into sc(学号, 课程号) values('201007002','07003');insert into sc(学号, 课程号) values('201007001','07004');insert into sc(学号, 课程号) values('201007004','07005');insert into sc(学号, 课程号) values('201007013','07005');select count(*) into num from sc where 学号 = xh;if num > 3 thenset @ch1 = '您选的课程数已超过3门,操作无效!';rollback;elsecommit;set @ch1 = '您选的课程数符合要求,操作成功!';end if;
end$$delimiter ;

验证结果

call tr_course('201007001');

select @ch1;