Mysql锁机制与优化

欢迎大家关注我的微信公众号：
?

传送门：Mysql事务原理与优化?

目录

概述?

锁分类?

锁等待分析?

锁优化实践

概述?

????????锁是计算机协调多个进程或线程并发访问某一资源的机制。

????????在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类?

• 从性能上分为乐观锁(用版本对比或CAS机制)和悲观锁，乐观锁适合读操作较多的场景，悲观 锁适合写操作较多的场景，如果在写操作较多的场景使用乐观锁会导致比对次数过多，影响性能
• 从对数据操作的粒度分，分为表锁、行锁、页锁
• 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)，还有意向锁

????????读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响，比如：?

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

select * from T where id=1 for update

?

--手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
--查看表上加过的锁
show open tables;
--删除表锁
unlock tables;

• InnoDB支持事务（TRANSACTION）
• InnoDB支持行级锁

select * from account where name = 'lilei' for update; ‐‐where条件里的name字段无索引

?那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，在Session_1下面执行如下sql：

select * from account where id = 18 for update;

则其他Session没法在这个(10,20)这个间隙范围里插入任何数据。??

如果执行下面这条sql：?

select * from account where id = 25 for update;

则其他Session没法在这个(20,正无穷)这个间隙范围里插入任何数据。?

????????也就是说，只要在间隙范围内锁了一条不存在的记录会锁住整个间隙范围，不锁边界记录，这样就能防止其它Session在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。?

?还是用account表，如果执行下面这条sql：

select * from account where id > 3 and id <= 10 for update;

?不仅 (3,10)的间隙会上锁，id=10的行记录也会上锁，这就是临键锁

锁等待分析?

通过检查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';
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尽量放在事务最后执行
• 尽可能用低的事务隔离级别

?