一、锁的简单分类
1. 对数据操作类型可分为:
(1)读锁(共享锁):针对同一份数据,多个读操作可以同时进行而互不影响
(2)写锁(排他锁):当前锁操作没有完成之前,他会阻断其它的读锁和写锁
2. 对数据操作粒度可分为:
(1)表锁
(2)行锁
二、表锁(偏读)
1.特点
(1)偏向MyISAM存储引擎,开销小,加锁快。
(2)无死锁。
(3)锁定粒度大,发生锁冲突概率最高,并发度最低。
2.操作指令
- show open tables:查看表是否加锁
- lock table 表名 read(write):添加表读锁(写锁)
- unlock tables:解锁
- show status like ‘table%’:
Table_Locks_immediate:表锁定次数
Table_Locks_waited:出现表级锁定争用而发生等待的次数,该值越大,则说明存在较严重的表锁争用情况
3.阻塞案例
- 添加读锁(表共享锁):
(1)session1在表table1加读锁,session1只可以读取表table1数据,不可修改表table1数据,不可读取除表table1以外表的数据
(2)session2可以读取表table1的数据,可以读取除表table1以外表的数据,但是session2向表table1发出的写操作会被阻塞,直到session1释放锁 - 添加写锁(表排他锁)
(1)session1在表table1加写锁,session1可以读取表table1数据,也可以修改表table1数据,不可读取除表table1以外表的数据
(2)session2可以读取除表table1以外表的数据,但是session2向表table1发出的所有请求都会被阻塞,直到session1释放锁
三、行锁(偏写)
1.特点
(1)偏向InnoDB存储引擎,开销大,加锁慢。
(2)会出现死锁。
(3)锁定粒度小,发生锁冲突概率最低,并发度最高。
2.操作指令
- show status like ‘innodb_row_lock%’:
Innodb_row_lock_current_waits:当前正在等待锁定的数量
Innodb_row_lock_time:从系统启动到现在锁定的时间长度
Innodb_row_lock_time_avg:每次等待花费的平均时间
Innodb_row_lock_time_max:从系统启动到现在等待的最长一次所花费的时间
Innodb_row_lock_waits:从系统启动到现在总共的等待次数
2.索引失效-行锁变表锁
因sql写操作语句中参数的类型不匹配,如varchar类型字段未加单引号,mysql底层类型自动转换时,会导致索引失效,最终使行锁变为表锁
3.间隙锁危害
当在sql的检索条件中使用范围条件时,对该范围内已有数据记录的索引项加锁,并且对于该范围不存在数据的“间隙”也会加锁,导致在插入该范围内不存的数据时,被阻塞。
4.优化建议
(1)尽可能让所有的数据检索都通过索引Index来完成,从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定。
(2)合理设计索引,让InnoDB在索引键上面加锁的时候尽可能准确,尽可能的缩小锁定范围,避免造成不必要的锁定而影响其他查询的执行。
(3)尽可能减少基于范围的数据检索过滤条件,避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
(4)尽量控制MySQL事务的大小,减小锁定的资源量和锁定的时间长度。
(5)在业务环境允许的情况下,尽量使用较低级别的事务隔离,以减少MySQL因为实现事务隔离级别所带来的附加成本。