MySQL-基础6-锁

	lock	latch
对象	事务	线程
保护	数据库内容	内存数据结构
持续时间	整个事务过程	临界资源
模式	行锁，表锁，意向锁	读写锁，互斥量
死锁	通过waits-for graph、time out等机制进行死锁检测与处理	无死锁检测与处理机制，仅通过应用程序加锁的顺序保证无死锁情况发生
存在于	Lock Manager的哈希表中	每个数据结构的对象中

latch信息可以通过show engine innodb mutex来查看；

lock信息可以通过show engine innodb status/information_schema.innodb_trx/innodb_locks/innodb_lock_waits来查看；

InnoDB中的锁

锁类型

InnoDB实现了2种标准的行级锁：

①共享锁S Lock：允许事务读一行数据；

②排他锁X Lock：允许事务删除或更新一行数据；

如果一个事务T1已经获得了行R的共享锁，那么另外的事务T2可以立即获得行R的共享锁，因为读取并不会改变行R的数据，这种情况叫做锁兼容；

如果另外的事务T3想获得行R的排他锁，则必须等待事务T1和T2释放行R上的共享锁，这种情况称为锁不兼容；

兼容是指对同一行记录的锁的兼容性情况，下面是排他锁与共享锁的兼容性：

	排他锁	共享锁
排他锁	不兼容	不兼容
共享锁	不兼容	兼容

InnoDB还支持多粒度锁定，这种锁定允许事务在行级上的锁、表级上的锁同时存在，为了支持不同粒度上加锁，InnoDB引擎支持一种额外的锁方式，称之为意向锁；意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更新粒度上进行加锁；（多粒度锁定，可以理解为不同范围内加锁，例如表，或者行）

InnoDB支持意向锁设计比较简练，其意向锁就是表级别的锁，目的就是为了在一个事务中揭示下一行将被请求的锁类型，其意向锁分为2种：

①意向共享锁IS Lock：事务想获取一张表中某几行的共享锁；

②意向排他锁IX Lock：事务想获取一张表中某几行的排他锁；

因为InnoDB支持的是行级别的锁，而意向锁是表级别的锁，下面说一下表级意向锁与行级锁的兼容性：

	意向共享锁	意向排他锁	共享锁	排他锁
意向共享锁	兼容	兼容	兼容	不兼容
意向排他锁	兼容	兼容	不兼容	不兼容
共享锁	兼容	不兼容	兼容	不兼容
排他锁	不兼容	不兼容	不兼容	不兼容

可以通过show engine innodb status来查看当前锁请求的信息；从InnoDB 1.0开始，information_schema.innodb_trx/iinnodb_locks/innodb_lock_waits简单监控当前事务并分析可能存在的锁问题；

一致性非锁定读

一致性非锁定读是指InnoDB通过行多版本控制的方式来读取当前执行时间内数据库中行的数据；如果读取的行正在执行delete或update操作，这时读取操作不会因此去等待行上锁的释放，相反的，InnoDB会读取行的一个快照数据；

一致性非锁定读，其中的非锁定读是指不需要等待被访问数据行上排他锁的释放；快照数据是指该行之前版本的数据，其实现是通过undo段来完成，而undo用来在事务中回滚数据，因此快照数据本身没有额外的开销，再加上没有事务会对历史数据进行修改，因此读取快照数据不需要上锁；

非锁定读提高了数据库的并发性，这是InnoDB默认的读取方式，但在不同事务隔离级别下，读取的方式也不同，并不是在每个事务隔离级别下都是采用非锁定的一致性读，同时，即便都使用非锁定的一致性读，对于快照数据的定义也各不相同；

快照数据是当前行数据之前的历史版本，且每行记录可能有多个历史版本，这种技术叫做行多版本技术，由此带来的并发控制，叫做多版本并发控制MVCC；

在事务隔离级别是read committed和repeatable read(InnoDB默认事务隔离级别)下，InnoDB使用非锁定的一致性读，但对快照数据的定义却各不相同：read committed下总是读取被锁定行的最新一份快照数据，而repeatable read下总是读取事务开始时的行数据版本；

一致性锁定读

