MySQL之锁介绍

什么是锁

锁是计算机协调多个进程或线程并发访问某一资源的机制；

MySQL锁类型

MySQL不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式实现了锁机制，服务器层完全不了解存储引擎的锁实现：

• MyISAM和MEMORY存储引擎采用表级锁（table-level locking）；
• BDB存储引擎采用页面锁（page-level locking），但也支持表级锁；
• InnoDB存储引擎采用行级锁（row-level locking），但也支持表级锁；

默认情况下，表锁和行锁都是自动获得的，不需要用户使用额外的命令；但也支持用户显式进行锁表或事务控制；

不同锁粒度比较：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突概率最高，并发度最低；
  • 一次性同时获取所有需要的锁并按相同顺序获取表锁来避免死锁；
  • 适合已查询为主，并发用户少，只有少量按索引条件更新数据的应用，如WEB应用；
• 行级锁：开销大，加锁慢；容易出现死锁；锁定粒度小，发生锁冲突概率低，并发度高；
  • 最大程度支持并发，同时带来了最大的锁性能开销；
  • InnoDB中，除单个SQL事务外，锁是逐步获得的，这也导致容易出现死锁；
  • 适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线式服务处理系统(OLTP)；
• 页面锁：开销和加锁时间介于表级锁与行级锁之间；会出现死锁；锁粒度介于表级锁与行级锁之间，并发度一般；

全局锁

全局锁又叫FTWRL（Flush tables with read lock），全局锁的使用场景一般为全库逻辑备份，让数据库可以恢复到最近一段时间之类的某个时刻，这对数据安全，对系统本身非常重要；通过FTWRL，让整个库处于只读状态，其实是基于最近的全量数据备份+备份开始的Binlog操作；使用全局锁时，所有写相关的操作都会处于阻塞等待状态，阻塞的操作包括：

• 数据修改语句DML：insert、delete、update语句；
• 数据定义语句DDL：alter table、create table、drop table等；
• 事务相关操作：开启 事务、提交事务、回滚事务等；
• 主从同步：从无无法拉取主库Binlog，增加主从同步延迟；

表级锁

表级锁包含表锁、元数据锁（Meta Data Lock，简称MDL）

表锁

基于历史原因，在没有InnoDB引擎前表锁就是最小粒度的锁，索引相关引擎都会直接使用表锁；表锁本身是互斥锁，加锁语法为lock tables ... read/write，需加锁线程为其解锁或客户端异常自动解锁，解锁语法为unlock tables；

MyISAM引擎在执行update等语句时会自动添加表锁；

InnoDB在开启事务并且隔离级别为串行化时也会加表锁；

个别情况下InnoDB的行级锁会退化为表锁；

元数据锁

MySQL5.5引入了元数据锁，元数据锁分为读锁和写锁，可以理解成类似Java中的ReentrantReadWriteLock中的ReadLock和WriteLock；读锁与读锁之间可共享，但读锁与写锁、写锁与写锁之间互斥；DML语句都会先获取MDL读锁，DDL语句都会先获取MDL写锁；MDL读锁与写锁都是自动获取与释放的；

高并发场景下，对表元数据进行修改或添加索引等操作可能会导致整个数据库实例宕机；优化方案为：

1. 对元数据操作设置超时参数；
2. 手动kill长事务线程；

行级锁

行级锁包括行锁、临键锁和间隙锁；

行锁

行锁就是所在主键索引的B+树上，也就是一行记录，行锁本身是互斥锁；在InnoDB引擎是服务中，行锁是在需要的时候才加上，但并不是不需要马上就会释放，而是等到事务结束时才能释放，即两阶段锁协议；基于两阶段锁协议，如果事务中需要锁多行，需要把最可能造成锁冲突、最可能影响并发度的锁尽量后释放；

临键锁

临键锁（Next-key Lock）是一个前开后闭的区间；

间隙锁

间隙锁（Gap Lock）是一个前开后开的区间；

举例说明

