MySQL事务、数据库读现象、数据库锁机制、解决读现象

一、数据库事务

数据库事务详解

结合上小节：redo log 日志和binlog 日志 commit 提交事务，只要commit成功，事务执行成功，就不会回滚。commit失败，本条事务的所有操作一条都不会成功，会用undo log 日志回滚到原来的状态

什么是事务

事务(Transaction)，事务就相当于一个盛放sql的容器
事务中的sql要么全部执行成功，要么所有已经修改的操作都回滚到原来的状态，即一条sql也不会执行成功

为何要用事务

为了保证数据的安全，一致性

# 经典的银行转账例子
A账户转给B账户10元，数据库操作需要两步，第一步A账户减10元，第二步B账户加10元，如果没有事务并且在两步中间发生异常，就会导致A的账户少了10元，但B的账户没有变化，如果不能保证这两步操作统一，银行的转账业务也没法进行展开了。

事务的四大特性

这四个特性通常称为ACID特性，不是独立的，是相互影响的

# 1、原子性（Atomicity）
事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

# 2、一致性（Consistency）
事务应确保数据库的状态从一个一致状态转变为另一个一致状态，例如转账行为中，一个人减了50元，另外一个人就应该加上这50元，而不能是40元，保持事务的一致性。
其他一致状态的含义是数据库中的数据应满足完整性约束,例如字段约束不能为负数，事务执行完毕后的该字段也同样不是负数

# 3、隔离性（Isolation）
多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

# 4、持久性（Durability）
一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。只要commit提交成功，就无法回滚了

案例：验证事务的一致性

# 准备表：
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
age int(3) unsigned not null default 20
);

insert into employee(name) values
('egon'),
('alex'),
('wupeiqi'),
('yuanhao'),
('liwenzhou'),
('jingliyang'),
('jinxin'),
('歪歪'),
('丫丫'),
('丁丁')
;
update employee set age = 18 where id <=3;

#################################################################

# 实验
 步骤1
 #事务一    （begin;是开启事务的意思=start transaction;）
mysql> begin;
mysql> select * from employee where name="egon";
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | egon |  18 |
+----+------+-----+
1 row in set (0.00 sec)
#事务二
mysql> begin;
mysql> select * from employee where name="egon";
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | egon |  18 |
+----+------+-----+
1 row in set (0.00 sec)


步骤2
#事务一
mysql> update employee set age=age+1 where name="egon";
mysql> select * from employee where name="egon";
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | egon |  19 |
+----+------+-----+
1 row in set (0.00 sec)

 步骤3
#事务二(事务的隔离性看不见另一个事务的改动)
mysql> select * from employee where name="egon";
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | egon |  18 |
+----+------+-----+
1 row in set (0.00 sec)

#事务一（一旦commit事务提交成功就不能回滚）
mysql> commit;
#事务一（修改成功刷入硬盘）
mysql> select * from employee where name="egon";
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | egon |  19 |
+----+------+-----+
1 row in set (0.00 sec)


  步骤4
#事务二（#-- 虽然看到的age仍为18，但因为事务的一致性原则，其实此处的修改是在age=19的基础上进行的）
mysql> update employee set age=age+1 where name="egon";

#-- 查看到age变为20
mysql> select * from employee where name="egon";
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | egon |  20 |
+----+------+-----+
1 row in set (0.00 sec)

步骤5
##事务二 （只有commit提交事务才会刷入硬盘，否则会在redo buffer中存着）
mysql> commit;

事务的三种运行模式

隐式 == 自动

显式 == 手动

1、自动提交事务（隐式开启、隐式提交）

mysql默认的事务运行模式

mysql默认为每条sql开启事务，并且会在本条sql执行完毕后自动执行commit提交


# 隐式提交触发条件
1.执行事务没有commit时，如果使用了DDL或者DCL会自动提交上一条事务
2.执行事务没有commit时，如果你手动执行begin，会自动提交上一条事务
3.执行事务没有commit时，如果执行锁表（lock tables）或者解锁（unlock tables），会自动提交上一条事务
4.load data infile（导数据）会自动提交上一条事务
5.select for update 加锁
6.在autocommit=1的时候，会自动提交上一条事务

2、隐式事务（隐式开启、显式提交）

既然mysql默认是为每条sql都开启了事务并且在该sql运行完毕后会自动提交那么我只需要将自动提交关闭即可变成“隐式开启、显式提交”

#1.临时关闭
set autocommit =0;
show variables like 'autocommit';  -- 查看

#2.永久关闭
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
autocommit=0

3、显式事务（显式开启、显式提交）

手动开启的事务里默认不会自动提交所以我们可以将要执行的sql语句放在我们自己手动开启的事务里，如此便是显式开启、显式提交
start transaction;

