第十二章:MySQL的事务
本节目标:
理解什么是事务?
了解事务必须满足的四个条件;
掌握事务处理的方法。
一、什么是事务?
事务由一个或多个SQL语句组成的一个不可分割的整体,SQL语句之间相互依赖,要么全都执行,要么全都不执行。
二、事务必须满足的四个条件:
Atomicity(原子性):一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
Consistency (—致性):在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
lsolation(隔离性):数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
Durability(持久性):事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。
三、控制事务处理:
- ROLLBACK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
- COMMIT:会提交事务,并使已对数据库进行的所有修改称为永久性的;
- SAVEPOINT identifier:允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT;
- ROLLBACK TO identifier:把事务回滚到标记点;
四、MYSQL事务处理主要有两种方法:
1、用BEGIN,ROLLBACK,COMMIT来实现
BEGIN或START TRANSACTION开始一个事务
ROLLBACK事务回滚
COMMIT事务确认
2、直接用SET来改变MySQL的自动提交模式:
SET AUTOCOMMIT = 0 禁止自动提交
SET AUTOCOMMIT = 1 开启自动提交
create table mytest( id int primary key, name varchar(20) ); -- 开始事务 begin; insert into mytest values(1,'test01'); insert into mytest values(2,'test02'); commit; select * from mytest; begin; insert into mytest values(3, 'test03'); rollback; select * from mytest; begin; insert into mytest values(4,'test04'); savepoint s1; insert into mytest values(5, 'test05'); savepoint s2; insert into mytest values(6, 'test06'); rollback to s2; commit; select * from mytest; -- 禁止事务的自动提交 set autocommit = 0; insert into mytest values(7, 'test07'); rollback; select * from mytest; set autocommit = 0; insert into mytest values(8, 'test08'); commit;
本节目标:通过案例理解事务的原理
一、通过InnoDB使用事务
InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID)。
MYSQL5.7中InnoDB作为默认存储引擎。所以事务处理正常处理就可以,但在MYSQL5.5.5之前MylSAM是默认存储引擎,当表需要事务处理时需设置存储引擎为InnoDB。
二、案例:
利用事务的原理,完成借阅图书的一些操作:
1、向借阅信息表插入借阅记录
2、更新图书信息表的库存
3、更新读者信息表的余额
要求把借阅的相关操作看做一个整体,要么都成功,要么都失败。
比如:当库存=0或余额<=200时都不可以借书。
delimiter // create procedure borrowproc(cid char(18), bid int) begin declare store_num int; declare money float(7,3); select store into store_num from bookinfo where book_id = bid; select balance into money from readerinfo where card_id = cid; set autocommit = 0; insert into borrowinfo values(bid,cid,curdate(), date_add(curdate(),interval 1 month),'否'); update bookinfo set store = store - 1 where book_id = bid; update readerinfo set balance = balance - (select price from bookinfo where book_id = bid)*0.05 where card_id = cid; if store_num = 0 or money<=200 then rollback; else commit; end if; end// delimiter ; select * from bookinfo; select * from borrowinfo; select * from readerinfo; call borrowproc('210210199901014444',20150202);