20. 锁问题

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

1. MySQL锁概述

MySQL不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY使用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁，默认情况下采用的是行级锁。

MySQL锁的特性

• 表级锁：开销小，加锁快，无死锁，锁定粒度大，锁冲突概率最高，并发度最低
• 行级锁：开销大，加锁慢，存在死锁，锁定粒度小，锁冲突概率最小，并发度最高
• 页面锁：特点介于行级锁和表级锁之间

MyISAM不支持事务，所以对其设置 set autocommit = 0无效，只有对支持事务的存储引擎（InnoDB）才生效。

2. MyISAM表锁

MyISAM 存储引擎只支持表锁，如果对并发要求不高，并且主要以查询为主的业务，可以采用MyISAM。

2.1 MySQL表级锁的锁模式

MySQL 的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

锁模式的兼容性如下：

根据上图，对于存储引擎为MyISAM的表，不会阻塞其他用户对表加读锁，可以对表加多个读锁，但会阻塞对同一表加多个写锁；对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM 表的读操作与写操作之间，以及写操作之间是串行的！

对表加读锁，会话都可读，不可写，但是加锁的会话不能读其它未加锁的表。

对表加写锁，只有加锁的会话能读写表，其余会话不可读写表。

表级锁的加锁和解锁语句：

# 加锁
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}
# 解锁
UNLOCK TABLES;

下面演示了当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

2.2 如何加表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

给 MyISAM 表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。

在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及到表的锁，并且 MySQL 不支持锁升级。也就是说，在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM 总是一次获得 SQL 语句所需要的全部锁。这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。

一个 session 使用 LOCK TABLE 命令给表 film_text 加了读锁，这个 session 可以查询锁定表中的记录，但更新或访问其他表都会提示错误；同时，另外一个session 可以查询表中的记录，但更新就会出现锁等待。

2.3 并发插入(Concurrent Inserts)

上文提到过 MyISAM 表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行，在对表读操作的同时，进行更新操作。

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

• 当concurrent_insert设置为0时，不允许并发插入。
• 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
• 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入
  记录。

2.4 MyISAM的锁调度

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM 表的读锁，同时另一个进程也请求同一表的写锁，MySQL 如何处理呢？
答案是写进程先获得锁。即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL 认为写请求一般比读请求要重要。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节 MyISAM 的调度行为。

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

3. InnoDB锁问题

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTIONS）；而是采用了行级锁。

3.1 事务基础

1、事务及其ACID属性

不在阐述。

2、并发事务处理带来的问题

相对于串行处理，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量。但并发事务处理也会带来一些问题，主要有：更新丢失、脏读、不可重复读、幻读。

更新丢失属于应用层面，不属于数据库系统的管理范畴。

3、事务隔离级别

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

数据库实现事务隔离的方式，基本上可分为以下两种。

• 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或MCC），也经常称为多版本数据库。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。

3.2 InnoDB的行锁模式及加锁方法

InnoDB实现标准的行级锁定，其中有两种类型的锁定：

• 共享（S）锁：允许持有该锁的事务读取一行。

• 排他（X）锁定：允许持有该锁的事务更新或删除行。

如果事务T1在行r上持有一个共享（S）锁，那么来自某些不同事务T2的对行r上的锁的请求将按以下方式处理：

• T2可以请求S锁可以立即获得，T1和T2都在r上保持了S锁
• T2不能立即授予X锁请求。

如果事务T1在行r上拥有排他（X）锁，则不能立即批准某个不同事务T2对r上任一类型的锁的请求。相反，事务T2必须等待事务T1释放对行r的锁定。

InnoDB为了支持多种粒度锁定，允许行锁和表锁并存。

InnoDB 还有两种内部使用的意向锁（Intention Locks）意向锁是表级锁，指示事务稍后对表中的行需要哪种类型的锁（共享锁或排他锁）。有两种类型的意图锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

InnoDB行锁模式兼容性列表：

如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁除了全表请求（例如LOCK TABLES ... WRITE）以外，不阻止任何其他内容。意图锁定的主要目的是表明有人正在锁定表中的行，或者打算锁定表中的行。

意向锁是InnoDB自动加的，不需要用户干预。对于 UPDATE、DELETE 和 INSERT 语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

• 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
• 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

当使用SELECT...FOR UPDATE加锁后再更新记录，出现如表20-8所示的情况。

3.3 InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

(1)在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。

加锁是给索引项加锁。现在T1会话通过索引查询并加锁，那么此会话中获得行锁，如果T2会话也通过索引查询并加锁，若不是同一条记录则不会BLOCK，否则BLOCK，若获得锁也是行锁；若果T2会话不是通过索引查询，如果加锁肯定是加表锁，但是此时T1会话持有IS锁或者IX锁，根据意向锁的性质，不能再次为其加表锁，所以此时会话就会出现等待。

(2)由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

演示时，一定要关闭自动提交模式

(3)当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

3.4 Record|Gap|Records Locks

这地方，我感觉《深入浅出MySQL》书中讲解的不是很好，大家可以网上看官方文档，或者搜索资料。

InnoDB 存储引擎有三种行锁的算法，其分别是：

• Record Lock: 单个行记录上的锁
  记录锁定是对索引记录的锁定。例如，从t WHERE c1 = 10 FOR UPDATE中选择SELECT c1；防止任何其他事务插入，更新或删除t.c1值为10的行。记录锁始终锁定索引记录，即使没有定义索引的表也是如此。在这种情况下，InnoDB创建一个隐藏的聚集索引并将该索引用于记录锁定。
• Gap Lock: 间隙锁，锁定一个范围，但不包含记录本身
• Next-Key 锁: Gap Lock + Record Lock，锁定一个范围，并且会锁定记录本身

---

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

假如 emp 表中只有 101 条记录，其 empid 的值分别是 1,2,...,100,101，下面的 SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁，也会对empid 大于 101（这些记录并不存在）的“间隙”加锁。

InnoDB 使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了 empid 大于 100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。

InnoDB 除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB 也会使用间隙锁！

3.5 恢复和复制的需要，对 InnoDB 锁机制的影响

MySQL 通过 BINLOG 录执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句，并由此实现 MySQL 数据库的恢复和主从复制。

MySQL恢复机制的特点：

1. MySQL 的恢复是 SQL 语句级的，也就是重新执行 BINLOG 中的 SQL 语句。
2. MySQL 的 Binlog 是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。

MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了 ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。