update test.t1 set id=33 where name = "jack";

commit;


# 注意
这种方式在当你使用commit或者rollback后，事务就结束了
再次进入事务状态需要再次start transaction或者begin

案例

#隐式开启、显式提交
#事务一
mysql> set autocommit=0;

mysql> select * from employee;

mysql> update employee set age=38 where id=1;

mysql> select * from employee;

#回滚事务提交，回滚到原来的状态，事务结束
mysql> rollback;


#这时我在执行一下，就是事务二，这是隐式开启，隐式提交
mysql> select * from employee;

mysql> update employee set age=38 where id=1;

mysql> commit;



#默认开启隐式开启，隐式提交
ysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

事务保存点

savepoint和虚拟机中的快照类似，用于事务中，每设置一个savepoint就是一个保存点，当事务结束时会自动删除定义的所有保存点，在事务没有结束前可以回退到任意保存点，注意：一旦回滚到2，3就没了。

#开启事务
begin;
update employee set name="EGON_NB" where id=1;
update employee set name="ALEX_SB" where id=2;
update employee set name="WXX" where id=3;

#设置保存点1
savepoint one;
select * from employee;


update employee set name="yxx_sb" where id=4;
update employee set name="lxx" where id=5;

#设置保存点2
savepoint two;
select * from employee;
	
#插入数据设置保存点3
insert into employee values(19,"egonxxx",19);
savepoint three;


#回到保存点2，保存点3就没有了
mysql> rollback to two;

总结事务的控制语句

START TRANSACTION（或 BEGIN）：显式开始一个新事务                #开启事务
SAVEPOINT：分配事务过程中的一个位置，以供将来引用                  #临时存档
COMMIT：永久记录当前事务所做的更改                               #提交
ROLLBACK：取消当前事务所做的更改                                #回滚
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改          #回到存档点
RELEASE SAVEPOINT：删除 savepoint 标识符                       #删除临时存档
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式

事务的使用原则

保持事务短小
尽量避免事务中rollback
尽量避免savepoint
显式声明打开事务
默认情况下，依赖于悲观锁，为吞吐量要求苛刻的事务考虑乐观锁
锁的行越少越好，锁的时间越短越好

二、数据库读现象

数据库管理软件的“读现象”指的是当多个事务并发执行时，在读取数据方面可能碰到的问题，包括有脏读、不可重复读和幻读。

在并发场景下，数据不安全的一种体现
读现象—》在并发场景下，并发的多个事务去操作同一份数据，而产生的一些奇怪的读现象

脏读

在高并发情况下，即多个并发的事务同时操作一份数据，在没有加锁处理的情况下，会引发一些奇怪的读现象

开启事务一先读 age=18 刚刚读完这一刻开始，事务二把age改成了20，然后提交了commit持久化,这时数据在硬盘中是20。内存和硬盘数据不一致，这就叫脏数据，也叫脏读。事务一以为是新数据，其实读完那一刻就是历史数据了，这就是脏读现象。脏读是有先后顺序的，先读在修改的顺序

不可重复读

事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录并且提交了。然后T1又再次读取这行记录，发现与刚才读取的结果不同。这就称为“不可重复”读，因为T1原来读取的那行记录已经发生了变化。

在高并发情况下抢票，假如只有一张票一个人能查看到只有一张，另一个人还能去修改成零，这两个人同时成功了。那就有问题了，那查看的那个人是看的0还是1呢，所以不能让他们两个同时成功，那个人写的时候不让读，等写的那个人修改成功在让他读

幻读

幻读是不可重复读的一种特殊场景

比如将3和7之间的age改成18岁，执行commit之后看结果。我在执行这条sql语句的时候。又有个事务插了一条记录，id=5改成19，其实不知道我之前的commit修改了多少条，查看结果的时候发现id=5的没有改，向上翻找执行记录发现也执行成功了。就是在修改的时候，又有人插入记录，导致你出现幻读的现象

三、数据库锁机制

数据库锁详细链接

什么是锁

锁是一种保障数据安全的机制

为何要用锁

以互斥锁为例,让多个并发的任务同一时间只有一个运行（注意这不是串行），牺牲了效率但换来数据安全

锁的优缺点

优点: 保障并发场景下的数据安全
缺点：降低了效率
	
# 所以我们在使用锁时应该尽可能缩小锁的范围，即锁住的数据越少越好，并发能力越高

锁的分类

一、按锁的粒度划分：行级锁、表级锁、页级锁。（mysql支持）

二、按锁级别划分：共享锁、排他锁

三、按使用方式划分：乐观锁、悲观锁

行级锁

innodb存储引擎支持行级锁，锁的粒度是最精细的，表示只针对当前操作的行进行加锁

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
支持引擎：InnoDB
行级锁定分为行共享读锁（共享锁）与行独占写锁（排他锁）


