文章目录
- 一、innodb核心特性事务
- 1、什么是事务
- 2、如何使用事务
- 3、事务演示
- 4、事务通俗理解
- 5、一个成功事务的生命周期
- 6、一个失败事务的生命周期
- 7、事务的特性 ACID
- 8、事务的控制语句
- 1）自动提交
- 2）事务的隐式提交
- 3）控制语句
- 二 、MySQL事务的3种运行模式
- 1、自动提交事务（隐式开启、隐式提交）
- 2、隐式事务\\（隐式开启、显式提交）
- 3、显式事务（ 显式开启、显式提交）
- 4、 事务保存点
- 三、 事务的使用原则
- 四、数据库读现象
- 1、四种隔离级别
- Mysql默认使用的数据隔离级别是REPEATABLE READ ，可重复读，允许幻读。
- 2、事务并发常见问题实例演示 --- 数据库读现象
- 2.1脏读
- 1）修改自动提交事务开关
- 2）修改数据库的事务隔离级别
- 3）终端1查询用户指定信息（此处不提交事务）
- 4）终端2查询用户信息
- 2.2不可重复读
- 1）修改事务隔离级别
- 2）窗口1开启事务，查询指定用户数据
- 3）窗口2同时执行事务
- 2.3 幻读
- 2）窗口1
- 3）窗口2：此处，事务A中查询指定id为5的记录，没有查询到结果后，事务B进行插入了一条id为5的记录并且提交事务；
- 3、修改隔离级别
- 4、解决方案
- 五、事务中的锁
- 1.什么是锁？锁的作用
- 2.示例
- 3.锁的种类分类
- 4.锁的粒度分类
- 1) 表级锁 （偏向于读）
- 2) 行级锁
- 3) 页级锁
- 5.锁的类别分类
- 1、锁的使用
- 6、多版本并发控制（MVCC）
- 7、事务的隔离级别
- 8.脏读，幻读，不可重复读
- 六、事务的日志
- 1.redo log
- 2.undo log
- 1）作用
- 七、 innodb存储引擎的锁机制
- 1、行级锁有三种算法：
- 2、总结
- 3、死锁
- 1、结论
- 2、有多种方法可以避免死锁
- 4 什么时候使用表锁（了解）
- 5 行锁优化建议
- 八、数据库总结
一、innodb核心特性事务
1、什么是事务
# 什么是事务
顾名思义就是要做的事情，事务就相当于一个盛放sql的容器
事务中的sql要么全部执行成功，要么所有已经修改的操作都回滚到原来的状态，即一条sql也别想成功
开启一个事务可以包含多条sql语句，这样sql语句要么全部成功，或者全部失败，成为事务的原子性
# 作用
保证了数据操作的安全性，一致性 2、如何使用事务
# 事务相关的关键字
# 1、开启事务
start transaction
# 2、回滚（回到事务执行之前的状态）
rollback
# 3、确认（确认之后就无法回滚了）
commit
# 总结：
当你想让sql语句同时保证数据的一致性，要么同时成功，要么同时失败，那么就可以考虑使用事务 3、事务演示
#1.创建一个表
mysql> create table jiaoyi(id int primary key auto_increment,name varchar(10),money int);
#2.插入两条数据
mysql> insert into jiaoyi values(1,'qiudao',100);
Query OK, 1 row affected (0.01 sec)
mysql> insert into jiaoyi values(2,'oldboy',200);
Query OK, 1 row affected (0.00 sec)
#3.再打开一个窗口
mysql> select from jiaoyi;
数据与原窗口查看结果一致
#4.开启一个事务（符合逻辑）
mysql> start transaction； #开启事务
# begin 也是开启事务
mysql> begin;
mysql> update jiaoyi set money=0 where name='qiudao';
mysql> select from jiaoyi;
mysql> update jiaoyi set money=400 where name='oldboy';
mysql> select from jiaoyi;
#在执行commit之前，另一个窗口是看不到数据变化的
mysql> commit;
#执行commit之后其他人都能看到数据的变化
#5.开启一个事务（不符合逻辑）
mysql> start transaction；
mysql> update jiaoyi set money=-100 where name='qiudao';
mysql> select from jiaoyi;
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | qiudao | -100 |
| 2 | oldboy | 400 |
+------+--------+-------+
2 rows in set (0.00 sec)
# 6、由于钱的值不能为负数，由逻辑判断，操作失败，回滚
mysql> rollback; #回滚事务
mysql> select from jiaoyi;
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | qiudao | 0 |
| 2 | oldboy | 400 |
+------+--------+-------+
2 rows in set (0.00 sec) 4、事务通俗理解
#伴随着“交易”出现的数据库概念。
我们理解的“交易”是什么？
1）物与物的交换（古代）
2）货币现金与实物的交换（现代1）
3）虚拟货币与实物的交换（现代2）
4）虚拟货币与虚拟实物交换（现代3）
数据库中的“交易”是什么？
1）事务又是如何保证“交易”的“和谐”？
2）ACID 5、一个成功事务的生命周期
start transaction； #begin 开启事务
sql1 #真正事务处理的时候，是第一条sql语句执行完，才是开启事务的
sql2
sql3
...
commit;
# 若用了begin手动开始编辑事务，编辑完后只要commit手动提交，另一端则能查询到数据 6、一个失败事务的生命周期
start transaction；
sql1
sql2
sql3
...
rollback;
# 若用了begin手动开始编辑事务，编辑完后且只要不commit手动提交，另一端则查询不到数据 7、事务的特性 ACID
ACID 四大特性 #ACID
# A: 原子性 (Atomic)
一个事务是一个不可分割的单位，事务中包含的诸多操作，要么同时失败，要么同时成功
所有语句作为一个单元全部成功执行或全部取消。
# C:一致性 （Consistent）
事务必须是是数据库一致性的状态变成另外一个一致性的状态，一致性根原子性密切相关的,
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
# I：隔离性 (Isolated)
一个事务的执行不能被其他事务干扰,事务之间不相互影响。
# D：持久性 (Durable)
也可以称为永久性，一个事务一旦提交成功执行成功，那么它就是对数据库中的数据修改是永久的
接下来的其他操作或者故障不应该对其任何的影响
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
我们可以分析一下，事务的四大特征中，所有的操作都会走向磁盘，所以持久性是事务操作的目的，而原子性是实现事务的基础，隔离性是实现数据安全的一种策略、手段，而最终维护的，就是数据的一致性，一致性才是事务中最重要的。四大特征之间，隔离性是为了达到一致性的手段。
ACID四大特征中，最难理解的不是一致性，而是事务的隔离性，数据库权威专家针对事务的隔离性研究出来了事务的隔离四种级别，四种事务隔离级别就是为了解决数据在高并发下产生的问题（脏读、不可重复读、幻读）。 8、事务的控制语句
1）自动提交
# 0.手动提交
mysql> commit；
#1.查看自动提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)
#2.临时关闭
set autocommit =0;
#3.永久关闭
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
autocommit=0 2）事务的隐式提交
1）现在版本在开启事务时，不需要手工start transaction；，只要你输入的是DML语句，就会自动开启事务。
2）有些情况下事务会被隐式提交
# 隐式提交触发条件
1.执行事务没有commit时，如果使用了DDL或者DCL会自动提交上一条事务
2.执行事务没有commit时，如果你手动执行begin，会自动提交上一条事务
3.执行事务没有commit时，如果执行锁表（lock tables）或者解锁（unlock tables），会自动提交上一条事务
4.load data infile（导数据）会自动提交上一条事务
5.select for update
6.在autocommit=1的时候，会自动提交上一条事务
update ....
commit;
insert into ....
begin;
update ....
create table ....
delete .... 3）控制语句
begin（或 start transaction）：显式开始一个新事务，推荐begin #开启事务
savepoint：分配事务过程中的一个位置，以供将来引用 #临时存档
commit：永久记录当前事务所做的更改 #提交事务
rollback：取消当前事务所做的更改 #回滚
roolback to savepoint：取消在 savepoint 之后执行的更改 #回到存档点
release savepoint：删除 savepoint 标识符 #删除临时存档
set autocommit：为当前连接禁用或启用默认 autocommit 模式 #临时开关自动提交
PS：永久开启或关闭autocommit，则在配置文件（my.cnf）插入一行：
autocommit=1 # 开启状态
autocommit=0 # 关闭状态 二 、MySQL事务的3种运行模式
隐式 == 自动
显式 == 手动
1、自动提交事务（隐式开启、隐式提交）
此乃mysql默认的事务运行模式
mysql默认为每条sql开启事务，并且会在本条sql执行完毕后自动执行commit提交 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
# 注意，重要的事说三遍
这种方式在当你使用commit或者rollback后，事务就结束了
再次进入事务状态需要再次start transaction
# 注意，重要的事说三遍
这种方式在当你使用commit或者rollback后，事务就结束了
再次进入事务状态需要再次start transaction 无论事务是显式开启还是隐式开启，事务会在某些情况下被隐式提交
# 隐式提交触发条件
1.执行事务没有commit时，如果使用了DDL或者DCL会自动提交上一条事务
2.执行事务没有commit时，如果你手动执行begin，会自动提交上一条事务
3.执行事务没有commit时，如果执行锁表（lock tables）或者解锁（unlock tables），会自动提交上一条事务
4.load data infile（导数据）会自动提交上一条事务
5.select for update 加锁
6.在autocommit=1的时候，会自动提交上一条事务
# 案例：
try:
# begin;
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
execpt 异常；
rollback；
else:
commit; 4、 事务保存点
savepoint和虚拟机中的快照类似，用于事务中，没设置一个savepoint就是一个保存点，当事务结束时会自动删除定义的所有保存点，在事务没有结束前可以回退到任意保存点
1、设置保存点savepoint 保存点名字
2、回滚到某个保存点，该保存点之后的操作无效，rollback 某个保存点名
3、取消全部事务，删除所有保存点rollback
# 注意：rollback和commit都会结束掉事务，这之后无法再回退到某个保存点 最后总结一下事务的控制语句
START TRANSACTION（或 BEGIN）：显式开始一个新事务 #开启事务
SAVEPOINT：分配事务过程中的一个位置，以供将来引用 #临时存档
COMMIT：永久记录当前事务所做的更改 #提交
ROLLBACK：取消当前事务所做的更改 #回滚
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改 #回到存档点
RELEASE SAVEPOINT：删除 savepoint 标识符 #删除临时存档
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式 三、 事务的使用原则
# 1. 保持事务短小
# 2. 尽量避免事务中rollback
# 3. 尽量避免savepoint
# 4. 显式声明打开事务
# 5. 默认情况下，依赖于悲观锁，为吞吐量要求苛刻的事务考虑乐观锁
# 6. 锁的行越少越好，锁的时间越短越好 四、数据库读现象
# 读现象 --》 在并发场景下，数据不安全的一种体现
# 读现象 --》 在并发场景下，并发的多个事务操作同一份数据，而产生的一些奇怪的独现象
前面讲到了事务的隔离性，如果要提升系统的吞吐量，当有多个任务需要处理时，应当让多个事务同时执行，这就是事务的并发。既然事务存在并发执行，那必然产生同一个数据操作时的冲突问题，来看一下都会出现什么问题。
在高并发情况下，即多个并发的事务同时操作的一份数据，在没有加锁处理的情况下，会引发一些奇怪的读现象？
# 1.更新丢失
Lost Update，当两个事务更新同一行数据时，双方都不知道对方的存在，就有可能覆盖对方的修改。比如两个人同时编辑一个文档，最后一个改完的人总会覆盖掉前面那个人的改动。
# 2.脏读 --》原因 -- 设置安全级别低导致的
Dirty Reads，一个事务在执行时修改了某条数据，另一个事务正好也读取了这条数据，并基于这条数据做了其他操作，因为前一个事务还没提交，如果基于修改后的数据进一步处理，就会产生无法挽回的损失。
# 3.不可重复读
Non-Repeatable Reads，同样是两个事务在操作同一数据，如果在事务开始时读了某数据，这时候另一个事务修改了这条数据，等事务再去读这条数据的时候发现已经变了，这就是没办法重复读一条数据。
# 4.幻读
Phantom Read，与上方场景相同，事务一开始按某个查询条件没查出任何数据，结果因为另一个事务的影响，再去查时却查到了数据，这种就像产生幻觉了一样，被称作幻读。
# 1、当设置事务的安全隔离级别低的的时候，相应的安全级别低，处理效率就比较高
# 2、当设置事务的安全隔离级别高的的时候，相应的安全级别高，处理效率就比较低 1、四种隔离级别
首先，更新丢失这种问题应该是由应用层来解决的，因为数据库没有办法控制用户不去更新某条数据。但是另外三个问题是可以得到解决的，既然有方案解决解决它不就好了，干嘛还要设置这么多隔离级别呢？
刚才说了，如果我们要性能好、吞吐量提升，那就不得不付出一些代价，如果要做到完全没有副作用，那么就只需要让事务排队执行就好了，一个一个执行绝对不会出现脏读幻读的问题，但是这样会导致数据库处理的非常慢。那怎么办呢？官方唯一能做的就是给你提供各种级别的处理方式，由你根据具体业务场景选择，于是就有了隔离级别。
# 1、读未提交 Read uncommitted
读未提交其实就是事务没提交就可以读，很显然这种隔离级别会导致读到别的还没提交的数据，一旦基于读到的数据做了进一步处理，而另一个事务最终回滚了操作，那么数据就会错乱，而且很难追踪。总的来说说，读未提交级别会导致脏读。
# 2、读提交 Read committed
顾名思义就是事务提交后才能读，假设你拿着银行卡去消费，付钱之前你看到卡里有2000元，这个时候你老婆在淘宝购物，赶在你前面完成了支付，这个时候你再支付的时候就提示余额不足，但是分明你看到卡里的钱是够的啊。
这就是两个事务在执行时，事务A一开始读取了卡里有2000元，这个时候事务B把卡里的钱花完了，事务A最终再确认余额的时候发现卡里已经没有钱了。很显然，读提交能解决脏读问题，但是解决不了不可重复读。
Sql Server，Oracle的默认隔离级别是Read committed。
# 3、可重复读 Repeatable read
看名字就看出来了，它的出现就是为了解决不可重复读问题，事务A一旦开始执行，无论事务B怎么改数据，事务A永远读到的就是它刚开始读的值。那么问题就来了，假设事务B把id为1的数据改成了2，事务A并不知道id发生了变化，当事务A新增数据的时候却发现为2的id已经存在了，这就是幻读。
MySQL的默认隔离级别就是Repeatable read。
# 4、串行化 serializable
这个就是最无敌的存在了，所有的事务串起来一个个执行，因为没有并发的场景出现了，什么幻读、脏读、不可重复读统统都不存在的。但是同样的，基本并发能力会非常差。最终，到底什么隔离级别完全要根据自己的业务场景选择，没有最好的，只有最适合的。
事务隔离级别越严格，越消耗计算机性能，效率也越低，通常情况下，设置为允许不可重复读就可以解决大多数的问题了。 
Mysql默认使用的数据隔离级别是REPEATABLE READ ，可重复读，允许幻读。
2、事务并发常见问题实例演示 — 数据库读现象
2.1脏读
脏读：比如有两个事务并行执行操作同一条数据库记录，A事务能读取到B事务未提交的数据。 1）修改自动提交事务开关
# 临时有效
mysql数据库事务开关
开启自动提交事务：set autocommit = 1;
关闭自动提交事务：set autocommit = 0 ;
查看事务开关：show variables like '%autocommit%'; 2）修改数据库的事务隔离级别
# 全局的 ---此处演示我们设置全局的
mysql> set global transaction isolation level read uncommitted;
mysql> show variables like '%iso%';
+-----------------------+------------------+
| Variable_name | Value |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
| tx_isolation | READ-UNCOMMITTED |
# 退出重新进入
# 当前会话
set session transaction isolation level read uncommitted; 3）终端1查询用户指定信息（此处不提交事务）
# 1、关闭自动提交事务
set autocommit = 0;
# 2、查询指定用户
select from jiaoyi where id=1;
# 3、修改指定用户余额
update jiaoyi set money = 500; 4）终端2查询用户信息
---------- 窗口B ------------------------------------------
# 1、关闭自动提交事务
set autocommit = 0;
# 2、查询指定用户
select from jiaoyi where id=1;
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | qiudao | 500 |
+------+--------+-------+
1 row in set (0.00 sec)
很显然，事务A修改了数据后还没有提交事务，此时事务B可以读取到事务A没有提交的事务的数据。这就是脏读，脏读情况下的数据是不可取的，所以一般没有数据库事务级别设置为允许脏读。
# 总结：脏读就是指事务A读取到事务B修改但未提交事务的数据。
(事务B未执行commit的时候，但是事务A却读取到了)，硬盘的数据和读取的数据不一样 2.2不可重复读
学习完脏读后，我们再来看看什么是不可重复读。比如事务A在同一事务中多次读取同一记录，此时事务B修改了事务A正在读的数据并且提交了事务，但是事务A读取到了事务B所提交的数据，导致两次读取数据不一致。
# （每次读取的数据不一样，读一次数据，数据每次不一样） 1）修改事务隔离级别
# 全局的 ---此处演示我们设置全局的
set global transaction isolation level read committed;
# 当前会话
set session transaction isolation level read committed; 2）窗口1开启事务，查询指定用户数据
---------- 窗口A ------------------------------------------
# 1、关闭自动提交事务
set autocommit = 0;
# 2、查询指定用户
select from jiaoyi where id = 1;
此时窗口2执行事务
# 1、查询指定用户
select from jiaoyi where id = 1;
# 2、提交事务
commit; 3）窗口2同时执行事务
窗口1先是开启事务，查询指定用户信息，然后窗口2开启事务，查询数据指定用户，修改数据，提交事务，然后再回到窗口1，查询指定用户信息；窗口2操作内容如下
# 1、关闭自动提交事务
set autocommit = 0;
# 2、查询指定用户
select from jiaoyi where id = 1;
# 3、修改指定用户余额
update money set money = 300 where id=1;
# 4、提交事务
commit;
事务A在两次查询中，查询的数据不一样，这就是不可重复读。Mysql默认采用的就是不可重复读的隔离级别，用一句话总结，不可重复读就是事务A读取到事务B已提交事务的数据，导致两次读取数据信息不一致。 2.3 幻读
上面我我们学习了一下什么不可重复读，在mysql数据库中，不可重复读是不被允许的。
# 全局的 ---此处演示我们设置全局的
set global transaction isolation level repeatable read;
# 当前会话
set session transaction isolation level repeatable read; 2）窗口1
# 1、关闭自动提交事务
set autocommit = 0;
select from jiaoyi where id = 5;
# 此时窗口1未查询到id为5的数据，正准备进行插入时，窗口2插入了一条id为5的数据。
# 2、没有查询到结果，进行插入
insert into jiaoyi values (5, 'cm',200);
# 3、再查询id为5的
select from money where id = 5;
commit; 3）窗口2：此处，事务A中查询指定id为5的记录，没有查询到结果后，事务B进行插入了一条id为5的记录并且提交事务；
# 1、关闭自动提交事务
set autocommit = 0;
# 2、插入一条记录
insert into jiaoyi values (5,'cm',200);
# 3、提交事务
commit;
在上述事务A中，不提交事务的情况下，插入id为5的记录会一直报错主键冲突，但是再怎么查询id为5的记录都查询不到；这是因为在MySql的设计中，事务中查询的是被修改前的日志。即Undo log。 3、修改隔离级别
#查看隔离级别
mysql> show variables like '%iso%';
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)
#配置隔离级别
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation=read-uncommit
4、解决方案
其实，脏写、脏读、不可重复读、幻读，都是因为业务系统会多线程并发执行，每个线程可能都会开启一个事务，每个事务都会执行增删改查操作。然后数据库会并发执行多个事务，多个事务可能会并发地对缓存页里的同一批数据进行增删改查操作，于是这个并发增删改查同一批数据的问题，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。
所以这些问题的本质，都是数据库的多事务并发问题，那么为了解决多事务并发带来的脏读、不可重复读、幻读等读等问题，数据库才设计了锁机制、事务隔离机制、MVCC 多版本隔离机制，用一整套机制来解决多事务并发问题，下面我们来分别介绍一下它们 五、事务中的锁
1.什么是锁？锁的作用
锁
官方链接： javascript:void(0)
# 什么是锁
顾名思义就是锁定的意思，修改数据时锁住数据，锁是一种保障数据的机制，如何保障?
# 为什么用锁？
以互斥锁为例,让多个并发的任务同一时间只有一个运行（注意这不是串行），牺牲了效率但换来数据安全
在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用，并发写同一份数据的时候安全，保证数据安全
# 锁的优缺点
优点：保障并发场景下的数据安全
缺点：降低了效率
# 所以我们在使用锁时尽可能缩小锁的范围，即锁住的数据越少越好，并发能力越高 2.示例
数据 id=1
事务1 set id=2
事务2 set id=3
#1.创建一个表
create table test(id int);
#2.插入数据
insert into test values(1);
#3.开启两个窗口开启事务
begin;
update test set id=2 where id=1;
begin;
update test set id=3 where id=1; 3.锁的种类分类
# 1、按锁的粒度划分，可分为行级锁、表级锁、页级锁。（mysql支持）
# 2、按锁级别划分，可分为共享锁、排他锁
# 3、按使用方式划分，可分为乐观锁、悲观锁
# 4、按加锁方式划分，可分为自动锁、显式锁
# 5、按操作划分，可分为DML锁、DDL锁 4.锁的粒度分类
1) 表级锁 （偏向于读）
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
age int(3) unsigned not null default 20
);
insert into emp(name) values
('egon'),
('alex'),
('wupeiqi'),
('yuanhao'),
('liwenzhou'),
('jingliyang'),
('jinxin'),
('成龙'),
('歪歪'),
('丫丫'),
('丁丁'),
('星星'),
('格格'),
('张野'),
('程咬金'),
('程咬银'),
('程咬铜'),
('程咬铁')
;
update emp set age = 18 where id <=3; # 表级锁 --> 支持引擎：MyISAM、MEMORY、InNoDB
# 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
lock table 表名 read(write),表名 read(write)，.....;
//给表加读锁或者写锁，例如
mysql> lock table emp write;
Query OK, 0 rows affected (0.00 sec)
mysql> show open tables where in_use>= 1;
+----------+----------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+----------+--------+-------------+
| ttt | emp | 1 | 0 |
+----------+----------+--------+-------------+
1 row in set (0.00 sec)
mysql> unlock tables; -- UNLOCK TABLES释放被当前会话持有的任何锁
Query OK, 0 rows affected (0.00 sec)
mysql> show open tables where in_use>= 1;
Empty set (0.00 sec)
mysql> 2) 行级锁
# 行级锁 -->支持引擎：InnoDB
行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
# 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
行级锁定分为行共享读锁（共享锁）与行独占写锁（排他锁）
# 共享锁（S）：SELECT FROM table_name WHERE ... LOCK IN SHARE MODE
# 排他锁（X)：SELECT FROM table_name WHERE ... FOR UPDATE
# 排它锁======== 等于========互斥锁 3) 页级锁
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁
# 特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。 5.锁的类别分类
1、锁的使用
事务一对id=3的行加了互斥锁之后，其它事务对id=3行不能加任何锁（写不行，但是可以读）
事务一对id=3的行加了共享锁之后，其它事务对id=3行只能加共享锁，或者不加锁（写不行，但可以读） 排他锁：保证在多事务操作时，数据的一致性。(在我修改数据时，其他人不得修改) # X 排查锁
# 特点：
加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通select查询没有任何锁机制。
共享锁：保证在多事务工作期间，数据查询时不会被阻塞。 # S 共享锁
# 特点：
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁或不加锁（在其他事务里一定不能再加排他锁，但是在事务T自己里面是可以加的），反之亦然。 行级锁分为共享锁和排他锁两种。
与行处理相关的sql有：insert、update、delete、select，这四类sql在操作记录行时，可以为行加上锁，但需要知道的是：
# 1、对于insert、update、delete语句，InnoDB会自动给涉及的数据加锁，而且是排他锁（X）；
# 2、对于普通的select语句，InnoDB不会加任何锁，需要我们手动自己加，可以加两种类型的锁.
# 共享锁（S）：SELECT ... LOCK IN SHARE MODE; -- 查出的记录行都会被锁住
# 排他锁（X)：SELECT ... FOR UPDATE; -- 查出的记录行都会被锁住
# 验证insert、update、delete是默认加排他锁的
# 共享锁一般用于读数据
# 互斥锁一般用于改数据 案例
# 链接 javascript:void(0)
实验一：事务二获取了排他锁，在事务一中验证上述特例
实验二：事务二获取了共享锁，在其他事务里也只能加共享锁或不加锁，在事务二中验证
实验三：事务二获取了共享锁，在其他事务里也只能加共享锁或不加锁，反之亦然，并验证在多个事务加了共享锁后，大家对加锁的数据行只能读不能写
=================================================================================================
解决脏读 ---》 加一个排他锁即可，别人就改不了了
解决不可重复读 ---》 加一个共享锁，别人只能加共享锁，或者不加速
解决幻读 ---》 加一个排他锁和间隙锁
# 修改的范围数据 id >3 id<1 别人正好插入了一个id=2，所以就造成id=2就没有改 6、多版本并发控制（MVCC）
乐观锁：多事务操作时，数据可以被同时修改，谁先提交，谁修改成功。
悲观锁：多事务操作时，数据只有一个人可以修改。
# 1）只阻塞修改类操作（排它锁），不阻塞查询类操作（共享锁）
# 2）乐观锁的机制（谁先提交谁为准）
在乐观锁与悲观锁的选择上面，主要看下两者的区别以及适用场景就可以了。
# 在MVCC并发控制中，读操作可以分成两类：
快照读 (snapshot read)与当前读 (current read)。
# 1、乐观锁并未真正加锁，效率高。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。
# 2、悲观锁依赖数据库锁，效率低。更新失败的概率比较低。
随着互联网三高架构（高并发、高性能、高可用）的提出，悲观锁已经越来越少的被使用到生产环境中了，尤其是并发量比较大的业务场景。 7、事务的隔离级别
1）四种隔离级别：
1.RC: read committed 允许事务查看其他事务所进行的已提交更改
2.RU: read uncommitted（独立提交），未提交读，允许事务查看其他事务所进行的未提交更改；
3.RR: repeatable read 可重复读 InnoDB 的默认级别 #commit提交以后可能看不到数据变化，必须重新连接
4.serializable：串行化:，将一个事务的结果与其他事务完全隔离 #如果一个事务没有提交，查询也不能查了
# 我改微信头像时你不能看我的信息，我看你朋友圈的时候你不能发朋友圈、不能看朋友圈
2）修改隔离级别
#查看隔离级别
mysql> show variables like '%iso%';
#修改隔离级别为RU
[mysqld]
transaction_isolation=read-uncommit
mysql> use oldboy
mysql> select from stu;
mysql> insert into stu(id,name,sex,money) values(2,'li4','f',123);
#修改隔离级别为RC
[mysqld]
transaction_isolation=read-commit
=======================================================================================
# 1、加锁
mysql> lock table t1 read ;
# 2、解锁
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec) 8.脏读，幻读，不可重复读
1.脏读：（RU级别会发生）
一个事务执行了，去没有提交，被其他人读取到了值，可是事务回滚了，那刚被独到的数据被称为脏数据
2.幻读：（RR级别可以解决这个问题） # 可理解为：操作端的当前状态
事务将数据库中所有数据都删除的时候，但是事务B就在这个时候新插入了一条记录，当事务A删除结束后发现还有一条数据，就好像发生了幻觉一样。这就叫幻读。
# 说白了，就是当前用户的当前数据还没刷新到最新，或者刷新了一部分，且有残留没刷新完全，导致数据的不一致
3.不可重复读： # 被操作端的当前状态
执行一个事务读取两次数据，在第一次读取某一条数据后，有一个事务修改了读到的数据并提交，第一个事务再次读取该数据，两次读取便得到了不同的结果。
# 说白了，就是当前用户的当前数据还没刷新到最新，导致数据的不一致
六、事务的日志
1.redo log
redo，顾名思义“重做日志”，是事务日志的一种。 # 1）作用
在事务ACID过程中，实现的是“ D ”持久化的作用。
REDO：记录的是，内存数据页的变化过程
特性:WAL(Write Ahead Log)日志优先写
# 2）REDO工作过程
执行步骤：
update t1 set num=2 where num=1;
1）首先将t1表中num=1的行所在数据页加载到内存中buffer page
2）MySQL实例在内存中将num=1的数据页改成num=2
3）num=1变成num=2的变化过程会记录到，redo内存区域，也就是redo buffer page中
提交事务执行步骤：
commit;
1）当敲下commit命令的瞬间，MySQL会将redo buffer page写入磁盘区域redo log
2）当写入成功之后，commit返回ok 2.undo log
1）作用
undo，顾名思义“回滚日志”，是事务日志的一种。
在事务ACID过程中，实现的是“A”原子性的作用。当然CI的特性也和undo有关
# PS：
undo buffer cache 会实时的将数据写入磁盘，也就是 是否执行了 commit
事务id：txid
日志版本号：lsn 物理备份时可以看到 七、 innodb存储引擎的锁机制
innodb存储引擎的锁机制锁定的是索引树，因为所有的数据行都被索引树组织起来了
# 链接 javascript:void(0) # 1、命中索引则通过索引锁定行，简称为锁索引
命中中的是聚集索引，就通过聚集索引本身来锁定行即可
id为主键
select * from t1 where id = 3;
命中中的是辅助索引，会先锁定辅助索引，然后再对应聚集索引中的节点
name为辅助索引
select * from t1 where name="egon"; -- "egon":10
# 2、如果没有命中索引，会把所有的行都锁住，相当于表锁
强调：命中了索引才会锁行，不是说有索引就锁行
表:
1 xxxx
2 tom
3 egon
select * from t1 where id = 3 for update;
select * from t1 where name="egon for update"
强调：条件中引用的索引字段，也不一定会命中 1、行级锁有三种算法：
InnoDB有三种行锁的算法，都属于排他锁：
# 1、Record Lock：单个行记录上的锁。
# 2、Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
# 例如
例：假如employee表中只有101条记录，其depid的值分别是 1,2,...,100,101，下面的SQL：
mysql> select * from emp where depid > 100 for update;是一个范围条件的检索，并且命中了索引，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
复制代码
# 3、Next-Key Lock：等于Record Lock结合Gap Lock，也就说Next-Key Lock既锁定记录本身也锁定一个范围，特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock。
对于行查询，innodb采用的都是Next-Key Lock，主要目的是解决幻读的问题，以满足相关隔离级别以及恢复和复制的需要。
超时时间的参数：innodb_lock_wait_timeout ，默认是50秒。
超时是否回滚参数：innodb_rollback_on_timeout 默认是OFF。 record Lock：命中索引才锁行
Gap Lock：锁间隙
Next-Key Lock：锁定行以及间隙
如果命中的索引不是唯一索引，那么采用的算法是Next-Key Lock，即锁行又锁定间隙
16 18 20
锁定
(负无穷,16] (16,18] (18,20] (20,正无穷)
update employee set name=concat(name,"_SB") where age=18;
update employee set name=concat(name,"_SB") where age>3 and age < 19;
1，5，7，11
(负无穷,1] (1,5] (5,7] (7,11] (11,正无穷)
where 字段=7;
如果命中的是唯一索引
update employee set name=concat(name,"_SB") where id=5;
update employee set name=concat(name,"_SB") where id>3 and age < 10;
- 16] (16,18] (18,20] 2、总结
# 总结：
1、如果没有命中索引，无论你筛选出哪一行，都会将整张表锁住
1、如果命中了非唯一索引，并且是等值查询，会锁行还有间隙
1、如果命中了非唯一索引，但是是范围查询，会锁行还有间隙
1、如果命中了唯一索引，并且是等值查询，只会锁定行
1、如果命中了唯一索引，并且是范围查询，会锁行还有间隙
create table t1(id int primary key,name varchar(20))engine =innodb;
insert into t1 values
(1,"egon1"),
(3,"egon2"),
(5,"egon3"),
(8,"egon4"),
(11,"egon5"); 3、死锁
MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。 
# 案例一：
第一个非常好理解，也是最常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。
# 案例二：
只有多个事务同时运行的情况下才可能出现，但隐蔽性极强，虽然每个Session都只有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。 1、结论
# 1、关于死锁问题需要储备的知识
在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，
如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；
如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。
在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。
# 2、死锁产生的本质原理
死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。
而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，
就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。
死锁现象2：高并发场景下，核心原理是：命中了辅助索引，会先锁定辅助索引，再锁定聚集索引
2、有多种方法可以避免死锁
# 1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
# 2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
# 3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
# 4、在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。 4 什么时候使用表锁（了解）
1、事务需要更新大部分数据，表又较大
若使用默认的行锁,不仅该事务执行效率低(因为需要对较多行加锁,加锁是需要耗时的); 而且可能造成其他事务长时间锁等待和锁冲突; 这种情况下可以考虑使用表锁来提高该事务的执行速度
2、事务涉及多个表，较复杂，很可能引起死锁，造成大量事务回滚
这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ。 5 行锁优化建议
show status like 'innodb_row_lock%';//查看行锁的状态
# 1、尽可能让所有数据检索都通过索引来完成， 从而避免无索引行锁升级为表锁
# 2、合理设计索引，尽量缩小锁的范围
# 3、尽可能减少检索条件，避免间隙锁
# 4、尽量控制事务大小，减少锁定资源量和时间长度
# 5、尽可能低级别事务隔离 八、数据库总结
数据库索引
# 1、索引是什么？
索引一种数据数据结构，既结构数据，大白话说：索引就是一种组织数据的方式
# 那么索引到底如何组织数据的呢？
为表中的一条条记录创建建立索引就跟为书的一页页内容创建目录很类似，但不太一样
不一样的是，创建索引分为两步：
1、以索引字段为key与数据对应，例如
create index idx_id on t1(id); -- key为id
创建索引
key
记录1 <- 1
记录2 <- 2
记录3 <- 3
记录4 <- 4
记录5 <- 5
。。。
# 2、以key为基础构建B+树，那么什么是B+树？？？
介绍下列树的原理结构以及特点-》讲明白下列树是如何提速查询的，以及每种树的问题和演变升级过程
二叉树
平衡二叉树
B树
B+树
# 特点总结：
1、节点内放的key+value：二叉树、平衡二叉树、B树
在叶子节点放value，其余节点只放key值：B+树
2、放等量数据的前提下，B+树的高度最低，查询速度最高
# 2、索引分类及区别
hash索引
b+树
# 3、 B+树索引分类
聚簇索引：构建原理，只能有一个
典型特点：叶子节点放key：一整条完整记录
辅助索引：构建原理，可以有多个
典型特点：叶子节点放的是key:该记录对应的主键id
# 4、覆盖索引与回表操作
命中辅助索引是否一定需要回表，如果不，解释原因
# 5、联合索引，索引的最左前缀匹配原则-》举例说明
# 6、索引下推技术
# 7、举例说明，命中索引之后是否一定对加速有明显的提升------》等同于回答了如何正确使用索引!!!
范围过大
索引字段占用空间过多
索引字段的区分度低
索引字段参与运算
索引字段放入函数
explain命令简介
# 8、常识
单表300w条记录-》硬盘空间200m
uv：user view单日累计用户访问
2-5w
pv：page view单日累计页面被点击的次数
20w-50w
最大并发（同时在线人数最大多少）：一天内，某一时刻的并发量
1000人同时在线
数据库（读多写少）：
累计2w的UV，平均每人往数据库中写入一条数据，
那么单日新增数据条数为2w条
2w条数据库-》占用空间大概2M
# 结论：以2-5w uv为例，单日数据库空间增长量从几M到几十M不等
# 数据库事务
举例说明事务特性ACID
事务开启的三种方式
事务可以设置保存点
# 数据库读现象
脏读
不可重复
幻读
# 数据库锁
粒度：行级< 表级 < 页级
级别越高并发越低，考虑到性能，innodb默认支持行级锁，但是只有在命中索引的情况下才锁行，否则锁住所有行，本质还是行锁，
但是此刻相当于锁表了
行级锁有三种算法：
Record lock
Gap lock
Next-key lock（默认）= Record lock+ Gap lock ------》 解决幻读问题
级别：排他、共享
排他
共享
使用方式：悲观、乐观
# 事务隔离机制
解决：
脏读
不可重复
幻读
方案：
RR机制+innodb存储引擎的Next-key lock行级锁算法
# MVCC：解释原理
快照读
select 。。。
当前读
其他都是