MySQL 锁机制

学习 尚硅谷MySQL高级 周阳老师视频，总结笔记。

概述

定义

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

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的分类

从对数据操作的类型分

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会相互影响。
• 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分

• 表锁
• 行锁

开销、加锁速度、死锁、粒度、并发性能 只能就具体应用的特点来说哪种锁更合适。

表锁（偏读）

特点

偏向MyISAM 存储引擎，开销小，加锁块；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

案例分析

建表

CREATE TABLE mylock(
id int NOT NULL KEY auto_increment,
name VARCHAR(20)
)ENGINE myisam;

锁的基本操作

查看数据库所有表上锁情况：

SHOW OPEN TABLES;

给mylock 表上读锁，book 表上写锁：

LOCK TABLE mylock READ,book WRITE;

释放所有锁：

UNLOCK TABLES;

加读锁（共享锁）

为mylock 表加 read 锁（读 阻塞写例子）

加写锁（独占锁）

为mylock 表加 write锁（写 阻塞读例子）

结论

MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

1. 对MyISAM 表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
2. 对MyISAM 表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之，读锁会阻塞写，但不会阻塞读。写锁既会阻塞读也会阻塞写。

表锁分析

查看数据库所有表上锁情况：

SHOW OPEN TABLES;

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定：

SHOW STATUS like 'table%';

• table_locks_immediate ：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁，值加1。
• table_locks_waited ：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况。

MyISAM的读写锁调度是写优先，这也是MyISAM 不适合做写为主的表引擎的原因。因为写锁后，其他线程不能做任何操作，大量更新会使查询很难得到锁，从而造成长时间阻塞。

行锁（偏写）

特点

InnoDB 存储引擎采用，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

InnoDB与MyISAM的最大不同：

1. 支持事务（transaction）
2. 采用了行级锁

案例分析

行锁演示

更新不同行，不会阻塞。

无索引行锁变成表锁

索引 建了，使用不当（如 索引列为varchar类型，操作时不加引号，导致自动类型转换，从而使索引失效），索引失效，会使行锁变成表锁。

varchar类型不加单引号是重罪！！！

间隙锁危害

间隙锁带来的插入问题：

间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。

InnDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

危害：
因为query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定范围内的任何数据。在某些场景下可能会对性能造成危害。

面试如何锁定一行

结论

Innodb 存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于 MyISAM 的表级锁定。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势。
但是，如果使用不当，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:系统启动后到现在总共等待的次数；

当等待次数很高，而且每次等待时长也不小时，就需要分析系统中为什么会有如此多的等待（用 show profile …），然后根据分析结果着手指定优化计划。

优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
2. 合理设计索引，尽量缩小锁的范围。
3. 尽可能减少检索条件，避免间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间
5. 尽可能低级别事务隔离

页锁

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。
了解即可。