锁类型 / 引擎 | 行锁 | 表锁 | 页锁 |
---|---|---|---|
MyISAM | 有 | ||
InnoDB | 有 | 有 | |
BDB(被 InnoDB 取代) | 有 | 有 |
锁的分类
表锁: 开销小, 加锁快, 不会死锁, 粒度大, 冲突率高, 并发低.
行锁: 开销大, 加锁慢, 会死锁, 粒度小, 冲突率低, 并发高.
页锁: 处于表锁和行锁之间, 会死锁.
锁的适用场景
表锁: 更适用于查询为主, 按少量索引条件更新.
行锁: 更适用于大量按索引并发更新少量不同数据, 同时又有并发查询.
MyISAM 表锁
查看锁争用相关参数: show status like 'table%';
Table_locks_waited 的值越高表示表锁争用越高.
MyISAM 表的读操作, 会阻塞同表的其他读请求, 会阻塞同表写请求;
写操作会阻塞同表的读请求和写请求.
读与写, 写与写之间串行, 持锁线程可对表更新, 其他线程读 / 写都会等待, 直到锁释放.
MyISAM 写阻塞读的例子
session 1 | session 2 |
---|---|
lock table user write; | |
select * from user; // 返回查询结果 | select * from user; // 被阻塞,等待锁被释放 |
unlock tables; | 获得锁,返回查询结果 |
注:
lock tables 时, 要一次性锁定用到的所有表
对别名也需要锁定, 如: lock table user as a read, user as b read;
MyISAM 读阻塞写例子
session 1 | session 2 |
---|---|
lock table user read; | |
可查询:select * from user; | 可查询:select * from user; |
不能查询未锁定的表:select * from goods; //Table 'goods' was not locked with Lock Tables | 能查询 / 更新未锁定的表 |
当前 session 更新锁定表会报错,Read Lock | 更新锁定表会等待 |
Unlock tables; | 获得锁,更新完成 |
MyISAM 并发插入
系统变量 concurrent_insert: 用于控制并发插入行为
0 不允许并发插入
1 表中没有被删除的行 (即没有空洞), 则允许一个进程读, 另一个进程在表尾插入 (默认设置)
2 表中不论是否存在空洞, 都允许在表尾并发插入
MyISAM 读写并发
session 1 | session 2 |
---|---|
lock table user read local; | |
当前 session 无法对该表更新或插入 | 可以插入,但更新需要等待锁释放 |
无法访问其他 session 插入的数据 | |
unlock tables; | 获得锁,更新完成 |
可以查到其他 session 插入的数据 |
注:
利用并发插入可以解决应用对同一个表查询和插入的锁争用;
将 cocurrent_insert 设置为 2, 定期 OPTIMIZE TABLE 来整理空间碎片, 回收删除记录产生的空洞.
MyISAM 锁调度
读锁与写锁互斥;
读操作与写操作串行;
写进程先获得锁, 即使读请求先到队列, 也会被写请求插队, 因为 MySQL 认为写比读要重要 (因此 MyISAM 不适合有大量更新 / 插入操作).
调节 MyISAM 锁调度行为
low-priority-updates, 给予读优先权利;
SET LOW-PRIORITY_UPDATES=1, 降低更新请求优先级;
指定 INSERT,UPDATE,DELETE 的 LOW-PRIORITY 属性, 降低该语句优先级.
解决读写冲突的方法:
系统参数 max_write_lock_count 设置合理值, 表的读锁达到设定阈值后, MySQL 就将写请求优先级降低.
一些需要长时间运行的读操作, 需要拆分为多条短 select sql, 复杂查询放在数据库空闲时段进行, 比如夜间执行.
InnoDB 与 MyISAM 最大区别:
支持事务;
行级锁.
事务 - Transaction
事务操作 | 描述 |
---|---|
BEGIN 或者 START TRANSACTION | 开始事务 |
COMMIT | 提交事务 |
ROLLBACK | 回滚结束事务,撤销进行中的所有未提交的修改 |
SAVEPOINT identifier | 设置保存点 |
RELEASE SAVEPOINT identifier | 事务回滚到保存点 |
ROLLBACK TO identifier | 撤销保存点 |
SET TRANSACTION = {READ UNCOMMITED,READ COMMITED,REPEATABLE READ,SERIALIZABLE} | 设置事务隔离级别 |
SET AUTOCOMMIT = {0,1} | 禁止 / 开启自动提交 |
事务的特性
A - Atomicity 原子性: 全执行 / 全不执行
C - Consistent 一致性: 数据状态一致
I - Isolation 隔离性: 事务处理过程中的中间状态对外不可见, 不受外部并发操作影响
D - Durable 持久性: 事务完成后对数据修改是永久性的
并发事务问题 | 描述 | 解决方案 |
---|---|---|
更新丢失 | 两个事务对同一行数据修改,先提交的被后提交的覆盖 | 应用程序对要更新的数据加锁 |
脏读 | A 事务改一行数据,B 事务读到了 A 的改动 “脏” 数据,A 回滚则 B 的数据有问题 | 数据库事务隔离,解决读一致性问题:1、读之前加锁,防止其他事务对数据修改;2、不加锁,生成快照,多版本并发控制 |
不可重复读 | 一个事务多次读取同一数据发现被改变 / 删除 | 同上 |
幻读 | 一个事务按先前的条件查询,发现其他事务插入了满足条件的新数据 | 同上 |
注:
事务隔离级别越高, 并发副作用越小, 代价越高, 因为事务隔离从某种程度上说使得事务串行化.
MySQL 事务隔离级别
隔离级别 / 并发问题 | 读一致性 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|---|
未提交读 | 最低 | 有 | 有 | 有 |
已提交读 | 语句级 | 无 | 有 | 有 |
可重复读 | 事务级 | 无 | 无 | 有 |
可序列化 | 最高 | 无 | 无 | 无 |
获取 InnoDB 行锁争用情况
show status like 'innodb_row_lock%';
锁争用严重时, InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 值较大.
InnoDB 行锁类型
行锁类型 | 描述 |
---|---|
共享锁 S | 允许事务读一行,阻止其他事务获得排他锁 |
排他锁 X | 允许事务更新数据,阻止其他事务获得共享读锁和排他写锁 |
意向共享锁 IS | 事务打算给行加共享锁,先取得表 IS 锁 |
意向排他锁 IX | 事务打算给行加排他锁,先取得表 IX 锁 |
请求锁模式是否兼容当前锁模式 | X | IX | S | IS |
---|---|---|---|---|
X | 否 | 否 | 否 | 否 |
IX | 否 | 是 | 否 | 是 |
S | 否 | 否 | 是 | 是 |
IS | 否 | 是 | 是 | 是 |
注:
含 I 的锁与含 I 的锁兼容;
单 X 与任何锁不兼容;
单 S 与含 X 的锁不兼容;
若一个事务请求的锁模式与当前的锁兼容, InnoDB 将请求的锁授予该事务, 不兼容就要等到锁释放;
意向锁是 InnoDB 自动加的, DELETE,UPDATE,INSERT,InnoDB 会自动加 X 锁, 普通 SELECT,InnoDB 不加任何锁.
手动加锁的方法
共享锁 (S):SELECT * FROM user LOCK IN SHARE MODE;
排他锁 (X):SELECT * FROM user FOR UPDATE;
注:
- SELECT * FROM ... LOCK IN SHARE MODE; // 若当前事务加了读锁, 进行更新会死锁
- SELECT * FROM ... FOR UPDATE; // 一个事务加了写锁, 其他事务加锁操作需要等待
InnoDB 行锁是通过给索引上的索引项加锁来实现的, 只有通过索引条件检索, 才会使用行级锁, 否则会用表锁;
分析锁冲突时, 检查 SQL 执行计划 (利用 explain), 以确认是否真正走了索引, 例如: SELECT * FROM user WHERE name = 123; //name 字段是 varchar 类型且有索引, 但条件中用了 int 型, 类型能自动转换, 但会进行全表扫描.
间隙锁 (Next-key Lock)
概念描述
用范围而非等值搜索数据, 并且请求共享 / 排他锁时, InnoDB 会对所有符合条件的已有记录的索引项加锁, 对键值在范围内但不存在的记录, 即 GAP - 间隙, 也会加锁.
例如:
user 表, id 从 1~100 共 100 个, 执行:
- SET AUTOCOMMIT = 0;
- SELECT * FROM id> 99 FOR UPDATE;
会对 id 等于 100 的记录的索引项加锁, 对 id 大于 99 的间隙加锁.
作用:
满足隔离级别要求, 防止幻读;
满足恢复和复制需要 (MySQL 通过 BINLOG 录入执行成功的 INSERT,UPDATE,DELETE 等更新语句)
存在的问题:
按范围加锁机制会阻塞符合条件范围内的键值并发插入, 造成锁等待.
解决方法:
优化业务逻辑, 尽量用相等条件来检索数据.
注:
相等条件检索一个不存在记录加锁时, InnoDB 也会使用间隙锁. 例如:
对上面的 user 表, 执行:
- SET AUTOCOMMIT = 0;
- SELECT * FROM id = 101 FOR UPDATE;
再在另一个 MySQL Session 中执行 INSERT INTO user (id, name, password, description)
- VALUES
- (101, 'clive', '123456', 'psw'); // 查询被阻塞, 进入等待直至锁释放
死锁的概念
死锁是指多个事务在统一资源上, 出现相互占用, 并请求锁定对方占用的资源, 从而导致恶性循环的现象.
MyISAM 和 InnoDB 在死锁上的区别
MyISAM 不会出现死锁, 因为 MyISAM 总是一次获得所需要的全部锁, 要么全部满足, 要么全等待;
InnoDB 除了单 SQL 事务, 锁是逐步获得的, 因此可能出现死锁. 一般 InnoDB 能自动检测死锁, 并使一个较简单的事务回退并释放锁, 另一个事务获得锁, 继续完成事务.
来源: http://www.linuxidc.com/Linux/2018-10/155054.htm