【MySQL】（四）MyISAM及InnoDB中的锁

1.锁的概述

1.1.锁是什么？

除了CPU、内存、I/O等传统资源，数据库中的数据也是一种共享资源，锁是一种协调多个线程并发访问某一贡献资源的机制。

1.2.锁的作用

解决并发冲突导致的资源竞争问题。

1.3.MySQL中锁的种类

分为表级锁、页级锁、行级锁。

• 表级锁加锁开销最小，加锁最快，但锁的粒度最大，并发能力最低，不会死锁。
• 行级锁加锁开销最大，加锁最慢，但锁的粒度最小，并发能力最高，会死锁。
• 页级锁介于两者之间，会死锁。

在MySQL的引擎中，MyISAM和MEMORY支持表级锁，BDB支持页面锁，而InnoDB既支持表级锁也支持行级锁。

2.MyISAM表锁

2.1.表锁的锁模式

分为两种，表共享读锁和表独占写锁。
其中读锁与读锁可以兼容，读锁也写锁不兼容，写锁不管与读锁还是与写锁都不兼容。
也就是说，某个session在对一张表做读操作时，加的是读锁，不会阻塞另一个请求做读操作，但会阻塞另一个session的写操作（包括INSERT、UPDATE、DELETE等）。
同理，某个session在做写操作时，加的是写锁，此时会阻塞另一个session的读写操作。

2.2.如何加表锁

会在执行脚本时自动加锁，当然也可以手动加锁，脚本如下：

-- 读锁
lock table xxx read;
-- 写锁
lock table xxx write;
-- 一次对多张表加锁
lock tables table1,table2 read;
-- 解锁
unlock tables;

需要注意的是，当一个session给表加上读锁后，这个session中只能查询已锁定的表中的数据，不能查询其它表的数据。

2.3.死锁问题

MyISAM表锁不会出现死锁，这是MyISAM表锁中特殊的锁定机制实现的。
首先，造成死锁最直接、最常见的原因是不同请求之前访问相同资源的顺序不一致，互相持有对方需要的锁，造成循环等待。
而在MyISAM表锁加锁时，会一次性获得待执行的SQL语句所需要的的全部锁，不可能存在循环等待问题，故而不会死锁。

2.4.并发插入

MyISAM中读锁和写锁是互斥的，也就是说表数据的读取和插入是串行的。但我们实际开发中可以使用MyISAM的并发插入机制在一定程度上解决查询和插入的锁争用问题。

2.4.1.并发插入参数设置

修改系统变量concurrent_insert，值有3个，分别为0、1、2。MySQL默认为1

0：不允许并发插入。
1：MyISAM表中没有空洞，允许一个请求在读表的同时，另一个请求可以在表尾插入数据。
2：不管有没有空洞，都可以并发插入

在开发和实际生产中可以将concurrent_insert设置为2。

set global concurrent_insert = 2;

需要注意的是，手动加锁时，要让并发插入生效，需要在加锁语句后加上local，如：

lock table xxx read local;

2.5.MyISAM的锁调度

前边说到了MyISAM的插入和读取是串行的，但是MySQL默认配置下，MyISAM的调度是写锁优先，也就是说同一个时间两个请求分别请求读锁和写锁，一定是写请求先获得写锁。甚至读请求和写请求一前一后，写请求也会插队插入到读请求之前。

MySQL默认配置下，如果程序中存在大量的写请求的时候，读请求很难获取到读锁，可能导致读阻塞。此时可通过配置启动参数low_priority来降低写请求的优先级。

但是写请求的的优先级过低，又会导致写堵塞，特别是一些耗时的查询操作，可能会导致写请求超时。

所以在读多写少时，可以选择MyISAM引擎，如果存在大量写请求的表，最好选用InnoDB引擎，使用行级锁。

3.InnoDB中的锁

InnoDB支持表锁和行锁，同时支持事务，这两个功能的实现带来了更多复杂性。

事务相关链接：
事务及MVCC原理

3.1.锁类型

InnoDB中的实现了两种行锁和两种表锁，其中两种表锁是意向锁。除此之外还有MySQL本身就支持的共享表锁和排他表锁。

行锁：

• 共享锁(S锁)：允许一个事务对某一行（或多行）加锁，阻止其他事务获取相同数据集的排他锁。
• 排他锁(X锁)：允许获取排他锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排他锁。

