Mysql高级——锁

锁

mysql锁的分类

• 从性能上分为：乐观锁、悲观锁
• 从锁的粒度上分：行锁、间隙锁、页锁、悲观锁
• 从对数据库的操作分类：读锁、写锁

乐观锁需要我们自己通过version字段来实现，如果更新失败则在代码中进行where重试。而我们常见的读锁和写锁就是悲观锁。乐观锁适合于读多的场景，悲观锁适合于写多的场景

读锁

共享锁

select * from tableName where id=1 lock in share mode

写锁

排他锁

select * from T where id=1  for update

行锁

InnoDB的行锁不是锁的一行记录，而是针对索引加的锁。如果在更新时 where字句后面的字段没有索引或者是索引失效，那么此时行锁则会升级为表锁，（只有可重复读隔离级别才会进行锁升级，RC隔离级别不会）

在InnoDB存储引擎中，读未提交、读已提交、可重复读隔离界别下进行查询操作是不会加读锁的，进行更新操作时会加写锁，如果更新的数据存在则是加行锁，如果更新的数据不存在，则会变为间隙锁，锁住一个区间。如果更新的字段没有索引则会从行锁/间隙锁升级为表锁(RR隔离级别下才会升级)。在串行化隔离界别下查询操作会加读锁，更新操作会加写锁。

而MyISAM存储引擎，进行读操作时会加读锁，进行更新操作时会加写锁，这里都是表锁

为什么在可重复读隔离界别下可能会出现行锁升级为表锁？

这是因为它需要满足可重复读和解决幻读问题，进行更新操作时会去扫描聚集索引，为了避免其他事务更改已经扫描过后的数据 进而造成不可重复读和幻读问题的出现，才会去进行表锁。这里也不是真正意义上的加一个表锁，因为此时可能会有其他行被其他事务锁住，它实际上是为每条数据和各个间隙加锁

间隙锁

锁的是一个区间，(n,m)，它只有在可重复读隔离界别下才有间隙锁，因为它是来避免幻读问题的。

在更新时如果要更新的数据不存在，此时则会创建间隙锁吗，锁住当前这个区间。

Next-key 锁

是行锁+间隙锁，锁的区间是(n,m]

页锁

页锁只有在BDB存储引擎下才会存在，了解即可

表锁

相比较于行锁而言，表锁的加锁粒度更大，加锁开销更小，锁冲突可能性更高

意向锁

它的目的是提高加表锁的效率，当我们加行锁时，就会为这张表加一个意向锁，其实就是在表上有一个字段标识位。

如果某条数据被某个事务加了行锁，此时其他事务是不能加表锁的，会有锁冲突

当如果需要加表锁时，就不需要去一行一行去遍历数据行 检查此时有没有加行锁，而是直接检查这个标识位就可以确定当前能不能加表锁。

意向锁也分为意向读锁、意向写锁

锁等待分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

对各个状态量的说明如下：

• 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（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

查看INFORMATION_SCHEMA系统库锁相关数据表

-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁，8.0之后需要换成这张表performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;  
-- 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  -- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id-- 查看锁等待详细信息
show engine innodb status; 

死锁问题分析

-- 先修改数据库隔离界别
set tx_isolation='repeatable-read';

-- 接下来就会造成死锁，当然是需要begin各自开启一个事务再开始执行下面的语句
Session_1执行：select * from account where id=1 for update;
Session_2执行：select * from account where id=2 for update;
Session_1执行：select * from account where id=2 for update;
Session_2执行：select * from account where id=1 for update;

-- 查看近期死锁日志信息
show engine innodb status; 

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁，这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉。

锁优化实践

• 让数据检索走索引，避免无索引导致行锁升级为表锁的情况
• 尽可能减少范围查询，避免间隙锁
• 尽量控制事务大小，控制锁定资源量与时长，涉及到事务加锁的sql操作尽量放在事务后面执行
• 为了提高效率尽可能使用低事务隔离级别