当前数据库主键索引中有：1，3，5，8四条数据，分别拥有的锁为：

行锁：1，3，5，8

临键锁：(-∞，1]，(1,3]，(3,5]，(5,8]，(8,+∞]

间隙锁：(-∞，1)，(1,3)，(3,5)，(5,8)，(8,+∞)

事务

事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行；

事务特性

• 原子性：事务操作不可分割；
• 一致性：事务执行前后数据完整性保持一致；
• 隔离性：一个事务执行过程中，不应该收到其它事务干扰；
• 持久性：事务一旦结束，数据就持久化到数据库或其他存储介质中；

默认情况下，MySQL每条SQL都是一个单独的事务；

事务并发问题

• 脏读（Dirty Reads）：一个事务正在进行操作时，在这个事务完成并提交前，其操作数据处于不一致状态；这时，其它事务读取对应数据时，如果不加控制，就会读取了这些“脏”了的数据，并据此做进一步处理，这种现场被称为脏读；
• 不可重复读（Non-Repeatable Reads）：一个事务读取某些数据，在它结束读之前，另一个事务可能完成了对数据行的更改；当第一个事务试图再次读取这些数据时，服务回返回被其他事务修改后的结果；
• 幻读（Phantom Reads）：一个事务按相同条件重新读取以前检索过的数据，却发现其它事务插入了满足条件的新数据，这种现象称为幻读；

事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交(read-uncommitted)	是	是	是
不可重复读(read-committed)	否	是	是
可重复读(repeatable-read)	否	否	是
串行化(serializable)	否	否	否

MySQL默认的事务隔离级别为repeatable-read

• 读未提交：事务可以读到其他事务未提交的数据，此时若A事务读取到B事务未提交的修改，然后B事务回滚就会产生脏读；
• 不可重复读：事务只能读到其他事务已提交的数据，不会产生脏读，但若事务B提交在A的两次查询间就会产生不可重复读；
• 可重复读：可重复读的隔离级别使用了MVCC机制，A事务中读取的是快照版本，而非最新版本；B事务的更新是创建了一个新版本来更新，不同事务的读和写是分离的；
• 串行化：MySQL中事务隔离级别为Serializable时会锁表，因此不会出现幻读的情况，但这种隔离级别并发性降低；

事务加锁方式

• 一次性锁协议：事物开始时，就一次性申请所有的锁，之后不在申请任何锁，如果其中某个锁不可用，则整个申请就会失败，事务不会执行，在事务尾端，一次性释放所有的锁；一次性锁协议不会产生死锁问题，但事务的并发度不高；
• 两阶段锁协议：整个事务分为两个阶段，前一个阶段为加锁，后一个阶段为解锁；在加锁阶段，事务只能加锁，也可以操作数据，但不能解锁，知道事务释放第一个锁，就进入解锁阶段，此过程中事务只能解锁，也可以操作数据，不能再加锁；两阶段锁协议是的事务具有较高的并发度，因为解锁不必发生在事务结尾，它的不足是没有解决死锁问题，因为在加锁阶段没有顺序要求；例如两个事务分别申请了A，B锁，接着又申请了对方的锁，此时将进入死锁状态；

MyISAM表锁

MyISAM表的读操作与写操作之间，以及写操作之间是串行的；当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作，其它线程的读写操作都会等待，直到锁被释放为止；

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等待的获取锁请求，然后再给读取锁队列中等待的获取锁请求；

这正是MyISAM表不太适合有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获取读锁，从而一直阻塞；同时，一些需要长时间运行的查询操作，也会使写线程“饿死”，应用中应尽量避免出现长时间运行的查询操作；

可以通过参数调整读锁与写锁优先级：

• 指定启动参数low-priority-updates使MyISAM引擎默认给予读请求以更高优先级；
• 通过执行命令set low_priority_updates=1，使该session发出的更新请求优先级降低；
• 通过指定insert、update、delete语句的low_priority属性，降低对应语句的优先级；
• 通过系统参数max_write_lock_count设置一个恰当的值，当一个表的读锁达到这个值后，MySQL就暂时将写锁请求优先级降低，给读锁一定获取锁的机会；

