Mysql 锁

参考书籍《mysql是怎样运行的》
推荐极客时间《mysql实战45讲》

一丶为什么数据库需要锁

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

二丶全局锁&全库逻辑备份

全局锁就是对整个数据库实例加锁。全局锁的典型使用场景是，做全库逻辑备份，全库逻辑备份有以下几种方式：

1.Flush tables with read lock (FTWRL)

Flush tables with read lock (FTWRL)可以让整个库处于只读状态的时候，使用这个命令，之后其他线程的数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句讲被阻塞。之所以备份需要加全局锁是为了保证数据的一致性，

2.mysqldump –single-transaction

使用此命令会先启动一个事务拿到一致性视图。由于 MVCC 的支持，这个过程中其他线程可以进行正常操作，但是使用mysqldump –single-transaction的前提是需要支持事务，如果存在MyISAM引擎的表，并不能保证一致性。

3.set global readonly=true

set global readonly=true也可以保证全库只读，主要有两个原因：

在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大。
在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

三丶锁解决并发事务带来的问题

1.锁解决脏写

多个未提交事务修改相继对同一条记录进行改动的时候，需要进行排队，排队过程其实就是通过为记录加锁实现的。当一个事务想修改记录的时候，首先需要看下有没有于记录相关联的锁结构，如果没有，那么会在内存中生成与之对应的锁结构

可能多个事务同时修改同一记录，会产生多个锁结构，其中只有一个事务可以获取到锁，其is_waiting为false，其他事务is_waiting为true，当获取锁的事务结束后，会唤醒其他等待的事务

2.脏读，幻读，不可重复读如何避免

mysql innodb 在repeatable隔离级别下很大程度下避免了幻读（后续详细说到）

2.1 mvcc解决脏读，幻读，不可重复读问题

Mysql InnoDB多版本并发控制MVCC 这篇博客我们详细说到了mvcc的实现的原理，简单来说就是在事务运行时会生成read view，其包含四个部分

m_ids:在生成read view时，当前系统中活跃的读写事务id列表
min_trx_id:生成read view时，当前系统中活跃的读写事务中最小事务id，也就是m_ids中的最小值
max_trx_id:生成read view时，系统应该分配给下一个事务的事务id值
creator_trx_id:生成该read view的事务的事务id

查询语句只可以查询生成read view 时刻可以看到的数据，及事务id小于min_trx_id中的数据，写操作则针对最新的数据。非当前读的普通语句在读数据的时候，生成read view的时机不同，在可重复读的时候只会在第一次读取（如果使用start transaction with consistent snapshot则是在事务启动时）生成一个read view 后续不变，达到可重复读的目的，对于读已提交，则是每次读取都会生成新的read view，从刷新未提交事务集合，和min_trx_id，读取到已经提交的数据。使用mvcc让读写并不冲突，数据库的并发能力更强。

2.2读写均加锁

比如在银行存款业务中，我们需要先读取账户余额，然后加上新增存款，然后进行写回操作，整个流程中，我们不希望存在另外一个ATM进行存取的操作，读写都需要排队，这时候就得使用锁。

上面我们说了，写操作进行排队可以解决脏写，那么脏读，不可重复读，幻读是怎么使用读写加锁解决昵？

脏读产生的原因是，当前事务读取到了另外一个事务没有提交的数据，那么只需要另外一个事务对操作的记录加锁，当前事务无法获取到锁，自然就不会发生脏读。

不可重复读产生的原因是，当前事务先读取了一条记录，然后存在另外一个事务修改了此记录的数据，那么只要当前事务对记录进行加锁，自然后续的事务将无法修改，自然不会发生不可重复读。

幻读产生的原因是，当前事务根据条件查询得到一批数据，然后后续事务新增了满足这些条件的数据，导致再次查询时发现多了一些数据，如同出现了幻觉。这里加锁则不是单对记录加锁，而是要锁住一个范围，让其他事务无法插入数据，从而解决幻读（后续会说到这种锁）

很明显读写都加锁，并发能力不及mvcc

3.一致性读

事务利用MVCC进行读取操作称为一致性读（又称一致性无锁读，快照读）,基本上所有的普通读在可重复读，读提交隔离级别下，都是一致性读。

4.锁定读

4.1共享锁&独占锁

共享锁：简称S锁，事务要读取一条记录的时候需要先获取到记录的共享锁

独占锁：简称x锁，当事务需要改动记录的时候，需要先获取记录的独占锁

共享锁和共享锁兼容，独占锁和独占锁，独占锁和共享锁互斥

4.2锁定读的语句

select xxx lock in share mode可以对读取的记录加S锁

select xxx for update可以对读取的记录加X锁

4.3 写操作

delete

删除记录首先要在b+数中定位到这条记录的位置，然后获取x锁，然后指向delete mark（标记记录被删除）
update

如果未修改键值，并且修改前后数据的存储空间大小不变，那么现在b+树上定位记录，然后加x锁。反之需要在b+树上定位数据，然后把记录彻底删除吗，然后再插入一条新的记录，对新增的这条数据加x锁
insert

新插入记录一般都是加隐式锁（后面说）不需要在内存中生成对应的锁结构。

三丶InnoDB表锁

1.表级S，X锁

innodb支持表级锁，也支持行级锁，表级锁粒度相对更粗，占用资源较少。使用表级锁效果相当于为表中的所有记录加锁，所以性能比较差。

表级S锁，X锁

