MySQL笔记(6)---锁

1.前言

　　我们都知道在并发的情况下，修改数据时需要添加锁，但是却对数据库锁的工作原理不甚理解，不知道锁的运行机制，也就对数据的安全性无法明白。本章记录MySQL中锁的相关知识。

2.什么是锁

　　锁是数据库系统区别与文件系统的一个关键特性，用于管理对共享资源的并发访问。InnoDB提供了行级别的锁，在数据库内部其他地方也使用了锁。例如：操作LRU列表，为了保证数据一致性就必须有锁了。

　　另一方面，不同的数据库有不同的锁实现方式，SQL语法层确实标准的，所以对锁的认识局限于数据库。对于MyISAM引擎，其锁是表锁设计，并发读没有问题，写就性能差点了。

　　InnoDB存储引擎锁的实现和oracle很类似，提供了一致性的非锁定读、行级锁支持，行锁没有相关额外开销，并可以同时得到并发性和一致性。

3. lock和latch

　　这两个概念经常被搞混，都可以被称为锁，但是含义不同。本章主要关注的是lock。

　　latch一般称为闩锁（轻量级锁），因为其要求锁定的时间必须非常短。若持续时间长，应用性能就会很差。目的是保证并发线程操作临界资源的正确性，并且通常没有死锁检测机制。

　　lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放。有死锁机制。

　　查看latch，可以执行show engine innodb mutex，输出说明如下：

　　　　count:mutex被请求的次数

　　　　spin_waits: spin lock 自旋锁的次数，latch在不能获取锁的时候进行自旋，仍不行才会进入等待状态。

　　　　spin_rounds: 自旋内部循环总次数，每次自旋的内部循环是一个随机数。spin_rounds/spin_waits表示平均每次自旋所需的内部循环次数

　　　　os_waits:表示操作系统等待的次数

　　　　os_yields:进行os_thread_yield唤醒操作的次数

　　　　os_wait_times:操作系统等待的时间，单位是ms

　　查看lock就直观多了，show engine innodb status及information_schema下的表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS来观察锁的信息。

4. InnoDB存储引擎中的锁

4.1 锁的类型

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

　　　　共享锁（S lock)，允许事务读一行数据、

　　　　排他锁（X lock)，允许事务删除或更新一行数据。

　　上面的含义简单解释就是：如果一个事务T1获得了行r的共享锁，另一个事务T2可以获取r的共享锁，因为读操作没有改变数据，这称为锁兼容。但是如果T3想要获取r的排他锁，必须等待t1、t2释放r的共享锁，这就是不兼容。只有共享锁之间兼容，其它情况都不兼容。

　　InnoDB支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称为意向锁。

　　意向锁就是将锁定的对象分为多个层次，意味着事务希望在更细粒度上进行加锁。

　　如果要对行进行加锁，那么势必要牵扯到页和表，因为这两个范围在行之上。如果一个事务对表加了锁，另一个事务要对行加锁怎么办？这就是意向锁的作用。在对下层的行进行加锁前，会对表加上意向锁，也分为读锁和写锁，IS，IX。

　　　　意向共享锁IS: 事务想要获得一张表中某几行的共享锁。

　　　　意向排他锁：事务想要获得一张表中某几行的排他锁。

　　不同S和X的兼容性，任何意向锁之间是兼容的，想想也容易明白，因为是意向没有具体到行，在行上会加上具体的X、S锁，那个时候保证数据也不迟。同样的道理只有IS和S锁是兼容的，IS与X，IX与S等都是不兼容的，毕竟别人锁了整个表的X，所有行都不可靠了，自然不允许添加意向锁，由于S和S锁之间兼容，所以即便是锁了表的S，IS也能兼容。

　　3小节介绍了查看lock信息的方法，这个方法在innodb 1.0开始才能使用，这里表查询结果的含义：

　　　　INNODB_TRX：

　　　　　　trx_id:唯一事务ID

　　　　　　trx_state:当前事务状态

　　　　　　trx_started: 事务开始的时间

　　　　　　trx_requested_lock_id:等待事务的锁ID。如trx_state的状态是LOCK WAIT，那么该值代表当前的事务等待之前事务占用的锁资源id，如果不是wait，这里就是NULL

　　　　　　trx_wait_started:事务等待开始的时间

　　　　　　trx_weight: 事务的权重，反映了一个事务修改和锁住的行数。在innodb中，当发生死锁需要回滚的时候，会选择该值最小的进行回滚。

