MySQL锁总结

MySQL锁总结

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性，有效性，锁重读也是影响数据库并发访问性能的一个重要因素。锁是MySQL在服务器层和存储引擎层的并发控制。

加锁是消耗资源的，锁的各种操作，包括获得锁，检测锁是否已解除，释放锁等。

锁机制

共享锁与排他锁

• 共享锁（读锁）：其他事务可以读，但不能写。
• 排他锁（写锁）：其他事务不能读取，也不能写。

粒度锁

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

• MyISAM和MEMORY存储引擎采用的是表级锁
• BDB存储引擎采用的是页面所，但也支持表级锁
• InnoDB存储引擎支持行级锁和表级锁，默认情况使用行锁

默认情况下，表锁和行锁都是自动获得的，不需要额外的命令

但是当用户需要明确的进行锁表或者进行事务的控制，一边确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

不同粒度锁的比较

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
• 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web应用。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的该概率最低，并发度也最高。

• 最大程度的支持并发，同时也带来了最大的锁开销。
• 在InnoDB中，除了单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB中发生死锁是可能的。
• 行级锁只在存储引擎层实现，而MySQL服务器没有实现，行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线书屋处理系统。

页面锁：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

MyISAM表锁

MyISAM表级锁模式

• 表共享读锁：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 表独占写锁：会阻塞其他用户对同一表的读和写操作；

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

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

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

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表中有没有空洞，都允许在表尾并发插入记录。

查询表级锁争用情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁的争夺，如果Table_locks_waited的值比较高，则说明存在较严重的表级锁争用情况

InnoDB行级锁和表级锁

InnoDB锁模式

InnoDB实现了以下两种类型的行锁：

• 共享锁（S）：允许一个书屋去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，组织其他事务取得相同数据集的共享读锁和排他写锁。

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

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

锁模式的兼容情况

InnoDB加锁方法

• 意向锁时InnoDB自动加的，不需用户干预
• 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X）；
• 对于普通的SELECT语句，InnoDB不会加任何锁；
• 事务可以通过以下语句显式给纪录集加共享锁或排他锁：
  • 共享锁（S） SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
  • 排他锁（X）SELECT * FROM table_name WHERE ... FOR UPDATE.其他session可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。
• 隐式锁定
  • InnoDB在事务执行过程中，使用两阶段锁协议：
  • 随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
  • 锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。
• 显式锁定

select ... lock in share mode
select ... for update

select for update

在执行这个select查询语句的时候，会将对应的索引访问条目继续宁上排他锁（X锁）,也就是说，这个语句对应的锁就相当于update带来的效果。

select *** for update的使用场景：为了让自己查到的数据确保时最新数据，并且查到后的数据只允许自己来修改的时候，需要用到for update子句。

select lock in share mode

in share mode子句的作用就是将查找到的数据加上一个share锁，这个就是表示其他的事务只能对这些数据进行简单的select操作，并不能够进行DML操作。select *** lock in share mode使用场景：为了确保自己查到的数据没有被其他事务正在修改，也就是说确保查到的数据时最新的数据，并且不允许其他人来修改数据，但是不一定能够修改数据，因为有可能其他的事务也对这些数据上了S锁。

性能影响

select for update语句，相当于一个update语句。在业务繁忙的情况下，如果书屋没有及时commit或者rollback可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。select lock in share mode语句是一个给查找的数据上一个共享锁的功能，它允许其他事务也对该数据上S锁，但是不能够允许对该数据进行修改，如果不及时commit或者rollback也可能会造成大量的事务等待。

InnoDB行锁的实现方式

• InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL于Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点就意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
• 不论是使用主键索引、唯一索引或者普通索引，InnoDB都会使用行锁来对数据加锁。
• 只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是有MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，他就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。
• 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用涉及的时候要注意这一点。

InnoDB的间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁是，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个间隙加锁，这种锁机制就是所谓的间隙锁。很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在时机应用开发中，尤其时并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

InnoDB使用间隙锁的目的

1. 防止幻读，以满足相关隔离级别的要求；
2. 满足恢复和复制的需要；

MySQL通过BINLOG录入执行成功的INSERT，UPDATE，DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制。MySQL的恢复机制有以下特点：

1. MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。
2. MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。

由此可见，MySQL的恢复机制要求，在一个事务未提交之前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

死锁

死锁产生

• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
• 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源也可能会产生死锁
• 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁，有些不会，死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁

• 数据库系统实现了各种死锁检测和死锁超时的机制，InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁的恢复

死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级的排他锁的事务进行回滚。所以事务型应用程序在涉及时必须考虑如何处理死锁，多数情况下只需要重新执行死锁回滚的事务即可。

外部锁的死锁检测

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务，但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决

死锁影响性能

死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务，在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢，有时发生死锁时，禁用死锁检测可能会更有效，这是可以依赖innoDB_lock_wait_timeout设置进行事务回滚。

MyISAM避免死锁

在自动加锁的情况下，MyISAM总是一次获得SQL语句所需要的全部锁，所以MyISAM表不会出现死锁。

InnoDB避免死锁

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预取要修改的每个行使用SELECT … FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。

• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时在申请排他锁，因为这时候当用户在申请排他锁时，其他事务可能有已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程度会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大达降低产生死锁的机会

• 通过SELECT … LOCK IN SHARE MODE获得读锁后，如果当前事务在需要对该季礼进行更新操作时，则很有可能造成死锁

• 改变事务隔离级别

如果出现死锁，可以使用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。