一、概述
锁是计算机协调多个进程或线程并发访问某一资源的机制
锁的分类:
- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。更适合于查询为主,只有少量按索引条件更新数据的应用,比如Web应用。
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。并发查询的应用以及大量按索引条件并发如在线事务处理系统。
- 页级锁:开销和加锁时间介于两者之间;会出现死锁;锁定粒度介于两者之间,并发度一般。
二、MyISAM表级锁
(一)锁模式
表共享读锁和表独占写锁
读锁可以叠加读锁,不可以叠加写锁;
写锁不可以叠加读锁,不可以叠加写锁
MyISAM表的读操作和写操作之间,以及写操作之间是串行的
(二)如何加锁
Lock tables orders read local, order_detail read local
select sum(total) from orders
select sum(subtotal) from order_detail
unlock tables;
加显示锁的时候,必须同时取得所有涉及表的锁,不支持锁的升级,不能访问未加锁的表。之所以不会出现死锁,是因为MyISAM总是一次获得SQL语句所需的全部锁。如果同一个使用多次,对相同的别名就得锁定多少次。
(三)并发插入
设置concurrent_insert系统变量设为2,允许并发插入;定期在系统空闲执行optimize table来整理空间碎片
(四)调度问题
- 读锁写锁互斥,读写串行,一般写的优先级高于读的,会先获得锁,可以设置系统参数,当读锁达到这个值后,降低写的优先级,读锁一定可以获得锁的机会。
- 还可以通过指定语句low_priority属性,降低该语句的优先级
(五)表锁优化建议
1、缩短锁定时间
- 尽量减少大的query
- 尽量简历高效的索引
- 优化MyISAM表数据文件
- 表控制字段类型
2、分离能并行的操作
使用current insert功能的参数
3、合理利用读写优先级
(六)MyISAM表级锁总结(重要)
- 共享锁(S)之间是兼容的,但共享锁(S)和排它锁(X)之间,以及排他锁(X)之间是互斥的,也就是说读写是串行的。
- 在一定条件下,MyISAM允许查询和插入的并发执行,利用这一点来解决应用中对同一表查询和插入的锁争用问题。
- MyISAM默认的锁调度机制是写优先,但是用户可以通过设置LOW_PRIORITY_UPDATES参数,或在INSERT、UPDATE、DELETE语句中设置LOW_PRIORITY选项来调节读写锁的争用。
- 由于表锁的锁定粒度大,读写之间又是串行的,更新越多,表可能出现严重的锁等待,可以考虑InnoDB表减少锁冲突
三、InnoDB行级锁
最大的不同:一是支持事务;二是采用了行级锁
(一)概念
- 事务以及ACID属性
事务是由一组SQL语句组成的逻辑单元 - 并发事务处理带来的问题
- 更新丢失
- 脏读
- 不可重复读
- 幻读
- 事务隔离级别
隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|---|
未提交读(Read Uncommitted) | 最低级别,只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
已提交读(Read Committed) | 语句级 | 否 | 是 | 是 |
可重复读(Repeatable Read) | 事务级 | 否 | 否 | 是 |
可序列化(Serializable) | 最高级别,事务级 | 否 | 否 | 否 |
使用InnoDB Monitors来监视发生锁冲突的表和数据行等
(二)行锁模式及加锁方法
- 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
- 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享锁和排他写锁
- 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁(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)
- 在不通过索引条件进行查询的时候,InnoDB确实使用表锁,而不是行锁。
- 行锁是针对索引加锁,不是针对记录加锁,虽然是访问不同行的数据记录,但是如果使用相同的索引键,是会出现冲突的。
- 当表中有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,不管使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁对数据加锁。
- 即使使用了索引字段,但是否使用索引检索数据是由MySQL通过判断不同执行计划的代价来决定的。
(四)间隙锁(Next-Key锁)
基本概念:
当在使用范围条件而不是相等条件检索数据,并请求共享和排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在范围内但不存在的记录,叫做间隙,对这个间隙进行加锁就叫做间隙锁。
目的:
5. 为了防止幻读,以满足相关隔离级别的要求
6. 为了满足其恢复和复制的需要
缺点:
这种加锁机制会阻塞符合条件范围的键值并发插入,会造成严重的锁等待现象。
(五)恢复和复制
恢复机制要求:
在一个事务未提交前,其它并发事务不能插入满足其锁定条件的任何记录,也就是不允许出现幻读,要求事务串行化。
为了保护恢复和复制的一致性,InnoDB加了共享锁。
(六)不同的隔离级别下的一致性读及锁
对于许多SQL来说,隔离级别越高,InnoDB给记录加的锁就越严格,发生锁冲突的可能性就越高,从而对并发性事务处理性能的影响也就越大,应该尽量用较低的隔离级别,减少锁争用的机率。
(七)使用表锁
- 事务需要更新大部分或全部数据,表又大
- 事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚
(八)死锁
概念:两个事务都需要获得对方持有的排他锁才能继续完成事务,从而出现循环等待。
避免死锁的常用方法:
9. 在应用中不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表。
10. 在程序以批量方式处理数据的时候,如果事先对数据进行排序,保证每个线程按固定的顺序来处理
11. 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享所,更新时再申请排它锁
12. 在隔离级别REPEATABLE-READ下,两个线程同时加排它锁,如果没有符合记录的,线程都会加锁成功,可以尝试将隔离级别变为READ COMMITTED。
13. 尝试用ROLLBACK释放排它锁。
(九)InnoDB锁小结(重要)
- 尽量使用较低的隔离级别
- 精心设计索引,并尽量使用索引访问数据,使加锁更加精确
- 选择合理的事务大小,小的事务发生锁冲突的机率也小
- 给记录集显示加锁时,最好一次性请求完足够级别的锁
- 不同程序访问一组表时,应尽量约定相同的顺序访问表,对一个表而言,尽可能以固定顺序存取表中的行
- 尽量使用相等条件访问数据
- 不要申请超过实际需要的锁级别
- 对于一些特定的事务,可以使用表锁来提高处理速度
参考文献
《深入浅出MySQL 数据库开发、优化与管理维护》 唐汉明 翟振兴 兰丽华 关宝军 申宝柱 编著
《MySQL 性能调优与架构设计》 简朝阳 著