　　　　　　trx_mysql_thread_id: MySQL中线程ID，SHOW PROCESSLIST显示结果

　　　　　　trx_query： 事务允许的SQL语句

　　　　INNODB_LOCKS：

　　　　　　lock_id: 锁的ID

　　　　　　lock_trx_id:事务ID

　　　　　　lock_mode:锁的模式

　　　　　　lock_type:锁的类型，表锁还是行锁

　　　　　　lock_table:要加锁的表

　　　　　　lock_index:锁住的索引

　　　　　　lock_space:锁对象的space id

　　　　　　lock_page:事务锁定页的数量。若是表锁为null

　　　　　　lock_rec:事务锁定行的数量。表锁为null

　　　　　　lock_data:事务锁定记录的主键值,表锁为null

　　　　INNODB_LOCK_WAITS：

　　　　　　requesting_trx_id: 申请锁资源的事务ID

　　　　　　requesting_lock_id:申请的锁的ID

　　　　　　blocking_trx_id: 阻塞的事务ID

　　　　　　blocking_trx_id: 阻塞的锁ID

4.2 一致性非锁定读

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

　　称为非锁定读就是因为不需要等待X锁的释放，这个和之前介绍的不同。快照指的就是该行之前版本的数据，通过undo段完成的。而undo用来在事务中回滚的数据，因此快照数据本身是没有额外开销的。此外，快照数据不需要上锁，因为没有事务需要对快照进行修改。

　　可以看到，非锁定读的机制极大地提高了数据库的并发性。在InnoDB存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是要注意的是：不同的事务级别，读取的方式是不同的，并不是在每个事务隔离级别下都采取非锁定的一致性读。对锁比较清楚的朋友就应该知道这种方式会产生什么问题。此外，即使都是使用非锁定的一致性读，但是对于快照数据的定义也各不相同。

　　前面说了是多版本并发控制，这也是因为行记录可能有不止一个快照数据，这种技术称为行多版本技术。

　　在事务隔离级别READ COMMITTED和REPEATABLE READ（默认的事务级别）下，InnoDB存储引擎使用非锁定的一致性读。但是，对于非快照数据的定义却不一样。READ COMMITTED事务隔离级别下，总是读取被锁定行的最新一份快照数据。而在REPETABLE READ事务隔离级别下，对于快照数据，总是读取事务开始时的行数据版本。

　　例如一下执行过程：

　　　　时间　　　　　　会话A　　　　　　　　　　　　　　　　会话B

 　　　　1　　　　　　BEGIN

　　　　 2　　　　　　select * from parent where id = 1

　　　　 3　　　　　　　　　　　　　　　　　　　　　　　　　　begin

　　　　 4　　　　　　　　　　　　　　　　　　　　　　　　　　update parent set id = 3 where id = 1

　　　　 5　　　　　　select * from parent where id = 1

　　　　 6　　　　　　　　　　　　　　　　　　　　　　　　　　commit

　　　　 7　　　　　　select * from parent where id = 1

　　　　 8 　　　　　  commit

　　上面过程，在第5步A会话查询的时候，READ COMMITTED和REPETABLE READ级别下，都是一样的答案，因为B事务没有提交，还是最新的版本还是之前的数据。但是B提交之后，出先了两个版本的数据。对于READ COMMITTED而言，其读取的是最新版本的，由于B事务提交了，没有了快照，取出来的为空结果。对于REPETABLE READ而言，是读取事务开始时的行数据，所以还是原来的结果。

　　特别需要注意的是，对于READ COMMITTED事务隔离级别而言，其违反了数据库事务理论的ACID种的I，隔离性的特性。会话B的事务对其产生了影响。

4.3 一致性锁定读

　　前面讲了一致性的非锁定读，默认的就是这种模式的REPETABLE READ事务隔离级别。但是某些情况下，用户需要显示地对数据库读取操作进行加锁，以保证数据逻辑的一致性，所以需要数据库支持加锁语句。这才是我们最初说的两种锁的兼容性的体现，一致性非锁定读虽然提高了性能，但是却带来了数据安全性的问题，对于一般应用问题不大，但是对于重要的应用而言就比较麻烦了。

　　InnoDB支持对SELECT语句两种一致性的锁定读操作：

　　　　SELECT ... FOR UPDATE: 这种是对读取的行记录加上一个X锁，其他事务不能对已锁定的行加任何锁。

　　　　SELECT ... LOCK IN SHARE MODE：这种是对读取的行记录加一个S锁，其他事务可以加S锁，但是加X锁会被阻塞。