共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

表级锁

锁住整张表，对表里的内容都没法修改，最粗狂的一种，并发度最低

页锁

锁定粒度界于表锁和行锁之间，并发度一般。

锁的使用

 #表级锁          
             事务一对表的write加锁：lock table employee write;
             事务一自己能改，自己能读
             事务二对该表不能改，也不能读
 #只有事务一unlock tables;释放锁事务二才能读和修改           
             事务一对表的read加锁：lock table employee read; 
             事务一自己能读，自己不能改
             事务二对该表能读，但不能改
 #可能做全备份的时候有的到read锁，锁住之后所有事务都能读，不能修改数据     
 
 #行级锁
 行级锁可以加共享锁，也可以加排它锁
 
1、对于insert、update、delete语句写操作，InnoDB会自动给涉及的数据加锁，而且是排他锁（X）；
2、对于普通的select语句，InnoDB不会加任何锁，需要我们手动自己加，可以加两种类型的锁
   
 共享锁(Share Lock) 
 享锁又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，获准共享锁的事务只能读数据，不能修改数据直到已释放所有共享锁，所以共享锁可以支持并发读。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁或不加锁（在其他事务里一定不能再加排他锁，但是在事务T自己里面是可以加的

# 事务一对id=3的行加了共享锁之后，其它事务对id=3行只能加共享锁，或者不加锁（写不行，但可以读）


# 案例：
事务一
mysql> begin;

事务二
mysql> begin;
#加上共享锁
mysql> select * from employee where id < 3 lock in share mode; 

事务一（加排它锁会阻塞到原地）
mysql> select * from employee where id = 1 for update;
#加入共享锁
mysql> select * from employee where id = 1 lock in share mode;

# 思考：两个事务手里都有排它锁，站在事务一的角度是：其他事务对事务一没有写权限，站在事务二的角度是：其他事务对事务二没有写权限，事务一和事务二都可以读，但是都不能写，这就侧重于读，这个锁对于锁表的锁，粒度更小。


事务二
mysql> update employee set id=111 where id=1;

事务一（不让加锁，在加就死锁了执行了之后，事务二显示成功）
mysql> update employee set id=222 where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction  # Deadlock 死锁

事务一出现死锁问题，就会释放锁，事务二就能执行成功，因为只有事务二有共享锁



排他锁（eXclusive Lock） 
排他锁又称为写锁，简称X锁，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再对该行加任何类型的其他他锁（共享锁和排他锁），但是获取排他锁的事务是可以对数据就行读取和修改。

# 事务一对id=3的行加了互斥锁之后，其它事务对id=3行不能加任何锁（写不行，但是可以读）

# 案例
事务一
mysql> begin;

事务二
mysql> begin;

事务一(排它锁)
mysql> update employee set name="ting" where id=1;

mysql> select * from employee;


事务二（能看到数据）
mysql> select * from employee;

事务二（不能修改因为排它锁在事务一手里，同时只能一个人改，牺牲效率保证数据的安全）
mysql> update employee set name = concat(name,"_NB") where id = 1;


事务一（提交任务，事务二就直接成功抢到排它锁）
mysql> commit;
事务一（看到的之前的脏数据，事务二的新数据没有提交）
mysql> select * from employee;

事务二
mysql> commit;

这时事务一事务二都能看到新数据

# 总结：写操作默认是加锁的，默认加的是排它锁

解决读现象

脏读:在并发情况下，一个事务从硬盘读取数据，将要使用的时候，另一个是事务修改了数据并提交到了硬盘，造成读取数据和硬盘数据不一致，这叫脏读
解决办法：加互斥锁 for update

不可重复读：在并发情况下，一个事务读取数据的时候，其他的事务在不停的修改那个读取事务的数据。之前的数据的事务发现每次读取的数据都不一样，这叫不可重复读
解决办法：加共享锁 lock in share mode

幻读：在并发情况下，一个事务要修改范围之间的数据：比如大于3小于10的5，7行数据，提交的完成后，发现6没有修改。其实已经修改成功了，是另外一个事务插入的数据
解决办法：排他锁锁定的是针对的那5,7两行记录，插入的行是6，解决不了
         共享锁也是过滤出来那几行，进行锁行，不能锁间隙。解决不了
         加间隙锁

小结

1 如何正确使用索引
2 什么是事务，事务的四大特性是什么，分别解释其核心意义
3事务的三种模式
4 举例解释数据库读现象
5 什么是锁，锁的作用是什么，优缺点是什么
6 锁的分类有哪些
7 如何为某几条记录添加排他锁，共享锁，哪些操作自带锁，带的是什么锁，哪些操作不带锁
8 互斥锁是用来限制什么的，共享锁是用来限制什么的