内容提供: 周彦伟, 极数云舟 CEO
锁是计算机协调多个进程或纯线程并发访问某一资源的机制. 在数据库中, 除传统的计算资源 (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 status\G 来查看自增锁的状态信息.
在自增锁的使用过程中, 有一个核心参数, 需要关注一下, 即 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 status\G 来进行查看, 重点关注 "LATEST DETECTED DEADLOCK" 部分.
避免线上业务因死锁造成的不必要的影响, 开发建议如下:
1, 更新 sql 的 where 条件尽量用索引
2, 加锁索引准确, 缩小锁定范围
3, 减少范围更新, 尤其非主键 / 非唯一索引上的范围更新
4, 控制事务大小, 减少锁定数据量和锁定时间长度 (innodb_row_lock_time_avg)
5, 加锁顺序一致, 尽可能一次性锁定所有所需数据行
内容摘取自《高性能 MySQL 实战》
来源: https://www.qcloud.com/developer/article/1643686