　　对于一致性非锁定读，即使读取的行执行了FOR UPDATE，也是可以进行读取的，这个前面就说明过。此外，这些操作必须在一个事务中，当事务提交了，锁也就释放了。

4.4 自增长与锁

　　自增长在数据库中很常见的一个属性，对于每个含有自增长值的表都有一个自增长计数器。但有数据插入时，就是+1赋予这个数据这个字段的值。这个实现方式称为AUTO-INC Locking。这种锁其实是采取一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成自增长值插入的SQL语句后立刻释放了。

　　AUTO-INC Locking的机制虽然在一定程度提高了并发插入效率，但是还是很差，事务必须等待前一个插入的完成。其次，对于INSERT ... SELECT的大量数据的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

　　从MySQL5.1.22版本开始，InnoDB提供了一种轻量级互斥量的自增长实现机制，大大提高了插入的性能。innodb_automic_lock_mode来控制自增长的模式，默认是1.

　　插入类型一般有以下几种：

　　　　insert-like:指所有的插入语句，如INSERT、REPLACE、INSERT ... SELECT、REPLACE .... SELECT、LOAD DATA

　　　　simple inserts: 指能在插入前就确定插入行数的语句。这些语句包括INSERT和REPLACE等

　　　　bulk inserts: 插入前不能确定插入行数的语句，如INSERT... SELECT、REPLACE .... SELECT、LOAD DATA等

　　　　mixed-mode inserts: 插入有一部分的值是自增长的，有一部分是确定的

　　innodb_automic_lock_mode的类型总共有3种，0、1、2,具体说明如下：

　　　　0：这是5.1.22之前的实现方式，通过AUTO-INC Locking方式

　　　　1：这个是默认值。对于simple inserts会使用互斥量去对内存的计数器进行累加操作。对于bulk inserts，使用的是传统的AUTO-INC Locking方式。在这种配置下，不考虑回滚操作，自增列的增长还是连续的。注意：使用了AUTO-INC Locking，这时进行simple inserts，需要等之前的释放。

　　　　2：insert-like通过互斥量。这个性能最高，但是会有问题。因为并发插入，自增长的值可能不是连续的。此外，Statement-Base Replication会出现问题。因此，使用这个的时候，应该使用row-base replication，才能保证最大的并发性能和主从数据一致。

　　InnoDB种自增长列必须是索引。

4.5 外键和锁

　　外键用于引用的完整性约束，对于外键的插入更新，首先需要查询父表中的记录，但是对父表的SELECT，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是SELECT ... LOCK IN SHARE MODE方式，加S锁。如果父表已经加了X锁，这个操作就会被阻塞。

5.锁的算法

5.1 行锁的3种算法

　　InnoDB有3种行锁的算法，其分别是：

　　　　Record Lock:单个行记录上的锁

　　　　Gap Lock:间隙锁，锁定一个范围，但不包含记录本身

　　　　Next-Key Lock: Gap+Record Lock，锁定一个范围，包含记录本身

　　Record Lock总是会去锁住索引记录，如果没有设置索引，就会使用隐式的主键来进行锁定。

　　Next-key Lock结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。如果一个索引有10，11，13，20这四个值，索引可能被Locking的区间为：（-∞,10] (10,11] (11,13] (13,20] (20,+∞)。这个设计的目的是为了解决Phantom Problem，幻像，后续会进行介绍。还有一种previous-key locking的技术，锁定的区间就是左闭右开了。当查询的索引有唯一属性时，会降级成Record Lock，提高并发。

　　InnoDB会对辅助索引的下一个键值加上gap lock，这一个键上还是Next-Key lock。Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内，但是由于不锁定本身的记录，会导致Phantom Problem。

5.2 解决Phantom Problem

　　在默认的事务隔离级别REPETABLE READ下，InnoDB采用Next-key locking机制来避免Phantom Problem。这个问题是指，在同一事务下，连续执行两次相同的SQL语句可能会有不同的结果，第二次会返回之前不存在的行。

　　例如：数据库种有数据a=1,2,5三条记录，事务1执行排他读操作，读取a大于2的记录，此时事务没有提交，事务2插入一条4的记录，并且数据库允许该操作，那么事务T1执行上述SQL会看见这条4的记录，这就和第一次的结果不一样了，违反的事务的隔离性。

