where解析顺序_MySQL中锁机制超详细解析，看这一篇就会了！

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决。
本篇文章结合具体案例详细解析了MySQL中锁机制。
MySQL中锁的分类
在MySQL中有三种锁的级别：页级锁、表级锁、行级锁。其中：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。会发生在：MyISAM、memory、InnoDB、BDB等存储引擎中
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。会发生在：InnoDB存储引擎
• 页级锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。会发生在：BDB存储引擎

MySQL中的表锁包括读锁和写锁。只需记住这个表锁模式兼容矩阵即可。

In'noDB存储引擎锁分类及问题排查
在MySQL InnoDB存储引擎中，锁分为行锁和表锁。行锁包括两种锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。表锁分为三种：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
• 自增锁(AUTO-INC Locks)：特殊表锁，自增长计数器通过该“锁”来获得子增长计数器最大的计数值。

在加行锁之前必须先获得表级意向锁，否则等待innodb_lock_wait_timeout超时后根据innodb_rollback_on_timeout决定是否回滚事务
在MySQL InnoDB存储引擎中，我们在设计表结构的时候，通常会建议添加一列作为自增主键。这里就会涉及到一个特殊的锁：自增锁（即：AUTO-INC lock），它属于表锁的一种，在insert结束后立即释放。我们可以执行show engine innodb statusG来查看自增锁的状态信息。
在自增锁的使用过程中，有一个核心参数，需要关注一下，即innodb_autoinc_lock_mode,它有0、1、2三个值。保持默认就行。具体的含义可以参考官方文档。

InnoDB锁关系矩阵如下，其中：+ 表示兼容，- 表示不兼容。

InnoDB行锁是通过对索引数据页上的记录（record）加锁实现。主要实现算法有3种：
1、record lock锁：单个行记录的锁（锁数据，不锁Gap）
2、gap lock锁：间隙锁，锁定一个范围，不包括记录本身（不锁数据，仅仅锁数据前面的Gap）
3、next-key lock锁：同时锁住数据，并且锁住数据前面的Gap。所以：next-key lock = record lock + gap lock排查InnoDB锁问题通常可以有2种方法：
第一种：打开innodb_lock_monitor表，注意使用后记得关闭，会影响性能。
第二种：在MySQL5.5之后，可以通过查看information_schema库下面的innodb_locks,innodb_lock_waits,innodb_trx 三个视图
InnoDB不同索引的加锁行为实例
分析锁要跟隔离级别联系起来，我们以RR为例，主要是从四个场景分析：
1、主键 + RR
2、唯一键 + RR
3、非唯一键 + RRc
4、无索引 + RR

InnoDB死锁产生条件及避免优化
在MySQL中死锁不会发生在MyISAM存储引擎中，但会在InnoDB存储引擎中发生，因为InnoDB是逐行加锁，极容易产生死锁。产生死锁的的四个条件如下：
1、互斥条件：一个资源每次只能被一个进程使用；
2、请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；
3、不剥夺条件：进程已获得的资源，在没使用完之前，不能强行剥夺；
4、循环等待条件：多个进程之间形成一种互相循环等待资源的关系。
在发生死锁时，InnoDB存储引擎会自动检测，并且会自动回滚代价较小的事务来解决死锁。但很多时候一旦死锁发生，InnoDB存储引擎的处理的效率是很低下的或者有时候根本解决不了，需要人为手动去解决。避免死锁的产生的一些建议：
1、加锁顺序一致
2、尽量基于primary或unique key更新数据
3、单次操作数据量不宜过多，涉及表尽量少
4、减少表上索引，减少锁定资源
5、相关工具：pt-deadlock-logger产生死锁的案例：
1）基于资源争用导致死锁的情况：

2）metadata lock（即元数据锁）导致的死锁的情况：

查看MySQL数据库中死锁的相关信息，可以执行show engine innodb statusG 来进行查看，重点关注 “LATEST DETECTED DEADLOCK” 部分。

避免线上业务因死锁造成的不必要的影响，开发建议如下：
1、更新sql的where条件尽量用索引
2、加锁索引准确，缩小锁定范围
3、减少范围更新，尤其非主键/非唯一索引上的范围更新
4、控制事务大小，减少锁定数据量和锁定时间长度 (innodb_row_lock_time_avg)
5、加锁顺序一致，尽可能一次性锁定所有所需数据行