使用Lock Tables t Read，innodb存储引擎会对表t加共享锁

使用Lock tables t write，innodb存储引擎会对表t加独占锁

类似于Java中的读写锁，共享锁和共享锁不互斥，独占锁和独占锁，独占锁和共享锁互斥。

2.MDL

一般情况下对某一个表执行增删改查的时候，都不会加表锁，但是执行一些DDL修改表结构，删除表时，其他事务对这表的增删改查发生阻塞。这是由MDL锁实现的，MDL锁也分为读锁和写锁，在进行crud操作的时候，会加MDL读锁，进行DDL的时候会加MDL写锁。

我们需要注意MDL读锁写锁是互斥的

如图四个不同的session先后依次执行语句，其中A，B都是获取MDL读锁，互不阻塞，随即C获取MDL写锁，这时候C会被阻塞，这一阻塞不打紧，还会阻塞后续获取MDL读锁的事务，造成整个表不可用。这启发了我们，在做DDL的时候要解决长事务，事务不提交，就会一直占着MDL锁。在 MySQL 的 information_schema 库的innodb_trx 表中，可以查到当前执行中的事务。如果要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

3.表级意向锁

innodb存储引擎中，当对表中某些记录加S锁之前，会在表上加上一个IS锁，同样加X锁之前会加表级IX锁，这里的I表示意向锁，SX表示共享还是互斥，表级意向锁存在的目的是后续对表加S锁，X锁的时候，可以快速判断表中是否存在加锁的记录，避免遍历每一个记录查看是否被加锁。

4.表级AUTO-INC锁

mysql可以为某列执行Auto Increment自增，系统给自增列赋值的实现方式主要存在两种

使用AUTO-INC锁，执行插入语句的时候加一个表级AUTO-INC锁，然后为每条待插入的记录中的自增列，进行递增赋值，单个插入语句执行结束后释放AUTO-INC锁。这样会导致其他事务的插入被阻塞。
采用轻量级AUTO-INC锁，在为插入列赋值结束后，就释放轻量级锁，而不是插入语句执行完后才释放

四丶innodb 行锁

1.Record Lock

官方名称Lock_REC_NOT_GAP

记录锁有S锁和X锁，S型记录锁之间可以共享，X型记录锁和S型记录锁，X型记录锁互斥

2.GAP Lock

innodb的可重复读级别，使用词锁解决幻读问题，前面我们说过，其难点在于，加锁的时候幻影记录还未出现。官方使用Lock_GAP实现如下操作

此处的gap锁可以反之其他事务在number为8记录前面的间隙插入新的记录，在区间（3，8）内无法进行插入操作，当另外一个事务要插入number为4的记录时，首先需要定位到该条记录的下一条记录，也就是number为8的记录，此时number为8的记录具备gap锁，所以将阻塞插入操作，直到gap锁被释放，其他事务才能进行插入。
gap锁出现的目的，就是为了防止插入幻影记录，如果对记录上gap锁，并不会限制其他事务对记录加记录锁。

innodb有两个虚拟的记录Infimum(虚拟最小)，Supermun(虚拟最大)当我们想在(xx,正无穷)范围锁住幻影记录时就可以对Supermun加gap锁。

3.Next-Key Lock

Next-Key Lock = 记录锁 + gap锁，既锁住记录，也锁住记录之前的间隙

4.Insert Intention Lock

插入意向锁，表示事务想在某个间隙插入新的记录，但是当前处于等待状态。

比如事务A持有（4，8）范围内的gap锁，事务B和C，想插入（4，8）范围内的记录，就会在内存中生成事务B，C对应的插入意向锁，当前事务A释放gap锁的时候，将唤醒事务B和C，事务B和C可以同时获取插入意向锁，然后进行插入。插入意向锁并不会阻止对记录继续上锁。

5.隐式锁

为事务生成内存中的锁结构并不是一个0成本的事情，为了节省这个成本，提出隐式锁的概念。

当一个事务插入语一条记录A，其他事务

select xxx Lock in share mode读取记录A（获取记录A的S锁），或者使用select xxx for update（获取记录A的X锁）
立即修改记录A（获取x锁）

对于聚簇索引来说，有一个隐藏列trx_id此列存储着最后更改记录的事务id，在当前事务A插入记录后，便是存储着当前事务A的id，其他事务B企图获取x锁，s锁的时候，就需要下先看一下，trx_id隐藏列对应的事务是否存活，如果不是那么正常获取，反之需要为当前事务A创建一个x锁内存结构，并标记is_waiting为false，然后事务B将为自己创建一个锁结构，is_waiting 为true然后事务B进入等待状态

对于二级索引来说，其不具备隐藏列trx_id但是在二级索引页面的page header中的page_maxt_trx_id属性，记录了改动页面最大的事务id，如果其属性值小于当前最小的活跃事务id，那么说明对页面的改动事务已经提交，否则需要定位到二级索引记录，然后回表对聚簇索引进行上述聚簇索引的操作。

一个事务对新插入的记录不需要显示的加锁，由于事务id的存在相当于加了一个隐式锁，别的事务需要加S锁或者X锁的时候，先帮之前的事务生成锁结构，然后为自己生成锁结构，再进入阻塞状态。隐式锁起到了延迟加锁的作用，也许别的事务不会获取于隐式锁冲突的锁，这时候可以减少内存中生成锁结构。

标签：事务,加锁,记录,read,插入,Mysql,id
From： https://www.cnblogs.com/cuzzz/p/16934334.html