　　是不是感觉很意外，读取大于2的记录时，锁定的行如果采取Record Lock，就只有5这行，另外一个事务提交4的记录是完全没问题的，再查询的时候是完全看的到另一个事务的提交的内容。而上面说的Next-Key Lock就不单是锁住5这个记录了，锁住的是(2,+∞)，插入4就是不被允许的了。事务隔离级别如果是READ COMMITTED，采取的只是Record Lock。

　　可以通过SELECT * from table where col = xxx lock in share mode，进行唯一性检测，检测为空，就可以插入数据了，因为这个机制保证数据库中在本次事务之内，数据不会被插入。这里有个问题，如果有多个事务并发操作这个操作，由于share mode是共享的，所以所有的都能检测为空，那么每个事务都执行相同的插入，这个机制不就有问题了吗？实际上不会，因为只有第一个插入的会成功，其他的会抛出死锁异常。

6 锁问题

　　通过锁机制可以实现事务的隔离性要求，使得事务可以并发工作。锁提高了并发，但是会带来潜在的问题。不过好在由于隔离性，只会带来3种问题，如果可以防止的这3种情况发生，就不会产生异常。

6.1 脏读

　　脏数据是指事务对缓冲池中行记录的修改，并没有被提交。这和脏页的概念不一样，那个指缓冲池的数据被修改的页，没有刷新到磁盘上。脏页的读取是正常的，这个是由于内存和磁盘数据的异步造成的，不影响数据的一致性。

　　但是脏数据不同，脏数据是指未提交的数据，如果读到了脏数据，即读取到了另一个事务未提交的数据，显然是违反了数据库的隔离性。

　　举个例子：修改事务隔离级别为READ UNCOMMITTED。这个时候A事务查询一次，B事务修改了这条记录，A事务再此查询，就会发现数据被修改了，这就产生了脏读，违反了事务的隔离性。这个之前解释过，READ COMMITTED读取的是最新的快照，所以不会产生脏读。另外由于行锁的实现是Record Lock，所以会产生Phantom现象。

　　所以InnoDB的默认事务级别使用的是REPETABLE READ，采取事务开始的快照内容，并且锁实现是Next-key lock。

6.2 不可重复读

　　不可重复读与脏读的定义很相似，其是指在一个事务中多次读取同一数据集合，另一个事务进行DML操作。这样第一个事务两次读取数据之间，由于第二个事务的修改，那么第二次读取的数据可能不一样。

　　与脏读的区别在于：脏读是读到未提交的数据，而不可重复读到已提交数据，但是违反了数据库事务一致性的要求。

　　换个方式记忆比较方便：没提交的数据是脏数据，读到了肯定会有问题，这个就是脏读。已提交的数据虽然是正确的数据，但是我之前读取过一次，在第二次读取的时候应该保证和第一次读取的结果一样，不然对业务逻辑可能就产生了影响，所以第二次的读取是错误的，也就是说由于提交了的数据，导致第二次读取错误，这就是不可重复读。

　　一般情况下是允许不可重复读的，因为既然数据提交了，那么数据应该就是正确的了，读到了也没有问题。但是还是要注意这个操作。

　　在InnoDB中，通过Next-key Lock算法避免不可重复读的问题，MySQL中将不可重复读定义成Phantom Problem。因为该算法不仅锁定了扫描到的索引，还锁定了这所有的范围（主动加锁？默认是一致性非锁定读，读取应该是由于多版本并发控制才对吧）。这个时候插入数据是不允许的。

6.3 丢失更新

　　丢失更新是另一个锁导致的问题，简单的说就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据不一致。比如：事务1将记录r更新为v1，但事务没有提交，事务2将记录更新为v2，也没有提交，事务1提交再事务2提交。

　　这样就产生了丢失更新的问题。在当前数据库的任何隔离级别都不会导致数据库理论意义上的丢失更新，因为即使是READ UNCOMMITTED的事务隔离级别，对于DML操作，需要对行或者其他粗细粒度级别的对象加锁（采取的是一致性非锁定读，但是修改还是加了锁的），这样事务2不能在事务1修改数据的时候进行操作，会被阻塞。

　　而实际上数据库虽然能够阻止这种的丢失更新问题，但是在应用上会有另一种逻辑上的丢失更新的问题，导致该问题的不是数据库本身。简单来说就是，事务T1查询一个数据，放入内存，给user1，事务T2查询该行数据，给user2。user1修改后提交，user2修改后提交，这时就发生了丢失更新的问题。这个问题就很严重了，比如1在两个客户端上进行转账，第一个客户端转了1000元，但是没有提交，又在第二个客户端用未提交数据进行操作，转了1元，后面两个都提交成功了，后面就覆盖了前面的记录。最终显示就是转了1元，但实际上有2个人，一人收到了1000，一人收到了1元。

