Mysql锁机制

MySQL有三种锁的级别：页级、表级、行级

MyISAM存储引擎采用的是表级锁（table-level locking）；

BDB存储引擎采用的是页面锁（page-levellocking），也支持表级锁；

InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，默认行锁。

MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度最高

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

1.表锁

表锁：把整张表锁起来，分为读锁和写锁。表锁偏读，主要存在MyISAM引擎上

1.1 读锁

当某个会话session为某个表table加了读锁：

当前会话可以读表，不可以写表，还不可以读其他表，直至解锁

其他会话可以读表，不可以写表，还可以读其他表

①show open tables；查看Mysql表的加锁情况，In_use=0表示该表没有加任何锁

2、lock table 【表名1】 【read/write】,【表名2】 【read/write】...;为表加锁

3、unlock tables;解锁

例子：

会话session_1为表t_stu加读锁，开另一个会话session_2观察执行情况：

①session_1和session_2都可以读取已加锁的表t_stu

②session_1不可以读其他未加锁的表，session_2可以

③session_1修改表数据会报错，session_2修改表数据会阻塞

④当session_1释放读锁后，session_2或其他会话抢夺锁资源执行sql

1.2 写锁

当会话session_1为表t_stu加了写锁：

session_1可以读表，可以修改表，不可以读/写其他未加锁的表

session_2不可以读表，不可以修改表，可以读/写其他未加锁的表

①session_1可以读表t_stu，session_2读表t_stu会阻塞

②session_1无法读/写其他表，session_2可以

③session_1可以修改表t_stu，session_2连读都不行，写就更不行了

④仅当session_1释放写锁，其他会话才可以读/写表t_stu

1.3 分析表锁

读锁会阻塞写，但不会阻塞读；写锁会把读和写都阻塞

如何分析表锁？

使用命令：show status like 'table%';分析系统的表锁定

Table_locks_immediate：产生表级锁定的次数

Table_locks_waited：出现表锁争用而发生等待的次数(每等待一次，锁值+1)，此值越高，说明存在较严重的竞争

2.行锁

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MyISAM的最大不同有两点：一是支持事务，二是采用行级锁。行锁默认是就是开启的，只要修改数据表的某行数据，该行就会自动加行锁，不同的行有不同的锁，互不影响。当要测试行锁时，需要把Mysql的自动事务提交关了，否则一回车，事务就提交，无法测试。

当session_1修改数据的时候，session_2再去修改只会被阻塞。在session_1未commit之前，session_2只会读到上一次commit后的数据

2.1 行锁变表锁

当where子句后面的条件列，不创建索引或者索引失效，会导致行锁变表锁。

索引生效：

行锁是对表的一行加锁，如果两个session操作不同行，是互不影响的：

当session_1修改id=1的行，session_2如果也修改id=1是会被阻塞的；

但session_2可以修改id!=1的行，如id=2

索引失效：

当索引失效时候，行锁就变成表锁，session_1在修改t_stu数据的时候，session_2即使修改不同的行，也是被阻塞：

让session_1修改第一行数据，即id=1

让session_2修改第五行数据，即id=10

如果正常情况下，这两个会话是互不影响的。根据前面的知识，如果varchar类型数据不加引号，会使索引失效：name字段是varchar，但我们故意在查询条件后不加引号使索引失效。可以看到，一旦索引失效，行锁直接变成表锁！！！

2.2 间隙锁

【什么是间隙锁】

       当使用范围条件而不是相等条件检索数据时，并请求共享或排他锁，InnoDB会给符合条件的已存在记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙”(GAP)，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）

【危害】

       因为Query执行过程中若用范围查找，它会锁定整个范围内所有的索引键值，即使这个键值不存在。因此，间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，就算某些不存在的键值也会被无辜锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据，仅当事务提交后才可添加，影响性能。

 

2.3 手动锁一行

当我们用select语句查询一行数据时，希望在查询期间，该行能锁定，不让其他事务修改，就需要我们手动锁定一行：

在select语句末尾加上“for update”，就可以为指定的行加锁，这时候，session_2要修改该行的数据就会被阻塞 ，直至session_1提交事务

2.4 分析行锁

通过命令：show status like'innodb_row_lock%';查看Mysql行锁的情况

对各个状态量的说明如下：（单位是毫秒）

innodb_row_lock_current_waits：当前正在等待锁定的数量

innodb_row_lock_time：从系统启动到现在锁定总时间长度

innodb_row_lock_time_avg：每次等待所花平均时间

innodb_row_lock_time_max：从系统启动到现在等待最长一次所花的时间

innodb_row_lock_waits：系统启动后到现在总共等待的次数

对于5个状态量，比较重要的是：

innodb_row_lock_time_avg：等待平均时长

innodb_row_lock_waits：等待总次数

innodb_row_lock_time：等待总时长

尤其当等待次数很高，且每次等待时长也不小的时候，便要分析并制定优化计划