mysql之锁机制

序言

     本文主要讲解mysql的锁种类各有什么作用，为什么需要锁，锁和什么有关，死锁等等。再来一个昨天项目中遇到的一个死锁的情况以及解决方法。一直没有想好，怎么写这篇文章，直到昨天遇到了死锁的问题并且已经解决了，才决定整理好思路，分享给大家，避免大家掉进坑里面。

为什么会有锁？

     记得很清楚初学java的时候，只是感觉会一些ddl以及dml语句就行了，直到找工作面试的时候发现，面试官喜欢问一些mysql锁、mysql索引算法以及隔离级别事务等。所以也针对性的学过mysql，也写过一些对应的文章，好了，扯的有点远了，说重点。其实我理解的锁就是和java中的synchronized、volatile和Lock作用是一样的，就是为了解决并发的问题，当然在解决并发问题（规避并发问题）的时候还需要解决性能的问题，不能捡了芝麻忘了西瓜吧，所以，mysql也为我们提供了丰富的锁，来解决问题。

 

锁和什么有关系？

     对于开发者来说mysql只是一个持久性数据库，按理说只需要会一些sql语句就完事了，并不会因为并发问题，还需要我们掌握额外的高级的sql来加锁，mysql中提供了隔离级别。在此我不想提太多关于隔离级别的知识，相关可以看隔离级别,但是如果有基础的朋友就可以略过，这个地方我还是要简单提一下：

     Innidb为我们提供了四种隔离级别（Isolation）：

     READ UNCOMMITTED  

     READ COMMITTED         读 添加 S 锁，读完立马释放不需要等待事务提交 ； 写 添加 X 锁 ，事务提交才释放锁 无间隙锁

     REPEATABLE READ     读 添加 S 锁，需要等待事务提交释放锁 ； 写 添加 X 锁 ，事务提交才释放锁  有间隙锁

     SERIALIZABLE

     不同隔离级别下存在不同的问题：

     脏读: 第二个事务读取到了第一个事务还未提交时的数据，在这种情况下，第一个事务发生回滚，第二个事务读取到的数据发生变化，导致事务二两次读取的数据不一致。这种情况发生在读未提交的隔离级别下。

     不可重复读：第一个事务读取的所有事务已经提交过的数据，但是这个时候，又来了一个事务二将符合事务一读取条件的部分记录进行修改了，也最终导致两次读取的数据不一致。这种情况发生在读已提交的隔离级别下。出现这种问题，是因为事务一读取数据之后植物虽然没有提交但是S锁已经释放了，随后事务二添加了X锁并进行了修改提交。可重复读隔离级别下避免了该问题。

      幻读：和上面的不可重复读场景相同，不同的是，幻读倾向于delete、insert，而不可重复读倾向于update。导致这个原因就是记录中间未添加间隙锁。导致符合条件的间隙之间还能插入数据。

   通过上面可以更明确的看到，锁其实减少并发问题的一种手段，而该手段不需要开发者自己实现而是通过修改数据库的隔离级别实现对并发问题的避免，也就是说不同的隔离级别使用的锁机制也不尽相同。

 

锁种类

    Mysql InnoDB存储引擎，实现的是基于多版本并发控制协议MVCC(Multi-Version Concurrency Control)。读操作可以分为两类：快照读(snapshot read ) 与 当前读(current read)。

    快照读：读取时记录的可见版本（有可能是历史版本），不加锁。

    select * from table where ?

    当前读：读取的记录是最新版本的，并且需要加锁保证其他事务不会再并发修改这条记录。

    (1) select * from table where ?  lock in share mode;

    (2) select * from table where ? for update;

    (3) insert into 

    (4) update 

    (5) delete from 

     上面五种类型都是属于当前读，读取记录的最新版本。其中第一种是使用的共享锁（S），其他四种是排他锁(X)。

     为什么将 插入/更新/删除 操作，都归为当前读？可以看看下面这个 更新 操作，在数据库中的执行流程：

       从上面可以看到所有的的update的具体流程，一条update语句，其实是一条一条的去执行的，并且是先读取再更新。而并非是先读取全部符合条件的记录都加上锁之后，再进行更新操作，想象也不太可能，因为这样的并发度就会降低。每次只锁一行。

 

   简单说一下，共享锁和排他锁都是都是属于悲观锁，mysql锁整体上分为两种：乐观锁和悲观锁，

   乐观锁：总时假设最好的情况，每次拿去数据的时候都认为别人不会修改，所以不会加锁，但是更新的时候会判断此区间别人有没有去更新这条记录；需要用户自己给表设置version字段，每次更新操作version加1（CAS）。适用于多读的应用类型下，这样可以提交吞吐量。

   悲观锁：总时假设最坏的情况，每次去拿数据䣌时候都认为别人会修改，所以每次拿数据的时候都会加上锁，这样别人想拿这个数据就会判断是否加锁。不同的锁情况不同，比如上面说的共享锁，就可以多个人同时去读，但是不能写，排他锁，只能一个人拿到读或者写，其他人都无法拿到。

 

