innodb 03 锁详述

快来打我* 2023-01-05 01:27 219阅读 0赞

# 锁 #

### 1、SQL语言分类 ###

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。  
数据查询语言DQL:  
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：

数据操纵语言DML：  
数据操纵语言DML主要有三种形式。(insert update delete)

数据定义语言DDL：  
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：CREATE TABLE/VIEW/INDEX/SYN/CLUSTER  
定义语言是隐式提交的，不能rollback

数据控制语言DCL：  
数据控制语言DCL用来授予或回收访问数据库的某种特权。 COMMIT \[WORK\]：提交。GRANT：授权。

### 2、事务提交方式 ###

a：显示提交；  
使用begin或者start transaction来显式开启一个事务，显式开启的事务必须使用commit或者rollback显式提交或回滚。几种特殊的情况除外：行版本隔离级别下的更新冲突和死锁会自动回滚。  
b：自动提交  
DML和DDL语句都会在执行语句前自动开启一个事务，执行语句结束后自动提交或回滚事务。  
c：隐式提交  
DDL 以及ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

auto\_commit 变量值为1或0来设置是否自动提交，为1表示自动提交，0表示关闭自动提交，即必须显式提交。  
但是不管设置为0还是1，显式开启的事务必须显式提交，而且隐式提交的事务不受任何人为控制。

### 3、lock与latch ###

latch一般称为闩锁（轻量级的锁），因为其要求锁定的时间必须非常短。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。  
lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放。lock，正如在大多数数据库中一样，是有死锁机制的。

### 3、锁的类型 ###

1.共享锁(S)：即读锁，不涉及修改数据，在检索数据时才申请的锁。  
2.独占锁(X)：增、删、改等涉及修改操作的时候，都会申请独占锁。  
以上是支持表锁的存储引擎都会有的锁类型。以下两种是支持行锁或页锁才会有的锁类型，也就是说myisam没有下面的锁，而innodb有。  
3.意向共享锁(IS)：获取低级别共享锁的同时，在高级别上也获取特殊的共享锁，这种特殊的共享锁是意向共享锁。  
4.意向独占锁(IX)：获取低级别独占锁的同时，在高级别上也获取特殊的独占锁，这种特殊的独占锁是意向独占锁。

低级别锁表示的是行锁或页锁，意向锁可能是多条记录组成的范围锁，也可能直接就是表意向锁。

### 4、一致性非锁定读 和 一致性锁定读 ###

一致性的非锁定读：如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB存储引擎会去读取行的一个快照数据。  
实现是通过undo的MVCC机制。  
一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制

在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。  
而在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

一致性锁定读：事务的隔离级别为REPEATABLE READ模式下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。  
在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作。  
❑SELECT…FOR UPDATE  
❑SELECT…LOCK IN SHARE MODE  
SELECT…FOR UPDATE，SELECT…LOCK IN SHARE MODE必须在一个事务中，当事务提交了，锁也就释放了

### 5、自增锁 ###

很多DBA或开发人员首选的主键方式。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器

自增的时候，是会上锁的：  
这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。  
从MySQL 5.1.22版本开始，InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。

innodb会根据插入语句的不同分类(是否在插入之前能够知道插入的行数)，选择是选用互斥量还是表锁，来进行插入。

### 6、外键和锁 ###

在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，因为这样可以避免表锁  
于外键值的插入或更新，首先需要查询父表中的记录，即SELECT父表。  
SELECT父表的时候，因此这时使用的是SELECT…LOCK IN SHARE MODE方式，即主动对父表加一个S锁。  
为什么不使用一致性非锁定读，因为会发生数据不一致的问题。(如果父表正在删除这一行。)

### 7、锁的三种算法 ###

Record Lock：单个行记录上的锁(锁定索引记录，如果没有显式指定索引，就用隐式索引。)  
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身  
Next-Key Lock∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身(结合了Gap Lock和Record Lock的一种锁定算法)

innodb对行的锁申请默认都是这种算法。如果有索引，则只锁定指定范围内的索引键值，如果没有索引，则自动创建索引并对整个表进行范围锁定。  
为什么会升级成表锁？实际上是伪表锁，它吧可能的区间都给锁定了。

当查询的索引含有唯一属性时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。从而提高应用的并发性。

如果是 id<值。锁定的是-无穷 —值之后的一个范围  
如果是 值<id。锁定的是值之前的一个范围— +无穷  
如果是 值=id。锁定的是 id之后的范围+ id之前的范围。

### 8、Phantom Problem问题。幻像问题。不可重复读。 ###

Phantom Problem是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。  
可以看到Gap Lock的作用是为了阻止多个事务将记录插入到同一范围内。  
多个事务将记录插入到同一范围内会导致幻象的产生。

### 9、锁问题。 ###

脏读。 即一个事务可以读到另外一个事务中未提交的数据。  
不可重复读。在第一个事务中的两次读数据之间，由于第二个事务的修改提交，那么第一个事务两次读到的数据可能是不一样的。(使用Next-Key Lock算法来避免不可重复读的问题。即幻像问题)  
丢失更新。事务A开启事务–>修改所有学生当天签到状况为false，此时切换到事务B，事务B开启事务–>事务B插入了一条学生数据，此时切换回事务A，事务A提交的时候发现了一条自己没有修改过的数据，这就是幻读，就好像发生了幻觉一样。幻读出现的前提是并发的事务中有事务发生了插入、删除操作。  
读未提交：脏读 + 不可重复读 + 幻读 + 丢失更新  
读提交： 不可重复读 + 幻读 + 丢失更新  
可重复读：幻读 + 丢失更新(innodb通过Next-Key Lock解决了幻读问题)  
串行化： 丢失更新

innodb唯一会出现的问题：丢失更新  
如果多个线程操作，基于同一个查询结果对表中的记录进行修改，那么后修改的记录将会覆盖前面修改的记录，前面的修改丢失掉了，这就叫丢失更新。  
丢失更新分为两类。第一类丢失更新：回滚丢失；第二类丢失更新：覆盖丢失。SQL92没有定义这种现象，标准定义的所有隔离级别都不允许第一类丢失更新发生。  
解决丢失更新的办法就是加锁。

悲观锁机制  
假定这样的问题是高概率的，最好一开始就锁住，免得更新老出错。

添加共享锁：select \* from account lock in share mode;  
添加排他锁：select \* from account for update;  
乐观锁机制  
假定这样的问题是小概率的，最后一步做更新的时候再锁住，免得锁住时间太长影响其他人操作。

在表中增加timestamp类型字段，在执行插入或更新时记录最新时间到该字段上；  
在修改数据时检查timestamp类型的字段是否改变判断当前的更新基于的查询是否已经过时。  
在用户并发数比较少且冲突比较严重的应用系统建议选择悲观锁中的排他锁，其他情况首选乐观锁。

### 10、阻塞 ###

不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。  
参数innodb\_lock\_wait\_timeout用来控制等待的时间（默认是50秒），innodb\_rollback\_on\_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是OFF，代表不回滚）。  
默认情况下InnoDB存储引擎不会回滚超时引发的错误异常。其实InnoDB存储引擎在大部分情况下都不会对异常进行回滚。这是十分危险的状态，因此用户必须判断是否需要COMMIT还是ROLLBACK，之后再进行下一步的操作。

### 11、死锁 ###

11.1死锁问题解决方案1：两个事务AB互相等待的时候，其中一个事务A超时回滚。
        缺点：回滚A的成本可能很高，回滚B的成本很低。
    11.2等待图死锁检测：深度优先算法实现，非递归方式。数据库中保留：锁的信息链表 + 事务等待链表
    在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。