MyISAM加锁方法

MyISAM在执行查询语句前，会自动给涉及的表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程不需要用于干预；因此，用户一般不用直接使用lock table命令给MyISAM表显式加锁；

在自动加锁的情况下，MyISAM总是一次获取SQL语句所需要的所有锁，这也正是MyISAM表不会出现死锁的原因；

MyISAM存储引擎支持并发插入，以减少给定表的读和写操作之间的争用：

如果MyISAM表在数据文件中间没有空闲块，则行始终插入数据文件的末尾；这种情况下，可以自由混合并发使用MyISAM表的insert和select语句而不需要加锁 — 可以在其他线程进行读操作的时候，同时将行插入到MyISAM表中；文件中间的空闲块可能是从表格中间删除或更新的行而产生的；如果文件中间有空闲块，则并发插入会被禁用，当所有空闲块都填充有新数据时，又会自动重新启用；该功能受MySQL的concurrent_insert系统变量控制；

使用lock tables可显式获取表锁，则可以请求read local锁而不是read锁，以便在锁定表时，其它会话可以使用并发插入；

• concurrent_insert设置为0时，不允许并发插入；
• concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表对应数据文件不存在空闲块），MyISAM允许一个线程读表时，另一个线程从表尾插入记录；MySQL默认配置；
• concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾插入记录；

InnoDB事务

InnoDB事务隔离

在MVCC并发控制中，读操作可分为两类：快照读（snapshot read）与当前读（current read）。

• 快照读，读取的是记录的可见版本，不用加锁；
• 当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录；

MySQL/InnoDB定义了4种隔离级别：

• Read Uncommitted

  可以读取未提交记录；

• Read Committed（RC）

  当前读操作RC格力级别保证对读取到的记录加锁，存在幻读现象；使用MVCC，读取数据时会读取自身版本和最新版本，以最新版本为主，可以读已提交记录，存在不可重复读现象；

• Repeatable Read（RR）

  当前读操作PR隔离级别保证对读取到的记录加锁，同时保证对读取的范围加锁，新的满足条件的记录不能插入间隙锁，不存在幻读现象；使用MVCC保存两个事务操作的数据互相隔离，不存在不可重复读现象；

• Serializable

  从MVCC并发控制退化为基于锁的并发控制；不区别快照读与当前读，所有读操作均为当前读，读加读锁，写加写锁；

Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL.InnoDB下不建议使用；

InnoDB下MVCC控制

MVCC是一种多版本并发控制机制；锁机制可以控制并发操作，但是其系统开销较大，而MVCC可以在大多数情况下代替行级锁，使用MVCC，能降低其系统开销；

MVCC是通过保存数据在某个时间点的快照来实现的；不同存储引擎的MVCC、不同存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制；

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的，这两个列，分别保存了该行的创建时间，一个保存该行的删除时间；这里存储的并不是实际的时间，而是系统版本号，每开始一个新的事务，系统版本号都会自动递增，事务开始时可的系统版本号会作为事务的ID；

• INSERT

  InnoDB为新插入的每一行保存当前系统版本号作为版本号；

• UPDATE

  InnoDB执行Update，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事物ID到要Update行的删除时间；

• DELETE

  InnoDB会为删除的每一行保存当前系统的版本号作为删除标识；

• SELECT

  InnoDB会检索每行记录，需要同时满足以下条件：

  • InnoDB只会查找版本早于当前事务版本的数据行，确保事务读取的行，要么在事务开始前已经存在，要么是事务自身插入或修改过的；
  • 行的删除版本要么未定义要么大于当前事务版本号，这可以确保事务读取到的行，在事务开始之前未被删除；

InnoDB事务锁

锁模式