　　这种更新操作要避免，就必须让事务在这种情况下进行串行化。读取的时候加上X锁，这样第二个操作就必须等第一个操作完成，才能获取当前的金额了，这样就避免了丢失更新的问题。直接使用update语句确实可以不需要这么麻烦，但是在实际应用中要先检测用户的金额够不够才能进行吧，这就涉及了查询动作。

　　丢失更新是最容易犯的错误，也是最不容易被发现的一个错误，这种现象是随机的，零星出现的，不过其可能造成的后果十分严重。

7.阻塞

　　由于锁的兼容性关系，有些事务必须等待其他事务执行完毕，释放暂用的资源，才能进行，这就是阻塞。阻塞可以保证事务可以正常的并发允许。

　　innodb_lock_wait_timeout用来控制等待的时间，默认50秒。该值是动态参数，可以运行时修改。

　　innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作，默认是off，不回滚。静态参数，不能修改。牢记，默认情况下不会回滚超时引发的错误异常。实际上大部分情况都不会对异常进行回滚。这是十分危险的状态，因此用户必须判断是否需要COMMIT还是ROLLBACK，之后再进行下一步操作。

8.死锁

8.1 死锁的概念

 　　死锁指的是两个或两个以上的事务在执行过程中，因争夺锁资源造成的一种互相等待的现象。没有外力推动，事务都无法推进下去。解决死锁问题最简单的方式就是不要有等待，将等待转换为回滚。毫无疑问可以避免死锁问题，但是会导致并发性能的下降，甚至任何一个事务都不能进行。这个问题比死锁更严重，还难以发现，浪费资源。

　　解决死锁的另一个方法就是超时，互相等待超过某一个时间，其中一个事务进行回滚，另一个就能继续执行下去了。innodb_lock_wait_timeout来设置超时时间。

　　超时机制虽然简单，但是其仅通过超时后对事务的回滚的方式进行处理，或者说是根据FIFO的顺序选择回滚对象，但若超时的事务所占权重很大，事务更新了很多行，产生了大量的undo页，这时使用FIFO方式就不合适了，因为回滚这个事务很可能比回滚另一个要慢。所以，当前数据库除了使用超时策略，还使用wait-for graph（等待图）来进行死锁检测，这是一种主动的死锁检测方式。

　　wait-for graph保存了以下信息：

　　　　1.锁的信息链表

　　　　2.事务的等待链表

　　通过上述链表可以构建一张图，而在这个图中如果有回路，就是死锁。采用深度优先算法实现，1.2版本之前使用递归方法实现。

8.2 死锁的概率

　　死锁发生的概率应该非常少，若经常发生，系统就是不可用的。此外，死锁的次数要小于等待的次数，因为必须2次等待才会发生一次死锁。

　　假设有n+1个线程执行，n+1个事务，每个事务操作相同。每个事务由r+1个操作组成，每个操作为从R行数据中随机操作一行数据，并占用对象锁。每个事务在执行完最后一个步骤释放所占用的所有锁资源。最后，假设nr<<R，即线程操作的数据只占所有数据的一小部分。

　　当事务获取一个锁需要等待的概率是多少呢？假设一个事务获得一个锁，其他任何一个事务获得锁的情况为（1+2+3+...+r)/(r+1)≈r/2

　　由于每个操作是从R行数据中取一条数据，每行数据被取到的概率是1/R,因此，事务中每个操作需要等待的概率PW为：nr/2R

　　事务是由r个操作组成，因此事务等待的概率PW(T)是 1-(1-PW）^r ≈ nr^2/2R

　　死锁是由于产生回路，也就是事务互相等待而发生的，若死锁长度为2，两个节点间发生，则概率为 PW(T)^2/n = nr^4/4R^2

　　由于nr<<R，所以概率是很低的。

　　　　事务的数量n越大，死锁概率越大。

　　　　每个事务的操作r越多，死锁概率越大

　　　　操所数据的集合R越小，概率越大。

9 锁升级

　　锁升级是指将锁的粒度降低。就是将行锁升级为一个页锁，页锁升级为表锁。锁是一种稀缺资源，想避免锁的开销，数据库就会频繁出现锁升级现象。

　　InnoDB不存在锁升级现象，因为其不是根据每条记录产生行锁的，是根据每个事务访问的每个页对锁进行管理的，采取的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录，开销通常是一致的。