锁机制

     传统的RDBMS加索的一个原则，就是2PL（二阶段锁）：Two-Phase Locking。2PL说的就是所操作分为两个阶段：加锁阶段和解锁阶段。

   从上图可以看出，2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段只加锁不释放锁；解锁阶段只释放锁不加锁。

 

一条简单的sql加锁实现分析

前提： 

    表中字段 ： user(id , name ,age,invalid)  

    sql：delete from user where id = 4;

    id情况不确定：主键、唯一索引、普通索引、普通字段

    隔离级别不确定：RC、RR   读未提交和串行化基本上没有人使用，暂时不考虑。

    分期之前确定执行计划中，如果id是索引走的是不是该索引优化，如果不是的就别提了。

分析

	主键	唯一索引	普通索引	普通字段
RC
RR

RC

1、主键

2、唯一索引

    假设name是主键，id是唯一索引

3、普通索引

4、普通字段

     因为id列上面没有索引，所以会走全表扫描，从图上可以看出，只有两条符合条件，但是所有的聚簇索引上都加上了X。这是因为一个条件如果无法通过索引快速过滤，mysql 的存储引擎方面就会将所有的记录加锁返回交给mysql server，再由server做进一步的处理。

 

   mysql实际中有一些改进，在mysql server过滤条件，发现不满足后，会调用unlock_row方法，把不满足的记录放锁，但是违背了2PL规范，这样最后只是在满足条件的记录上加锁，但是上面的在每个上面加锁是少不了的。

 

RR

1、主键

和上面相同

2、唯一索引

和上面相同

3、普通索引

    GAP锁只会添加到二级索引的间隙

4、普通字段

   和上面的第四种情况一样，将所有的记录都加上X，并且间隙为GAP锁，看起来是不是很可怕。

  相对应的也是有优化的。semi-consistent read开启的情况下，对于不满足条件的记录会提前释放锁，同时不加GAP锁

 

 

 

死锁

查看死锁日志sql

 

死锁原因

 

分析死锁

 

 

解决死锁

 

 

 

死锁实战分析

 创建表的语句

mysql> show create table db_object \G;
*************************** 1. row ***************************
       Table: db_object
Create Table: CREATE TABLE `db_object` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '对象ID',
  `name` varchar(200) NOT NULL DEFAULT '' COMMENT '对象名称',
  `sub_name` varchar(256) NOT NULL DEFAULT '' COMMENT '对象名称2 Oracle中有，不清楚干吗用',
  `db_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '所属数据库ID',
  `schema_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '所属模式ID',
  `type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '对象类型 0未知，1表，2视图，3物化视图，4主键，5外键，6约束，7触发器，8存储函数，9存储过程，10包头，11包体，12TYPE，13TYPE_BODY，14序列，15同义词...',
  `ddl` longtext NOT NULL COMMENT '对象类型 0未知，1表，2视图，3物化视图，4主键，5外键，6约束，7触发器，8存储函数，9存储过程，10包头，11包体，12TYPE，13TYPE_BODY，14序列，15同义词...',
  `invalid` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否已删除 0否，1是',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_db_id` (`db_id`) USING BTREE,
  KEY `idx_schema_id` (`schema_id`,`type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30830 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='数据库包含的对象信息表'

 

   

   

 

 

 

 

参考

面试必备之乐观锁与悲观锁:https://blog.csdn.net/qq_34337272/article/details/81072874

mysql索引加锁分析:https://www.jianshu.com/p/13f5777966dd