共计 3707 个字符，预计需要花费 10 分钟才能阅读完成。
| 锁类型 / 引擎 | 行锁 | 表锁 | 页锁 |
|---|---|---|---|
| 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 能自动检测死锁，并使一个较简单的事务回退并释放锁，另一个事务获得锁，继续完成事务。
：
正文完
星哥玩云-微信公众号
