深入浅出MySQL读书笔记（二）
1.前言
这篇文章是读书笔记的第二部分，主要内容是MySQL中锁的实现。具体内容如下：
- MyISAM引擎表锁的相关内容
- InnoDB引擎行锁相关内容
- 事务的原子性
- 事务隔离级别
下面逐一介绍。
2.表锁与行锁的对比
MySQL中锁机制实际上是比较简单的，不同的存储引擎支持不同的锁机制。其中MyISAM和MEMORY引擎支持表级锁，BDB引擎支持页面锁和表级锁，InnoDB支持行级锁及表级锁，默认使用行级锁。
目前BDB已经被InnoDB取代，因此页面锁也就没有进一步讨论的必要了。
行级锁：开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高。
表级锁：开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
3.MyISAM表锁
3.1 表锁的两种模式
表锁主要有两种模式：表共享读锁和表共享写锁，锁模式的兼容性如下。
| 读锁 | 写锁 | |
|---|---|---|
| 读锁 | 兼容 | 不兼容 |
| 写锁 | 不兼容 | 不兼容 |
也就是说，读操作是不会阻塞其他用户对同一表的读请求，但是会阻塞对同一张表的写请求；而写操作会阻塞其他用户的读和写操作。
总结来说，读操作与写操作之间，写操作和写操作之间实际上是串行的。
当一个线程获得一个表的写锁后，只有持有锁的线程才能对表进行更新操作，其他线程的读写操作都会等待，知道锁被释放为止。
举例说明：
//session1获得表锁
lock table film_text write;
//session1查询、更新、插入数据均可进行
select film_id, title from film_text where film_id = 1001;
//此时session2查询锁定表，结果将会被阻塞，一直等待
select film_id, title from film_text where film_id = 1001;
//session1释放锁
unlock tables;
//session2此时可以获得查询结果
3.2 如何加表锁
在默认情况下，MyISAM引擎执行每个select语句前，会自动增加读锁，在执行更新操作（包括insert/update/delete等）前，会自动增加写锁，这个过程不需要用户进行手动操作。例子中的手动加锁实际上是为了方便说明问题的本质，实际上，并不需要直接lock tables。
实际使用中，有时也会出现需要自己显示加表锁的情形。例如，有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order_detail，其中记录有各订单每一产品的金额小计subtoal，假设我们需要检查这两个表的金额合计是否相等。
那么，这时我们就需要先一次性获得两张表的读锁，然后再执行统计，否则，再统计第一张表的时候，如果有数据插入第二张表，那么就会出现结果不相符的错误情况。
具体命令如下：
lock tables orders read local, order_detail read local;
select sum(total) from orders;
select sum(subtotal) from order_detail;
unlock tables;
这里有几点需要特别注意：
lock tables时命令最后的local选项，这个的作用就是在满足MyISAM表的并发插入条件的情况下，允许其他用户在表尾并发插入记录，有关并发插入的问题，后面还会具体介绍、- 在
lock tables加锁的时候，必须一次性获得所有需要的锁。具体来说就是在加锁命令后只能够访问加锁的表，不能够访问未加锁的表，这样由于锁都是一次性获得的，也就没有死锁的问题了。 - 在
lock tables加锁的时候不支持锁的升级。这个指的是，如果我们对表加读锁，那么只能执行查询语句，不能执行更新操作。 - 使用
lock tables加锁的时候，不仅要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过SQL语句中相同的别名锁定多少次，否则也会出错。
3.3 并发插入
MyISAM表的读和写操作是串行进行的，但是这时就总体而言的。在一定条件些，MyISAM表也支持查询和插入并发进行。
MyISAM中有一个系统变量concurrent_insert，专门控制并发插入的行为，其值可以为0、1和2。
- 0表示不允许并发插入
- 1表示，如果MyISAM表中没有空洞（空洞指的是表的中间被删除的行），就允许一个进程读取表的时候，另一个进程从表尾插入记录。这也是默认设置。
- 2表示，无论是否存在空洞，都允许在表尾并发插入记录。
下面给出在默认情况下模拟两个进程查询和并发插入的情况
//session1锁定表
lock table film_text read local;
//session1不能更新或插入锁定表，但是可以查询
//session2可以进行插入操作
insert into film_text (film_id, title) values(1002,'Test');
//session2的更新操作将会一直等待
update film_text set title ='Update Test' where film_id = 1001;
//此时session1无法访问session2并发插入的数据
//session1释放锁
unlock tables;
//session1释放锁后，可以查询到session2插入的数据
//session2获得锁，完成更新操作
3.4 MyISAM的锁调度
MyISAM引擎的读锁和写锁是互斥的，读写操作是串行的，那么，一个进程请求某个MyISAM表的读锁的同时，另一个进程也来请求同一个表的写锁，这时那个会优先获得锁呢？
MySQL一般会认为写操作的重要性是高于读操作的，所以上面那种情况下，一般会是写进程获得锁。不仅如此，即使读请求先于写请求，写锁也会插入到读锁之前。
这样的一种设计让我们的MyISAM表不太适合有大量更新操作和查询操作的应用场景，因为，更新操作过多导致查询操作很难获得读锁，会导致永远阻塞。
不过，我们可以通过参数low_priority_updates，low_priority_inserts，low_priority_deletes来降低该语句的优先级。
同时，MySQL也提供了一种折中的办法来调节读写冲突，可以给max_write_lock_count一个合适的值，当读锁达到一定数目的时候，MySQL会暂时将写请求的优先级降级，从而让读进程更容易获得锁。
还有一点就是，一些十分复杂的查询操作也有可能会将写进程“饿死”！因此，我们应该尽量避免出现长时间运行的查询操作，可以通过中间表等将SQL语句分解，减少锁冲突。如果不可避免，那么就在空闲时间运行。
4.InnoDB锁问题
InnoDB与MyISAM最大的不同，就是支持事务和采用行级锁。
4.1 事务的相关内容
-
事务及其ACID属性
事务指的是一组SQL语句组成的逻辑处理单元，事务具有以下四个属性，分别为：
- 原子性（Atomicity）:事务是一个原子操作单元，其对数据的操作，要么全部执行，要么全不执行。
- 一致性（Consistent）：事务开始和完成时，数据必须保证一直状态。这意味着相关的数据规则必须应用与事务的修改，以保证数据的完整性；事务结束时，所有内部数据结构（如B树索引或双向链表）都必须是正确的。
- 隔离性（Isolation）:数据库系统需要提供一定的隔离机制，以保证事务不受外部并发操作的影响。也就是说事务的中间状态是不可以见的。
- 持久性（Durable）：事务完成后，数据的修改是永久性的，即使系统出现故障也不会丢失。
-
并发事务处理带来的问题
- 更新丢失：当两个或多个事务选择同一行，选择的时候，互相都不知道其他事务的存在，就会发生更新丢失问题——最后的更新覆盖了其他事务所做的更新。这个问题可以通过加锁进行避免。当一个事务完成提交之前，另一个事务无法访问同一个文件。
- 脏读：一个事务正在对一条记录做修改，在这个事务没有完成提交前，数据处于一种不一致的状态，这时，另外一个事务来读取同一记录，就会读取了脏数据，
- 不可重复读：一个事务在读取某些数据后的某个时刻，再次读取以前读过的数据，却发现读出的数据已经被另外的事务改变或者被删除了。
- 幻读：一个事务按照相同的查询条件读取以前检索过的数据，却发现其他事务插入了满足条件的新数据。幻读的实际案例就是一个事务无法保证更新操作更新的是自己想要更新的数据，因为更新的同时可能另一个并发事务插入了同样满足更新条件的数据。
-
事务隔离级别
并发事务引发的问题中，更新丢失问题是完全应该避免的，这个的避免一般是由应用程序来对数据进行加锁控制。因此，防止更新丢失应该是应用的责任。
另外三个问题实际上都是数据库读一致性问题，是有数据库提供的事务隔离机制解决的。
数据库实现数据隔离的方式，基本分为两种：
第一种是在读取数据之前对其加锁，防止其他事务对数据修改；第二种是不加锁，通过一定机制生成一个数据请求时间点的一致性数据快照版本，并用这个快照来提供一定级别的一致性读取，这种技术叫做数据多版本并发控制。事务的四种隔离级别
事务的四种隔离，当隔离级别越高，并发实际上越低，因此，我们需要平衡隔离级别和并发之间的矛盾，使用最低的隔离级别完成需要的业务逻辑。
| 一致性 | 脏读 | 不可重复度 | 幻读 | |
|---|---|---|---|---|
| 未提交读(RN) | 最低级别，只能保证不读取物理损坏的数据 | 是 | 是 | 是 |
| 已提交读(RC) | 语句级 | 否 | 是 | 是 |
| 可重复读(RR) | 事务级 | 否 | 否 | 是 |
| 可序列化(Serializable) | 最高级，事务级 | 否 | 否 | 否 |
4.2 行锁模式及加锁方法
InnoDB引擎主要实现了两种行锁。
共享锁（S锁）：允许事务去读取一行，阻止其他事务获取排他锁。
排他锁（X锁）：允许事务更新锁定数据，阻止其他事务获得任何锁。
另外，InnoDB还有两种意向锁，均是表锁。
意向共享锁（IS锁）：事务打算给记录增加共享锁，事务会在获得S锁前先获得IS锁。
意向排他锁（IX锁）：事务打算给数据行加排他锁，事务会在加排他锁前先获得IX锁。
锁的兼容模式如下：
| X | IX | S | IS | |
|---|---|---|---|---|
| X | 冲突 | 冲突 | 冲突 | 冲突 |
| IX | 冲突 | 兼容 | 冲突 | 兼容 |
| S | 冲突 | 冲突 | 兼容 | 兼容 |
| IS | 冲突 | 兼容 | 兼容 | 兼容 |
如果一个事务请求的锁与当前的锁兼容，那么事务申请锁就会成功，如果冲突，就会等待锁释放后才能获得锁。
意向锁是有InnoDB自动增加的，不需要用户干预。对于update、delete及insert语句，InnoDB会自动加X锁，而对于普通的selete语句，不会加锁。事务如果想要获得锁，可以通过以下语句实现：
//获得S锁
select * from table_name where ··· lock in share mode;
//获得X锁
select * from table_name where ··· for update;
S锁的使用场景可以举例一个，当有主从表的情况的时候，我们想要向从表中insert条记录，这时，我们需要对主表中的这条记录加S锁，然后再insert从表，来实现主从表数据一致性，防止其他session删除主表数据，造成主从表数据不一致的情况。
而通常情况下，我们如果只是单纯的查询并不需要加锁，当我们的目的是更新的时候，最好直接获得X锁，以防止发生死锁的情况，因为可能有两个session同时有S锁，两个都想获得X锁，这时就会发生死锁，因为两个Session都不会释放S锁，导致死锁。如果直接获取X锁就不会发生这种情况。
因此，当我们在加锁的时候应该考虑好业务实际场景，尽量避免锁的升级，因为非常有可能发生死锁。
4.3 行锁的实现方法
InnoDB行锁实际上是对索引上的索引项加锁来实现的，没有索引时，通过隐藏的聚簇索引来对记录加锁。行锁有三种实现方式：
- Record lock：对索引行加锁
- Gap lock：间隙锁，对索引项之间的“间隙”、第一条记录前的“间隙”，、最后一条记录后的“间隙”加锁。（这里的间隙实际上指的是不存在的记录，后面会详细介绍）
- Next-key lock: 前两种的组合，对记录及其前面的间隙加锁
这里插个题外话
聚簇索引(clustered index)
有主键时，根据主键创建聚簇索引
没有主键时，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引
如果以上两个都不满足那innodb自己创建一个虚拟的聚集索引
由于InnoDB行锁的实现方式，如果我们的查询没有使用索引，就会导致InnoDB对所有记录加锁，实际上的效果就变成了表锁。
举例说明：
//首先创建表
create table tab_no_index(id int, name varchar(10)) engine=innodb;
//插入数据
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
//首先需要将两个session都设置为不自动提交事务
set autocommit=0;
//session1获得id=1的记录X锁，由于没有索引，实际上把整个表都锁住了
select * from tab_no_index where id = 1 for update;
//session2尝试获取id为2的记录的X锁，失败，一直等待
select * from tab_no_index where id = 2 for update;
上面的例子中，如果我们给表中id列增加索引，那么session2就会成功获得id为2的记录的锁，这时只锁定了符合条件的记录。
这一特性就要求我们在写SQL的时候使用的索引，与上一篇中的内容相结合，索引不仅关系到查询的快慢，还关系到加锁的问题，因此，需要特别注意，以防发生大面积的锁冲突。
1） 不通过索引条件查询InnoDB会锁定表中所有记录
2） 由于MySQL的行锁是通过索引加锁的，索引虽然是访问不同的行，但如果是相同的索引键，也会出现锁冲突。
//表tab_with_index的id字段有索引，name字段没有索引
//id为1的记录有两条，name值分别为1,4
//首先需要将两个session都设置为不自动提交事务
set autocommit=0;
//session1获得锁，通过id上的索引，实际锁定了两条记录
select * from tab_with_index where id = 1 and name = '1' for update;
//这时session2申请和session1的不同记录，但是因为是相同索引，依然需要等待锁释放
select * from tab_with_index where id = 1 and name = '4' for update;
3） 如果不同事务通过不同的索引对相同的行加锁，那么也会发生锁冲突，因为虽然是通过索引加锁，但实际上锁定的是记录。
//表tab_with_index的id字段、name字段均有索引id为1的记录有两条，name值分别为1,4
//首先需要将两个session都设置为不自动提交事务
set autocommit=0;
//session1通过id上索引获得锁，此时两条记录均被锁定
select * from tab_with_index where id = 1 for update;
//session2使用name索引访问记录，name为2的行没有被锁定，可以成功
select * from tab_with_index where name = '2' for update;
//session2使用name索引访问被session1锁定的记录，需要等待
select * from tab_with_index where name = '4' for update;
4.4 Next-Key锁及Gap锁
间隙锁的存在实际上是为了满足事务的隔离级别，防止幻读等情况的出现。
当我们使用相等条件检索数据，请求共享或排他锁的时候，如果数据不存在，InnoDB也会对这个不存在的记录，也就是间隙加锁。如果我们使用范围条件检索，加锁时，也会对不存在的记录加锁，这种锁机制就是Next-Key锁。
举例来说，emp表中只有101条记录，其empid的值分别为1,2，···，100,101，当我们执行下面的SQL时，
select * from emp where empid>100 for update;
这时一个范围条件检索，InnoDB不仅会对记录100和101加锁，同时也会对empid大于101（实际不存在）的间隙加锁。
这种加锁方式一方面是为了防止幻读，以满足隔离级别的要求。对于上面的情况，如果不对empid大于101的间隙加锁，如果其他事务插入了empid大于101的记录，那么当我们进行后续的更新，或者重新查询时，就会发生幻读的情况；另一方面是为了满足恢复和复制的需要，这个会在下一部分详细介绍。
需要说明的是，只有当MySQL的隔离级别是REPEATABLE-READ之上，才会对显示加锁的范围查询情况加间隙锁，RC模式以下实际上只是加普通的行锁。实际上事务隔离级别上RR模式只需要解决不可重复读和脏读问题，但是由于间隙锁的存在，MySQL实际上也解决了幻读的问题，这里可能会有疑问那么SERIALIZABLE级别为何还会存在，其实MySQL的SERIALIZABLE级别采用了读写都加锁的经典模式即读写串行模式。
4.5 恢复和复制对锁的要求
MySQL通过BINLOG记录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现数据库的恢复和主从复制。
MySQL有三种日志格式，分别是基于语句的日志格式SBL、基于行的日志格式RBL和混合日志格式。同时还有四种复制模式：
- 基于SQL语句的复制SBR
- 基于行数据的复制RBR：优点：支持非安全SQL的复制
- 混合复制模式：对安全SQL采用SBR，对非安全SQL采用RBR
- 使用全局事务ID（GTIDS）的复制：主要解决主从同步一致问题
这里主要介绍MySQL对两类特殊SQL的特殊处理。首先介绍以下情况。
insert into target_tab select * from source_tab where···
create table new_tab···select··· from source_tab where···
这两类语句实际上并没有对原表进行更新操作，只是简单的读取原表的数据，就是一个普通的select语句，用一致性读就可以实现。在Oracle中是通过MVCC技术实现的多版本数据库实现的，不需要对原表加锁。InnoDB实际也实现了多版本数据库，但是，这种操作下，InnoDB对原表加了共享锁，并没有使用多版本一致性读技术。
这里这样做的原因是为了保证恢复和复制数据的正确性。考虑以下这种情况，在没有加锁的情况下，在上述语句执行的过程中，另一个事务更新了原表中的数据，并提交了事务，这时就可能导致数据恢复出错，分析BINLOG日志，会发现更新操作被写到了insert...select语句之前，如果使用这个日志恢复数据，那么得到的数据结果实际上是错误的，如果进行复制，就会导致主从数据库不一致的问题。
了解了上面的情况，也就不难理解这时加锁的原因。这里还需要注意，如果上述select语句是范围查询，还会加Next-Key锁。
通过将innodb_locks_unsafe_for_binlog的值设置为“on”，可以强制MySQL使用多版本一致性读，但是代价就是无法使用BINLOG正确的恢复或复制数据，不推荐使用这种方式。
这两种SQL语句被称为不确定的SQL，属于“Unsafe SQL”,需要尽量避免使用。
如果要实现这种业务逻辑，可以考虑以下两种方式：
- 通过
select * from source_tab···into outfile和load data infile···语句组间接实现 - 使用基于行的BINLOG格式和基于行的数据的复制
4.6 表锁的使用
在InnoDB引擎中的大部分表，我们都是使用 行级锁，但是在一些十分特殊的情形下，我们可以考虑使用表锁。
- 事务需要更新大部分或全部数据
- 事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚，这种情况下，可以考虑一次性锁定事务涉及的表
这两种情形如果过多，就需要考虑存储引擎是否选择有误。
InnoDB中使用表锁需要注意两点：
- lock tables给InnoDB加表级锁时，这个锁是由MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1(默认)时，InnoDB才能知道加了表锁，Server也才能知道加了行锁，这样，InnoDB才能处理涉及表级锁的死锁，否则将无法自动处理。
- 使用lock tables加锁时，必须将autocommit设为0，否则不会加表锁，事务结束前不要使用unlock tables释放锁，该命令隐含提交事务，应该自己提交事务或回滚后再使用unlock tables命令释放锁。
set autocommit = 0;
lock tables t1 write, t2 read, ···;
//do something...
commit;// rollback
unlock tables;
4.7 死锁问题
由于InnoDB的锁时逐渐获得的，所以有可能发生死锁。
考虑如下情况，session1有表A的X锁，session2有表B的X锁，此时，session1需要获得表B的X锁，session2需要获得表A的X锁，这时就发生了死锁的情况。
一般情况下，InnoDB可以自动检测到死锁，并使一个事务释放锁并回滚，另一个事务可以获得锁，完成事务。但是，涉及到外部锁或表锁的时候，可能无法自动检测，这时，我们可以通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要注意，这个锁并不只是用来解决死锁问题，如果并发较高的情况下，大量事务无法获得锁挂起，会占用大量资源，影响性能，通过设置这个参数，也可以避免这种问题。
下面介绍一些避免死锁的方法。
- 在程序中不同程序并发存取多个表，尽量约定以相同的顺序访问，降低死锁的发生概率。
- 如果要更新记录，直接申请足够级别的锁，例如不要先申请S锁，更新时再申请X锁，这样十分容易死锁
- 在RR级别下，两个线程同时对相同条件的记录加排他锁，当记录不存在的情况下，两者都加锁成功，当两线程试图插入记录时，发生死锁。这种情况将隔离级别改为RC可以避免。
- 多线程同时插入操作的时候可以直接插入，通过捕获主键异常来处理同时插入的死锁问题。