MySQL数据库学习笔记-锁机制

一、概述

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

1. 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。更适合于查询为主，只有少量按索引条件更新数据的应用，比如Web应用。
2. 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。并发查询的应用以及大量按索引条件并发如在线事务处理系统。
3. 页级锁：开销和加锁时间介于两者之间；会出现死锁；锁定粒度介于两者之间，并发度一般。

二、MyISAM表级锁

（一）锁模式

表共享读锁和表独占写锁
读锁可以叠加读锁，不可以叠加写锁；
写锁不可以叠加读锁，不可以叠加写锁
MyISAM表的读操作和写操作之间，以及写操作之间是串行的

（二）如何加锁

Lock tables orders read local, order_detail read localselect sum(total) from ordersselect sum(subtotal) from order_detailunlock tables;

加显示锁的时候，必须同时取得所有涉及表的锁，不支持锁的升级，不能访问未加锁的表。之所以不会出现死锁，是因为MyISAM总是一次获得SQL语句所需的全部锁。如果同一个使用多次，对相同的别名就得锁定多少次。

（三）并发插入

设置concurrent_insert系统变量设为2，允许并发插入；定期在系统空闲执行optimize table来整理空间碎片

（四）调度问题

1. 读锁写锁互斥，读写串行，一般写的优先级高于读的，会先获得锁，可以设置系统参数，当读锁达到这个值后，降低写的优先级，读锁一定可以获得锁的机会。
2. 还可以通过指定语句low_priority属性，降低该语句的优先级

（五）表锁优化建议

1、缩短锁定时间
- 尽量减少大的query
- 尽量简历高效的索引
- 优化MyISAM表数据文件
- 表控制字段类型
2、分离能并行的操作
使用current insert功能的参数
3、合理利用读写优先级

（六）MyISAM表级锁总结（重要）

1. 共享锁（S）之间是兼容的，但共享锁（S）和排它锁（X）之间，以及排他锁（X）之间是互斥的，也就是说读写是串行的。
2. 在一定条件下，MyISAM允许查询和插入的并发执行，利用这一点来解决应用中对同一表查询和插入的锁争用问题。
3. MyISAM默认的锁调度机制是写优先，但是用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中设置LOW_PRIORITY选项来调节读写锁的争用。
4. 由于表锁的锁定粒度大，读写之间又是串行的，更新越多，表可能出现严重的锁等待，可以考虑InnoDB表减少锁冲突

三、InnoDB行级锁

最大的不同：一是支持事务；二是采用了行级锁

（一）概念

1. 事务以及ACID属性
   事务是由一组SQL语句组成的逻辑单元
2. 并发事务处理带来的问题
3. 更新丢失
4. 脏读
5. 不可重复读
6. 幻读
7. 事务隔离级别

隔离级别 读数据一致性 脏读 不可重复读 幻读 未提交读(Read Uncommitted) 最低级别，只能保证不读取物理上损坏的数据 是 是 是 已提交读(Read Committed) 语句级 否 是 是 可重复读(Repeatable Read) 事务级 否 否 是 可序列化(Serializable) 最高级别，事务级 否 否 否

使用InnoDB Monitors来监视发生锁冲突的表和数据行等

（二）行锁模式及加锁方法

1. 共享锁（S）:允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
2. 排他锁（X）:允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他写锁
3. 意向共享锁（IS）:事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
4. 意向排他锁（IX）:事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

判断 X(请求锁模式) IX S IS X(当前锁模式) 冲突 冲突 冲突 冲突 IX 冲突 兼容 冲突 兼容 S 冲突 冲突 兼容 兼容 IS 冲突 兼容 兼容 兼容

如果一个事务请求的锁模式与当前的锁模式兼容，InnoDB就将请求的锁授予该事务，反之，如果两者不兼容，该事务就要等待锁释放。
对于update，delete和insert语句，InnoDB会自动给涉及的数据集加排他锁
用select …in share mode获得共享锁，确保没有人对这个记录进行update或delete操作，但是如果当前操作也需要进行更新，则很可能出现死锁，对于锁定记录后需要进行更新操作的应用，应该使用select …for update方式获得排他锁

（三）行锁实现方式

InnoDB行锁是通过给索引项加锁来实现的只有通过索引条件检索数据，InnoDB才能使用行级锁，否则只能使用表锁。

//创建索引表，添加索引方式alter table library_index add index id(id)

1. 在不通过索引条件进行查询的时候，InnoDB确实使用表锁，而不是行锁。
2. 行锁是针对索引加锁，不是针对记录加锁，虽然是访问不同行的数据记录，但是如果使用相同的索引键，是会出现冲突的。
3. 当表中有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不管使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁对数据加锁。
4. 即使使用了索引字段，但是否使用索引检索数据是由MySQL通过判断不同执行计划的代价来决定的。

（四）间隙锁（Next-Key锁）

基本概念：
当在使用范围条件而不是相等条件检索数据，并请求共享和排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在范围内但不存在的记录，叫做间隙，对这个间隙进行加锁就叫做间隙锁。
目的：
5. 为了防止幻读，以满足相关隔离级别的要求
6. 为了满足其恢复和复制的需要
缺点：
这种加锁机制会阻塞符合条件范围的键值并发插入，会造成严重的锁等待现象。

（五）恢复和复制

恢复机制要求：
在一个事务未提交前，其它并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，要求事务串行化。
为了保护恢复和复制的一致性，InnoDB加了共享锁。

（六）不同的隔离级别下的一致性读及锁

对于许多SQL来说，隔离级别越高，InnoDB给记录加的锁就越严格，发生锁冲突的可能性就越高，从而对并发性事务处理性能的影响也就越大，应该尽量用较低的隔离级别，减少锁争用的机率。

（七）使用表锁

1. 事务需要更新大部分或全部数据，表又大
2. 事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚

（八）死锁

概念：两个事务都需要获得对方持有的排他锁才能继续完成事务，从而出现循环等待。
避免死锁的常用方法：
9. 在应用中不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表。
10. 在程序以批量方式处理数据的时候，如果事先对数据进行排序，保证每个线程按固定的顺序来处理
11. 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享所，更新时再申请排它锁
12. 在隔离级别REPEATABLE-READ下，两个线程同时加排它锁，如果没有符合记录的，线程都会加锁成功，可以尝试将隔离级别变为READ COMMITTED。
13. 尝试用ROLLBACK释放排它锁。

（九）InnoDB锁小结（重要）

1. 尽量使用较低的隔离级别
2. 精心设计索引，并尽量使用索引访问数据，使加锁更加精确
3. 选择合理的事务大小，小的事务发生锁冲突的机率也小
4. 给记录集显示加锁时，最好一次性请求完足够级别的锁
5. 不同程序访问一组表时，应尽量约定相同的顺序访问表，对一个表而言，尽可能以固定顺序存取表中的行
6. 尽量使用相等条件访问数据
7. 不要申请超过实际需要的锁级别
8. 对于一些特定的事务，可以使用表锁来提高处理速度

参考文献

《深入浅出MySQL 数据库开发、优化与管理维护》 唐汉明 翟振兴 兰丽华 关宝军 申宝柱 编著

《MySQL 性能调优与架构设计》 简朝阳 著