mysql 性能优化（七）锁机制

1、锁介绍

解决因资源共享，而造成的并发问题。
例如：例如：电商A，B两人同时购买最后一件商品
A：仅快0.0001秒抢到的->加锁->处理业务逻辑->释放锁
B：在B获取到商品时，商品已加锁，等待所释放，商品状态已发生改变，已售空。

• 分类：
  • 操作类型：
    • 读锁（共享锁）：对同一个数据，可以同时进行多个读操作，互不干扰
    • 写锁（互斥锁）： 如果当前写操作没有完成（未释放锁），则无法进行其他的读操作和写操作
  • 操作范围：
    • 行锁：一次性对一行加锁，如InnoDB存储引擎，开销大，加锁慢，容易出现死锁，锁的范围较小，不容易发生锁冲突，并发度高（很少发生，脏读、幻读、不可重复读、丢失更新）
    • 表锁：一次性对一个表加锁，如MyISAM存储引擎，开销小，加锁快，无死锁，容易生锁冲突，并发度低
    • 页锁（基本不用）

1.1、MyISAM表锁

• 查看锁

  1代表加了锁
  show open tables;

• 增加锁

  locak table 表1 read/write ,表2 read/write…可以加多个

• 释放锁

  unlocak tables;
  也可以通过事物解锁，ROLLBACK / COMMIT

• 查看表状态

  SHOW STATUS LIKE ‘table%’;

  • Table_locks_immediate：可以获取到的表锁数
  • Table_locks_waited：需要等待的表锁数，值越大说明锁竞争越大
  • Table_open_cache_hits：表缓存命中的次数
  • Table_open_cache_misses：高速缓存大小
  • Table_open_cache_overflows：超过了table_open_cache的设置，则会进行淘汰，淘汰的值记录到Table_open_cache_overflows

一般建议：Table_locks_immediate / Table_locks_waited > 5000采用InnoDb，否则采用MyISAM

1.1.1、加读锁

例如：会话0给A表增加了读锁

• 当前会话
  • 如果给A表加了读操作，则当前会话只能对A表进行读操作
• 其他会话
  • 可以对其他表进行读写操作
  • 可以对A表进行读操作，写操作需要等待会话0释放锁后才会执行

1.1.2、加写锁

例如：会话0给A表增加了写锁

• 当前会话
  • 可以对当前表进行任何操作；
  • 不能操作其他表
• 其他会话
  • 需要等待会话0释放锁后，才能进行增删改查操作

mysql表级锁的锁模式

MyISAM

• 执行查询语句（select）前，会自动给涉及的所有表加读锁。
• 执行更新操作（DML）前，会自动给涉及的表加写锁。

所以对MyISAM表操作会有一下几种情况

• 对MyISAM表的读操作（加读锁）
  • 不会阻塞其他进程读（会话）对同一个表的读操作
  • 会阻塞对同一个表的写操作，只有读锁释放后才会执行其他会话写操作。
• 对MyISAM表的写操作（加写锁）
  • 会阻塞其他进程（会话）对同一个表读写操作，写锁释放后才会执行其他会话读写操作。

2.1、行锁（InnoDB）

如果会话X对某条数据a进行DML操作

• 其他会话需要等待会话A结束事物后，才能对a数据 进行操作
• 行锁是通过事物来解锁的
• 行锁一次锁一行数据，操作不同行数据互不干扰

注意

• 如果没有索引或索引失效，则行锁转为表锁

通过检查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(等待总时长)

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

2.1.1、间隙锁

行锁的一种特殊情况：值在范围内，但却不存在

例如：一个表a中，ID有1、2、3、4、6、7，这些条数据，但是没有id等于5的

update a set name =‘name’ where id >1 and id< 7

在where中没有id等于5的数据，所以 id =5的数据称之为间隙

mysql会自动给间隙加锁 所以示例中会自动给id=5的数据加间隙锁（行锁）

如果有where 则加锁的范围就是where后面的范围，不是实际的值

• 总结：
  InnoDB默认采用行锁；
  缺点：比表锁性能损耗大
  有点：并发能力强，效率高
  建议：高并发采用InnoDB，否则采用MyISAM
• 分析

  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：等待锁的总时长，从系统启动到现在一共等待的次数

2.1.2、查询数据加锁

for update 行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。

只有当出现如下之一的条件，才会释放共享更新锁：

1. 执行提交（COMMIT）语句
2. 退出数据库（LOG　OFF）
3. 程序停止运行

select * from tableName where id =1 for update;

FOR UPDATE仅适用于InnoDB，且必须在事务处理模块(BEGIN/COMMIT)中才能生效。

什么时候需要使用for update？

借助for update语句，我们可以在应用程序的层面手工实现数据加锁保护操作。就是那些需要业务层面数据独占时，可以考虑使用for update。

场景上，比如火车票订票，在屏幕上显示有票，而真正进行出票时，需要重新确定一下这个数据没有被其他客户端修改。所以，在这个确认过程中，可以使用for update。

2.1.3 update where 无索引字段更新或索引范围大

例如 一个用户表，学历字段qualification 是非索引字段

update user set  qualification ="本科以下" where qualification = "大专"

在执行更新时会全表锁，会导致有其他业务在使用时表中数据时产生Lock wait timeout exceeded; try restarting transaction 异常

或者是索引，但是索引覆盖范围太大，在业务请求量大时也会引起间隙锁

update user_table set qualification ="本科以下" where qualification = "大专" and ....

• 总结
  • 尽量使用主键更新数据
  • 特殊场景一定要控制好索引覆盖范围

3、锁优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
• 尽可能低级别事务隔离