MySQL中的事务
1. 事务
1.1. 为什么需要事务
现在的很多软件都是多用户,多程序,多线程的,对同一个表可能同时有很多人在用,为保持数据的一致性,所以提出了事务的概念。
A 给B 要划钱,A 的账户-1000元, B 的账户就要+1000元,这两个update 语句必须作为一个整体来执行,不然A 扣钱了,B 没有加钱这种情况很难处理。
1.2. 什么存储引擎支持事务
1.查看数据库下面是否支持事务(InnoDB支持)?
show engines;
2.查看mysql当前默认的存储引擎?
show variables like '%storage_engine%';
3.查看某张表的存储引擎?
show create table 表名 ;
4.对于表的存储结构的修改?
建立InnoDB 表:Create table .... type=InnoDB; Alter table table_name type=InnoDB;
1.3. 事务特性
事务应该具有4个属性:原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
l 原子性(atomicity)
l 一致性(consistency)
l 隔离性(isolation)
l 持久性(durability)
1.3.1. 原子性(atomicity)
一个事务必须被视为一个不可分割的最小单元,整个事务中的所有操作要么全部提交成功,要么全部失败,对于一个事务来说,不可能只执行其中的一部分操作
老婆大人给Deer发生活费
1.老婆大人工资卡扣除500元
2.Deer工资卡增加500
整个事务要么全部成功,要么全部失败
1.3.2. 一致性(consistency)
一致性是指事务将数据库从一种一致性转换到另外一种一致性状态,在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏
老婆大人给Deer发生活费
1.老婆大人工资卡扣除500元
2.Deer工资卡增加500
2.Deer工资卡增加1000
扣除的钱(-500) 与增加的钱(500) 相加应该为0
1.3.3. 持久性(durability)
一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,已经提交的修改数据也不会丢失
并不是数据库的角度完全能解决
1.3.4. 隔离性(isolation)
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
(对数据库的并行执行,应该像串行执行一样)
l 未提交读(READ UNCOMMITED)脏读
l 已提交读 (READ COMMITED)不可重复读
l 可重复读(REPEATABLE READ)
l 可串行化(SERIALIZABLE)
mysql默认的事务隔离级别为repeatable-read
show variables like '%tx_isolation%';
1.3.4.1. 事务并发问题
l 脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
l 不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。
l 幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。
不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表
1.3.4.2. 未提交读(READ UNCOMMITED)脏读
show variables like '%tx_isolation%';
set SESSION TRANSACTION ISOLATION LEVEL read UNCOMMITTED;
一个session中
start TRANSACTION
update account set balance = balance -50 where id = 1
另外一个session中查询
select * from account
回到第一个session中 回滚事务
ROLLBACK
在第二个session中
select * from account
在另外一个session中读取到了为提交的数据,这部分的数据为脏数据
1.3.4.3. 已提交读 (READ COMMITED)不可重复读
show variables like '%tx_isolation%';
set SESSION TRANSACTION ISOLATION LEVEL read committed;
一个session中
start TRANSACTION
update account set balance = balance -50 where id = 1
另外一个session中查询 (数据并没改变)
select * from account
回到第一个session中 回滚事务
commit
在第二个session中
select * from account (数据已经改变)
1.3.4.4. 可重复读(REPEATABLE READ)
show variables like '%tx_isolation%';
set SESSION TRANSACTION ISOLATION LEVEL repeatable read;
一个session中
start TRANSACTION
update account set balance = balance -50 where id = 1
另外一个session中查询 (数据并没改变)
select * from account
回到第一个session中 回滚事务
commit
在第二个session中
select * from account (数据并未改变)
1.3.4.5. 可串行化(SERIALIZABLE)
show variables like '%tx_isolation%';
set SESSION TRANSACTION ISOLATION LEVEL serializable;
1.开启一个事务
begin
select * from account 发现3条记录
2.开启另外一个事务
begin
select * from account 发现3条记录 也是3条记录
insert into account VALUES(4,'deer',500) 发现根本就不让插入
3.回到第一个事务 commit
1.1.1.1. 间隙锁(gap锁)
其实在mysql中,可重复读已经解决了幻读问题,借助的就是间隙锁
实验1:
select @@tx_isolation;
create table t_lock_1 (a int primary key);
insert into t_lock_1 values(10),(11),(13),(20),(40);
begin
select * from t_lock_1 where a <= 13 for update;
在另外一个会话中
insert into t_lock_1 values(21) 成功
insert into t_lock_1 values(19) 阻塞
在rr隔离级别中者个会扫描到当前值(13)的下一个值(20),并把这些数据全部加锁
实验:2
create table t_lock_2 (a int primary key,b int, key (b));
insert into t_lock_2 values(1,1),(3,1),(5,3),(8,6),(10,8);
会话1
BEGIN
select * from t_lock_2 where b=3 for update;
1 3 5 8 10
1 1 3 6 8
会话2
select * from t_lock_2 where a = 5 lock in share mode; -- 不可执行,因为a=5上有一把记录锁
insert into t_lock_2 values(4, 2); -- 不可以执行,因为b=2在(1, 3]内
insert into t_lock_2 values(6, 5); -- 不可以执行,因为b=5在(3, 6)内
insert into t_lock_2 values(2, 0); -- 可以执行,(2, 0)均不在锁住的范围内
insert into t_lock_2 values(6, 7); -- 可以执行,(6, 7)均不在锁住的范围内
insert into t_lock_2 values(9, 6); -- 可以执行
insert into t_lock_2 values(7, 6); -- 不可以执行
二级索引锁住的范围是 (1, 3],(3, 6)
主键索引只锁住了a=5的这条记录 [5]
1.1. 事务语法
1.1.1. 开启事务
1、begin
2、START TRANSACTION(推荐)
3、begin work
1.1.2. 事务回滚
rollback
1.1.3. 事务提交
commit
1.1.4. 还原点
savepoint
show variables like '%autocommit%'; 自动提交事务是开启的
set autocommit=0; //关闭事务自动提交
insert into testdemo values(5,5,5);
savepoint s1;
insert into testdemo values(6,6,6);
savepoint s2;
insert into testdemo values(7,7,7);
savepoint s3;
select * from testdemo
rollback to savepoint s2
rollback