这里有种情况：在repeatable read模式下，InnoDB的select操作使用一致性非锁定读，但用户想显式的对select进行加锁，来保证数据逻辑的一致性，这就要求InnoDB要支持加锁的语句，InnoDB对于select语句支持2种一致性锁定读操作：

①select ... for update：对读取的行记录添加一个X锁，其他事务就不能对已锁定的行加任何锁；

②select ... lock in share mode：对读取的行记录添加一个共享锁，其他事务可以向被锁定的行加S锁，但如果加排他锁，则需要等待共享锁释放；

注意：select...for update与select...lock in share mode必须在一个事务中，当事务提交后锁就释放，因此在使用这2种select锁定语句时，必须加上begin、start transaction、set autocommit=0；

（通俗来讲就是：外面必须有个事务，然后里面包裹select锁定语句，至于为什么必须是select，那是因为用户想绕开一致性非锁定读）

自增长与锁

自增长是非常常见的一种属性，是很多开发人员首选的主键方式，InnoDB的内存结构中，对每个包含自增长的表都有一个自增长计数器auto-increment counter，当对包含自增长的计数器的表进行insert操作时，这个计数器会被初始化，执行如下语句来获得计数器的值：select max(auto_inc_col) from t for update

插入操作会根据这个自增长的计数器值加1赋予自增长列，这个实现方式叫做auto-inc locking，这种锁采用一种特殊的表锁机制，为了提高插入性能，锁在完成对自增长值插入的SQL语句后立即释放；

虽然auto-inc locking在一定程度上提高了并发插入的效率，但还是存在性能上的问题：①有自增长值的列，其并发插入性能较差，事务必须等待前一个插入完成(是等待前一个插入完成，而不是前一个是事务完成)；②对于insert...select的大数据量的插入会影响插入性能，因为另一个事务中插入会被阻塞；

从MySQL 5.1.22开始，InnoDB提供了一种轻量级互斥量的自增长实现机制，从而提高自增长值插入的性能，并且InnoDB提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数默认值为1；

自增长插入进行如下分类：

插入类型	说明
insert-like	指所有的插入语句，例如insert、replace、insert-select，replace-select，load data等
simple inserts	指能在插入之前就确定插入行数的语句，包括insert、replace等等，注意的是simple inserts不包含insert-on duplicate key update这类SQL语句
bulk inserts	指在插入前不能确定得到插入行数的语句，如insert-select，replace-select，load data等
mixed-mode inserts	指插入中有一部分值是自增长的，有一部分是确定的，也可以是指insert-on duplicate key update这类SQL语句

至于参数innodb_autoinc_lock_mode分析以及各个设置下对自增的影响：

取值范围	说明
0	MySQL 5.1.22版本之前自增长的实现方式，即通过表锁auto-inc locking方式；
1	默认值： ①对于simple inserts，该值会用互斥量mutex去对内存中的计数器进行累加操作； ②对于bulk inserts，还是传统的表锁的auto-inc locking方式，这种配置下，如果不考虑回滚操作，对于自增值列的增长还是连续的，并且在这种方式下，statement-based方式的replication还是能很好地工作，需要注意的是：如果已经使用了auto-inc locking方式产生自增长的值，而这时需要再进行simple inserts操作时，还是需要等待auto-inc locking的释放；
2	该模式下，对所有“insert-like”自增长值的产生都是通过互斥量，而不是auto-inc locking方式，这是性能最高的方式，但存在一个问题：并发插入时，在每次插入时，自增长值可能不是连续的，另外，基于statement-base replication会出现问题，所以使用该模式，任何时候都应该使用row-base replication，这样才能保证最大并发性能以及replication主从数据的一致性；

注意：InnoDB中自增长值列必须是索引，还必须是索引的第一个列，否则会抛出异常；

外键和锁

InnoDB对外键列，如果没有显式地对这个列添加索引，那么InnoDB自动对其加一个索引，避免表锁情况发生；

对于外键值的插入/更新，首先查询父表中的记录，即select父表，但对于父表的select操作，不能使用一致性非锁定读方式，这时应该人为告诉InnoDB使用select-lock in share mode，主动给父表加一个S锁，如果此刻父表已经加了排他锁，子表上的操作会被堵塞；

锁的算法

行锁的3种算法

①record lock：单个行记录上的锁；如果InnoDB表在建立的时候没有设置任何一个索引，则InnoDB会使用隐式主键来进行锁定；

