前言：
Mysql是一个支持插件式存储引擎的数据库系统，本文讨论的锁机制也主要包含两部分SERVER层的锁和存储引擎的锁，存储引擎是指innodb，其它存储引暂不讨论。
1. 数据库中锁相关的基本概念
1) 乐观锁，悲观锁
乐观锁和悲观锁都是一种并发控制策略。悲观锁假定多个事务会同时访问同一个资源，采用的策略是“先上锁，后访问”，这种策略会有死锁的风险。乐观锁相对于悲观锁而言，假定多个事务在运行过程中不会相互影响，写入在读取和写入记录时，不上锁，取而代之是产生一个时间戳或版本号，事务提交阶段，检查记录的版本号是否有被修改(若修改，则表示有其他事务读写)，确定是否需要回滚事务。目前在数据库领域，几乎所有的DBMS都是采用悲观锁机制。
2) MVCC(Multi-Version Concurrency Control)
MVCC也是一种并发控制方法，MVCC对悲观锁控制机制做了改进，通过冗余数据的历史版本，达到“读不上锁，读写不冲突”的效果，提高了并发效果。MVCC主要作用于读提交和可重复读两种隔离级别上。
3) 两阶段锁协议
所谓两段锁协议是指上锁分为两个阶段，加锁和解锁阶段，保证加锁和解锁阶段不交错。对于数据库系统而言，事务开始时，处于加锁阶段；事务提交或rollback时，事务进入解锁阶段。只有满足两段锁协议的数据库系统，并发调度的事务才是可以串行化的。
4) 意向锁
意向锁机制约定如果对一个节点加锁，必需先对它的上一层节点加意向锁。比如，对一个记录加锁之前，首先对该记录所在的表加意向锁。意向锁主要包括IS和IX，他们与S和X的兼容关系不在这里赘述。意向锁的主要作用在于提高表锁和行锁冲突检测效率。
5) 表锁，记录(行)锁
表锁和记录锁是锁系统里面最基本的锁。分别用于锁定表和记录。对于表而言，根据情况可以有S，X，IS和IX四种锁类别；对表上IS和IX，表示需要读记录和写记录；记录锁则主要包括X锁和S锁。有关行锁的实现，可以参考之前的文章，INNODB行锁源码学习
6) 字典锁(metadata lock)
字典锁是保护元数据的一种锁，主要为了防止DDL和DML冲突的情况。有关MDL的详细介绍，可以参考之前的文章，MYSQL METADATA LOCK
7) 死锁
所谓死锁是指两个或多个事务，各自占有对方的期望获得的资源，形成的循环等待，彼此无法继续执行的一种状态。
2. 举个栗子
上面列了这么多种类的锁，下面通过一个简单的例子说明各种锁是如何作用的，它们加锁和释放锁的先后顺序如何。这里假设隔离级别是RC，ID为主键。
begin：
update t3 set c1=1 where id=1;
commit;
| 流程 | 执行语句 | 执行内容 | 字典锁 | 行锁/表锁 |
| 1 | Begin |
| 释放MDL release_transactional_locks | 释放表锁，行锁 |
| 2 | update t3 set c1=1 where id=1;
| 上字典锁 | GLOBAL:STATMENT MDL_INTENTION_EXCLUSIVE |
|
| 3 | TABLE:TRANSACTION MDL_SHARED_WRITE |
| ||
| 4 | 上行锁 |
| LOCK_TABLE:IX (table:t3) | |
| 5 |
| LOCK_REC:X (id=1) | ||
| 6 | 执行更新 |
|
| |
| 7 | 释放MDL | GLOBAL:STATMENT |
| |
| 8 | commit;
| COMMIT 字典锁 | COMMIT: MDL_EXPLICIT MDL_INTENTION_EXCLUSIVE |
|
| 9 | 执行提交 |
|
| |
| 10 | 释放引擎锁 |
| lock_release | |
| 11 | 释放MDL
| COMMIT: MDL_EXPLICIT MDL_INTENTION_EXCLUSIVE |
| |
| 12 | release_transactional_locks TABLE:TRANSACTION |
|
可以看到，第一行begin，表示开启一个新事务，隐含提交会话的上一个事务，需要释放之前的锁。第2到7行是两阶段锁中的上锁阶段，分别先后上了字典锁、表的意向锁和行锁。上完锁后，才开始真正的更新阶段，从这里也可以看到MySQL的写操作是符合悲观锁策略。第4行和第5行，我们可以看到意向锁是如何运作的，上记录id=1的行锁之前，先对表t3上了意向锁。第7行，语句执行完后，可以释放STATEMENT级别的字典锁，避免长时间持有锁阻塞该表的DDL操作。8-12是提交阶段，进入两段锁中的释放锁过程，先后释放引擎层的表锁和行锁；然后释放TRANSACTION级别的MDL锁。
3. 常用语句加锁分析
假设隔离级别：RC，id为主键
| 典型语句 | SQL层面（MDL锁） | 存储引擎 innodb | ||
|
|
| |||
| 范围/对象 | 持有时间 | 表锁 | 行锁 | |
| SELECT操作 SELECT * FROM T | TABLE： MDL_SHARED_READ | MDL_TRANSACTION | None | None |
| Show create table T | TABLE: MDL_SHARED_HIGH_PRIO | MDL_TRANSACTION |
|
|
| LOCK TABLE T READ | TABLE: MDL_SHARED_READ | MDL_TRANSACTION | None | None |
| LOCK TABLE T WRITE | GLOBAL： MDL_INTENTION_EXCLUSIVE | MDL_STATEMENT
| None | None |
| SCHEMA: MDL_INTENTION_EXCLUSIVE TABLE： MDL_SHARED_NO_READ_WRITE
| TRANSACTION | |||
| Flush table t with read lock | TABLE: MDL_SHARED_NO_WRITE | TRANSACTION | None | None |
| Flush table with read lock | GLOBAL: MDL_SHARED
| MDL_EXPLICIT | None | None |
| COMMIT: MDL_SHARED
| MDL_EXPLICIT | |||
| DML操作 SELECT * FROM T FOR UPDATE; Update T set c1=? Where id=? | GLOBAL: MDL_INTENTION_EXCLUSIVE | MDL_STATEMENT | IX | X |
| TABLE: MDL_SHARED_WRITE | TRANSACTION
| |||
| DDL操作 Alter table t add column c1 int; Truncate table t; | GLOBAL: MDL_INTENTION_EXCLUSIVE
| MDL_STATEMENT | None | X |
| SCHEMA: MDL_INTENTION_EXCLUSIVE TABLE: MDL_EXCLUSIVE | TRANSACTION
| |||
| COMMIT: MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT | |||
| Set global read_only=1; | GLOBAL: MDL_SHARED COMMIT: MDL_SHARED | MDL_EXPLICIT |
None |
None |