表锁：

• 意向共享锁(IS锁)：事务在加行共享锁之前，会先获取该表的意向共享锁。
• 意向排他锁(IX锁)：事务在加行排他锁之前，会先获取该表的意向排他锁。

3.1.1.加锁方式

意向锁由InnoDB自动加上，X锁会在执行INSERT、UPDATE、DELETE时自动加上，而普通的SELECT查询不会加上任何锁。
也可以使用下面的SQL显示加锁：

-- 行锁
select * from table_name ... lock in share mode;
select * from table_name ... for update;
-- 锁的释放，事务提交或回滚后就会释放锁
commit;
rollback;

如何加表锁可以参考2.2

3.1.2.各类锁的作用

共享锁：主要是使用在某些准确的数据查询，避免其他事务对需要查询的数据做了写操作导致数据不准确。
排他锁：避免MySQL的并发插入引起的数据安全问题。
意向锁：减少加锁开销，提高加锁效率。

3.1.2.1.意向锁存在的意义

现在有两个事务，A和B。
此时事务A锁住了一行数据，让其他事务不能操作这行数据。
然后事务B对同一张表加表锁，让表里的每一条数据都可以被事务B操作。但是事务A获取了某一行数据的行锁，所以会出现锁冲突。

MySQL如何判断锁冲突呢？

• 判断当前表是否已被其它事务获取了表锁。
• 遍历判断表中的每一行数据是否已经被其它事务锁住。

显然上面的第二种遍历判断的方式效率不高，于是在InnoDB中引入了意向锁。
事务A在给某一行数据加上行锁的时候，同时对整张表加上意向锁。
事务B在给表加表锁前，先判断是否其它事务已经加上了意向表锁，如果有则直接阻塞，不再遍历每行数据。

需要注意的是：

• 意向锁与意向锁之间是互相兼容的。
• 意向共享锁与共享表锁兼容，与排他表锁互斥。
• 意向排它锁与共享表锁、排他表锁都互斥。

3.2.InnoDB行锁的实现方式

InnoDB行锁是通过给索引加锁实现的，如果不通过索引条件检索数据，InnoDB会给表中的所有记录加锁。
这样的特性，在实际使用中如果不注意的话，可能会导致大量的锁冲突。

3.2.1.行锁的3种锁算法

• 记录锁(Rrcord Lock)：对唯一性索引（主键索引或唯一索引）做等值查询，如果精确的匹配到了一条记录，则锁住这条记录的索引。
• 间隙锁(Gap Lock)：对唯一性或普通索引查询的单条记录不存在，则会对此查询条件两端的记录中间的间隙加锁；或精确命中普通索引的一行记录，则会对这行记录两端的间隙及其本身加锁，唯一索引的加锁范围是一个左开右开的区间，普通索引的加锁范围是一个左闭右开的区间。
• 临键锁(Next-key Lock)：对索引做范围查询，包含记录和区间，对范围内的记录和区间加锁的同时，会对范围内最右一个记录的右侧的区间及右侧的第一个记录加锁。是一个左开右闭的区间。

---

注意点：

• 如果锁住的不是主键索引而是辅助索引，根据InnoDB的索引查询规则，会把这一个行记录的辅助索引对应的主键索引同时锁住。
• 间隙锁和间隙锁之间互相不会冲突，只会阻塞在加锁的范围内插入数据。
• 临键锁要锁住最右侧记录的右侧的左开右闭的区间，是为了解决幻读问题。
• 间隙锁和临键锁只在RR隔离级别中存在，RC级别中除了外键和唯一键的检测外都使用记录锁。
• 如果索引是字符类型，会使用ASCII码排序。

InnoDB中各种锁的验证

3.3.死锁

3.3.1.造成死锁的原因

• 排他锁之间是互斥的，并且一个事务获取了锁，另一个事务就只能等待锁。
• 两个事务时间形成了锁的环路等待，互相等待对方先释放锁。

3.3.2.如何避免死锁

• 按照顺序访问数据库。
• 对需要检索的数据进行排序，再做查询。
• 如果要做写操作，直接申请写锁，不要先申请共享锁，再申请写锁。
• 尽量在查询中命中索引。
• 尽量将大事务拆分为小事务。
• 尽可能的使用等值查询。