3.mysql事务特性(脏读，可重复读，幻读)

3.mysql事务

事务sql相关语法

-- 1.查看当前表的存储引擎
show create table te ;
show variables like '%storage_engine%';
-- 2.开启事务、事务回滚、事务提交、还原点
begin/rollback/commit/savepoint
show variables like '%autocommit%'
-- 关闭自动提交
set autocommit = 0;
insert into te values(3,3);
savepoint n1;
insert into te values(4,4);
savepoint n2;
insert into te values(5,5);
rollback to savepoint n1;

select * from te

关闭事务自动提交，插入3,set n1,插入4,set n2,插入5 查询三条已插入，执行rollback n1，查询有一条数据
插入

1) 特性ACID

1.原子性：一个事务是不可分割的工作单位，一批操作要么成功要么失败
例,a转b100元，a账号减100，b账号就增加100;
2.一致性：从一个一致性状态变到另一个一致性状态，
例,a转b100元，a账号减100，b账号就增加100,增加和扣减的符合，这就是一直性 
3.隔离性：一个事务执行不能被其它事务干扰，
4.持久性：一个事务一旦提交，它对数据库中的数据改变就应该是永久性
例,事务提交了，数据就会被持久到磁盘中

隔离性

1.未提交读(脏读) read uncommitted

事务a读取了事务b更新的数据，然后b回滚，那么a读取到就是脏数据

CREATE TABLE `te` (
  `id` int(11) NOT NULL,
  `score` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_bin
insert into te values(1,100);


原数据
id  score
1   100
事务a
-- 1. 设置未提交读
set session transaction isolation level read uncommitted;
-- 2.开启事务
begin
update te set score = score -1 where id = 1;
select * from te where id = 1;
-- 3.事务回滚
ROLLBACK

事务b
-- 1. 设置未提交读
set session transaction isolation level read uncommitted;
-- 2.开启事务
begin
-- 3.查询
select * from te where id = 1;

先执行a1 a2 然后执行b1 b2 执行a-update操作，然后执行b3，结果为99，然后a3回滚事务，b3读取的就是脏数据

2.已提提交读(不可重复读)-read commited

事务a多次读取同一条数据，事务b修改了数据并提交，导致事务a读取到的数据前后不一致

事务a
-- 1. 设置已提提交读(不可重复读)
set session transaction isolation level read committed;
-- 2.开启事务
begin
update te set score = score -1 where id = 1;
-- 3.查询
select * from te where id = 1;
-- 4.事务回滚
ROLLBACK

事务b
-- 1. 设置已提提交读(不可重复读)
set session transaction isolation level read committed;
-- 2.开启事务
begin
-- 3.查询
select * from te where id = 1;
commit

先执行a1 a2 然后执行b1 b2 执行a-update操作，执行a3为结果查询为99，而b3查询结果还是100
但是事务a提交了，b3查询则为99，这就是跟可重复读区别

3.可重复读-repeatable read

事务a开启，事务b开启，事务a修改后，提交事务，事务b查询时修改前的数据

事务a 
-- 1.设置已提提交读(不可重复读)
set session transaction isolation level repeatable read;
-- 2.开启事务
begin
update te set score = score -1 where id = 1;
-- 3.查询
select * from te where id = 1;
-- 4.事务回滚
ROLLBACK
commit

事务b
-- 1. 设置已提提交读(不可重复读)
set session transaction isolation level repeatable read;
-- 2.开启事务
begin;
-- 3.查询
select * from te where id = 1;
commit

先执行a1 a2 然后执行b1 b2,然后执行a-update，查询a3结果为99，查询b3结果为100，然后事务commit，
继续查询b3结果还是100，然后b3commit，重新执行b3，结果则变成99

4.可串行读-serializable

幻读:事务a开启，将表中所有数据修改成1，同时事务b插入一条值为2的数据，a就会发现自己并未完全修改好，
sql就不来演示了

总结

• 1.事务隔离级别为可重复读，如果有索引的时候，已索引为条件更新数据，会存在行锁、页锁、间隙锁的问题，如果没有索引，更新数据时会锁住整张表(行锁升级为表锁);
• 2.隔离级别越高，越能保证事务的一致性，并发性能就较低，根据自己应用，合理选择事务的隔离级别;
• 3.不可重复读侧重于修改，幻读侧重于新增和删除，解决不可重复读锁行，而幻读则要锁表;
• 4.事务级别为串行化时，读写数据则会锁住整张表;

题

(1)mysql 什么情况下行锁会升级为表锁

如果我们where条件对索引列进行上锁，不会出现;如果我们where条件对非索引列进行上锁，则会出现表锁变成行锁

CREATE TABLE `course` (
  `id` varchar(35) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `my_like` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `real_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `reg_date` date DEFAULT NULL,
  `score` int(255) DEFAULT NULL,
  `reg_Valid` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

BEGIN
select * from course where name = '张三1' for update
-- 上述只针对name='张三1'进行行锁，但是会发现执行下面sql会出现等待，说明已经升级为表锁
update course set name = 'summit' where id = 5