• 共享锁(S)：又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其它事务只能再对A添加S锁，而不能加X锁，直到事务T释放A上的S锁；这保证了其它事务可以读A，但在T释放A上的S锁之前不能对A做任何修改；
• 排它锁(X)：又称写锁，若事务T读数据对象A加上X锁，则事务T既可以读A又可以写A，其它事务不能再对A添加任何锁，直到事务T释放A上的锁；这保证了其它事务在T释放A上的锁之前不能再读取和修改A；

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

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

意向锁仅仅用于表锁和行锁的共存使用；如果我们的操作仅仅涉及行锁，那么意向锁不会对我们的操作产生任何影响；在任一操作给表A的一行记录加锁前，首先要给该表加意向锁，如果获得了意向锁，然后才会加行锁，并在加行锁时判断是否冲突；如果现在有一个操作要获得表A的表锁，由于意向锁的存在，表锁获取会失败（如果没有意向锁的存在，加表锁之前可能要遍历整个聚簇索引，判断是否有行锁存在，如果没有行锁才能加表锁）；

同理，如果某一操作已经获得了表A的表锁，那么另一操作获得行锁之前，首先会检查是否可以获得意向锁，并在获得意向锁失败后，等待表锁操作的完成；

1. 意向锁是表级锁，但是却表示事务正在读或写某一行记录；
2. 意向锁之间不会冲突，因为意向锁仅仅代表要对某行记录进行操作，在家行锁时，会判断是否冲突；
3. 意向锁是InnoDB自动加的，不需要用户干预；

锁类型

• 间隙锁（Gap Lock）：只锁间隙，表现为锁住一个区间（区间为开区间，也就是不包括边界值）；

• 记录锁（Record Lock）：只锁记录，表现为仅锁单独的一行记录；

• Next-key锁，同时锁住记录与间隙，从实现的角度为Record Lock + Gap Lock，而且两种锁有可能只成功一个，所以Next-key是半开半闭区间，且是下界开，上界闭；一张表中的Next-key锁包括：（负无穷大,最小的第一条记录]，(记录行间区间]，(最大的最后一条记录,正无穷大)；

• 插入意图锁（Insert Intention Lock）：插入操作时使用的锁；在代码中，插入意图锁实际上是Gap锁上加了一个LOCK_INSERT_INTENTION标记；也就是说insert语句会对插入的行加一个X记录锁，但是在插入这个行的过程之前，会设置一个Insert Intention的Gap锁，叫做Insert Intention锁；

• 乐观锁：总是认为不会产生并发问题，每次读取数据的时候总认为不会有其它线程对数据进行修改，因此不会上锁；但是在更新时会判断其它线程在这之前是否有对数据进行修改，一般会使用版本号机制或CAS操作实现；

  • 版本号方式：

    一般在数据表中加上数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值自增1；当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功；

• 悲观锁：总是假设最坏情况，每次读取数据时总认为有其它线程会修改数据，所以总是会加锁（读锁、写锁、行锁等），当其它线程想要访问数据时，都需要阻塞挂起；可以依靠数据库实现，如行锁、读锁和写锁等，都是在操作之前加锁；

一致性非锁定读

InnoDB使用MVCC来实现一致性非锁定读，在read-committed和repeatable-read两种事务隔离级别下使用，且效果不同，具体如下：

• read-committed

  在读已提交的隔离级别下，事务在一致性非锁定读始终读取当前最新的数据快照，即当其它事务提交更新后快照更新也会读取最新的版本，也就是出现不可重复读；

• repeatable-read

  在可重复读的隔离级别下，事务始终读取事物开始时的快照版本；

一致性锁定读

一致性锁定读有两种实现方式，一种是加X锁，一种是加S锁：

• X锁：通过select ... for update方式显式添加X锁读取数据；
• S锁：通过select ... lock in share mode方式显式添加S锁读取数据；

自增长与锁

innodb_autoinc_lock_mode有3种模式：0，1，2