②gap lock：间隙锁，锁定一个范围，但不包含记录本身

③next-key lock：gap lock + record lock，锁定一个范围，且锁定记录本身；InnoDB对于行查询都是采用这种锁定算法，该算法目的就是为了解决Phantom Problem问题；

利用next-key lock锁定算法，锁定的不是单个值，而是一个范围，例如当事务T1通过next-key lock锁定了如下范围[10,11]、[11,13]，然后插入新纪录12时，锁定范围则变成：[10,11]、[11,12]、[12,13]，但当select的索引含有唯一属性时(主键，即聚集索引)，InnoDB则把next-key lock降级为recored lock，即仅仅锁住索引本身，而不是范围；而对于辅助索引则继续使用next-key lock来锁定某个范围；

注意：InnoDB还会对辅助索引下一个键值加上gap lock，因此，gap lock作用就是为了阻止多个事务将记录插入到同一范围内，因为这会导致Phantom Problem问题，用户可以通过以下2种方式来显示关闭gap lock：

①将事务的隔离级别设置为read committed；

②将参数innodb_locks_unsafe_for_binlog=1；

在上述配置下，除了外键约束和唯一性检查依然需要gap lock外，其他情况仅仅使用record lock进行锁定，但上述设置破坏了事务的隔离性，并且对于replication可能会导致主从数据不一致，另外从性能角度看，read committed也不会优于read repeatable；

在InnoDB中，对于insert操作，会检查插入记录的下一条记录是否被锁定，若已经被锁定则不允许查询；

总结：对于唯一键值的锁定，next-key lock降级为record lock，仅仅发生于查询所有的唯一索引列；如果唯一索引是由多个列共同组成，而查询仅仅是查找多个唯一索引列中的其中一个，那么查询其实是range类型查询，而不是point类型查询，所以InnoDB还是使用next-key lock进行锁定；

解决Phantom Problem

Phantom Problem是指在同一个事物下，连续执行2次同样的SQL语句可能导致不同的结果，第2次的SQL语句可能会返回之前不存在的行记录；

在默认事务隔离级别repeatable read下，InnoDB采用next-key lock算法来避免Phantom Rroblem，例如我在执行select * from t where a>2 for update，而数据表t里只有1/2/5，那么InnoDB锁住的不是5这一个值，而是对(2, +∞)这个范围添加了排他锁，因此任何对于这个范围的插入都是不被允许的，都需要排队等待获取锁，因此避免Phantom Problem；

InnoDB默认的事务隔离级别repeatable read采用next-key lock算法，而在read committed隔离级别下采用record lock算法

锁问题

通过锁定机制，可以实现事务的隔离性要求，从而使得事务可以并发工作，提高了并发性能，但锁定机制不是完美的，其会带来下面3种问题，如果能防止这3种问题产生，则不会产生并发异常；

脏读

脏页：数据库实例内存中的页，与磁盘中的页数据不一致，不过数据库系统会负责保持内存与磁盘上的数据一致性，也不会影响数据库实例对外提供服务；

脏数据：事务对缓冲池中行记录的修改，并且还没有被提交commit；如果一个事务读到另外一个事务中未提交的数据则显然违反了数据库的隔离性；

脏读：在不同的事务下，当前事务可以读取到其他事务尚未提交的数据；

脏读发生的条件是需要把事务隔离级别设置为read uncommitted；

不可重复读

不可重复读是指在同一个事务内，多次读取同一数据集合；在当前事务还没有结束时，另外一个事务也访问该同一数据集合，并做了DML操作，导致当前事务中2次读到的数据可能不一致，这样就发生了同一个事务内2次读到的数据不一样的情况，这种情况叫做不可重复读；（通俗理解：同一个事务内同一个SQL执行2次，结果不一样）

脏读是读到了其他事务尚未提交的数据，而不可重复读则是读到了其他事务已经提交的数据，但不可重复度违反了数据库事务一致性要求；

一般来说，不可重复读是可以接受的，毕竟是读到其他事务已经提交的数据，本身不会带来很大的问题，其他数据库厂商与允许不可重复读的出现，但InnoDB默认事务隔离级别read repeatable通过使用next-key lock算法直接避免不可重复读；