• 0：涉及auto-increment列的插入语句加的表级AUTO-INC锁，只有插入执行结束后才会释放锁，即事务在进行插入时获取自增长值时先加锁，后插入，插入完释放；
• 1：对于可以事先确定插入行数的语句（包括单行和多行插入），使用互斥量操作自增值，分配连续的确定的auto-increment值，对于插入行数不确定的插入语句仍使用表级AUTO-INC锁；这种模式下，事务回滚，auto-increment值不会回滚，即自增列内容会不连续；
• 2：对于所有的插入操作使用互斥量操作自增值，来一个插入分配一个auto-increment值，此时一个批量插入的自增长值就可能不连续，且在SQL语句级的主从同步可能会出现问题；

锁升级

InnoDB行锁是通过给索引上的索引项加锁来实现，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现；InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁；

InnoDB目前处理死锁的方法是：将持有最少行级排它锁的事务回滚；如果是因为死锁引起的回滚，可以考虑在应用中重新执行；

显式锁与隐式锁

• 显式锁：某些特殊情况需要开发人员手动进行加锁、解锁，这种使用方式被称为显示锁；
• 隐式锁：当多个线程并发访问同一个数据的时候，为了保证数据一致性，数据库管理系统会自动为该数据加锁、解锁，这种机制称为隐式锁；

悲观锁与乐观锁

乐观锁

乐观锁（Optimistic Lock）：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性；乐观锁不能解决脏读问题；

乐观锁，顾名思义，就是乐观的认为每次取数据的时候不会被其它进程修改，所以不用上锁，但在更新的时候会判断在此期间是否有其它进程更新这个数据，可以使用版本号等机制实现；乐观锁适用于多读的应用类型，可以提高吞吐量，像数据库提供类似于write_condition机制的其实都是提供的乐观锁；

悲观锁

悲观锁（Pessimistic Lock）：假定肯定会发生冲突，屏蔽一些可能违反数据完整性的操作；

悲观锁，顾名思义，就是悲观的认为每次获取数据的时候都会被其他进程修改，所以每次获取数据的时候都会上锁，其它进程获取数据会一直阻塞到当前进程释放锁；传统的关系型数据库里边用到了很多这种锁机制，比如行锁、表锁、读锁、写锁等，都是在操作之前上锁；

死锁

通常来说，死锁都是应用设计的问题，通过调整业务流程，数据库对象设计、事务大小以及访问数据库SQL等，绝大部分死锁都可以避免；避免死锁的方法有：

• 在应用中，不同程序并发存取多个表时，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会；
• 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，可以大大降低死锁出现的可能；
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排它锁，而非先申请共享锁，更新时再申请排它锁；因为在申请排它锁时，可能已经有其它线程申请了相同记录的共享锁，从而造成锁冲突甚至死锁；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；

如果出现死锁，可以用show innodb status命令来确定最后一个死锁产生的原因；返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待的锁以及被回滚的事务等；

死锁的发生与否，并不在于事务中有多少条SQL，死锁的关键在于：两个（或以上）Session加锁的顺序不一致；

自增锁是语句级的锁，如果当前事务先获取锁，却后执行结束，在从库按语句复制的话，会出现ID不一致；

锁性能优化

• 尽量使用较低的隔离级别；
• 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小失误发生锁冲突的几率也更小；
• 给记录集显示加锁时，做好一次性请求足够级别的锁；比如要修改数据的话，最好直接申请排它锁，而不是先申请共享锁，修改时在申请排它锁，这容易导致死锁；
• 不同的程序访问一组表时，应尽量约定以相同顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行，从而减少死锁的机会；
• 尽量用相等条件访问数据，避免间隙锁对并发插入的影响；
• 不要申请超过实际需要的锁级别；
• 除非必须，查询时不要显式加锁；MySQL的MVCC可以实现事务中的查询不加锁，优化事务性能；MVCC只在committed read和repeatable read两种级别下工作；
• 对于一些特定事务，可以使用表锁来提高处理速度，减少死锁可能；