一、表锁
表锁不依赖于储存引擎(不管你是MySQL的什么存储引擎,对于表锁的策略都是一样的),开销小(因为力度大)。表锁不会产生死锁问题,但是表锁会影响并发率。 表锁不依赖索引,当索引失效的时候,行锁会升级成表锁。索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer,我们加上引号,就变成了 String,这个时候索引就会失效了。
二、页锁
页锁就是在 页的粒度 上进行锁定,锁定的数据资源比行锁要多,因为一个页中可以有多个行记录。当我 们使用页锁的时候,会出现数据浪费的现象,但这样的浪费最多也就是一个页上的数据行。页锁的开销 介于表锁和行锁之间,会出现死锁。锁定粒度介于表锁和行锁之间,并发度一般。
三、行锁
在MySQL的InnoDB引擎支持行锁,与Oracle不同,MySQL的行锁是通过索引加载的,也就是说,行锁是加在索引响应的行上的,要是对应的SQL语句没有走索引,则会全表扫描,行锁则无法实现,取而代之的是表锁,此时其它事务无法对当前表进行更新或插入操作。
1、行锁种类
1、Record Lock:单行记录上的锁
2、Gap Lock:间隙锁,锁定一个范围,但不包含记录本身
3、Next-Key Lock:等于Gap Lock + Record Lock,锁定一个范围,
并且锁定记录本身【次策略就是解决数据库泛读的】
锁力度: Next-Key Lock > Gap Lock > Record Lock
Record Lock:单独在记录上加锁适合,比如主键记录更新。例如:select * from user where user_id =7 for update;
间隙锁:适合范围更新,比如范围更新。 例如:select * from user where user_id < 7 for update;
Next-Key Lock:此锁有叫临键锁,是mysql解决幻读试用的。例如:insert语句
2、行锁特点注意
1、行锁必须有索引才能实现,否则会自动锁全表,那么就不是行锁了
2、两个事务不能锁同一个索引
3、insert,delete,update在事务中都会自动默认加上排它锁
3、行锁的问题
行锁力度Record Lock、Gap Lock、Next-Key Lock会一点点升级,会导致锁定与自己不相干的记录。会严重的影响MySql的插入性能。【Next-Key Lock锁定数据范围】
4、行锁范围
Record Lock:锁定一条记录
Gap Lock:锁定的是间隙
Next-Key Lock: 锁定的是前开后闭区间,例如:锁定[5,+无穷大]
5、行锁优化
1、尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
2、合理设计索引,尽量缩小锁的范围
3、尽可能减少索引条件,避免间隙锁
4、尽量控制事务大小,减少锁定资源量和时间长度
6、表锁与Next-Key Lock区别?
Next-Key Lock 是基于索引的一种行锁需要索引支持,表锁不需要索引支持适合各种引擎。 InnoDB如果表无索引默认主键为索引。 是否能说明InnoDB场景下没有区别,如何证明?
五、其他锁
1、自增长锁
自增锁是一种比较特殊的表级锁。并且在事务向包含了 AUTO_INCREMENT
列的表中新增数据时就会去持有自增锁,假设事务 A 正在做这个操作,如果另一个事务 B 尝试执行 INSERT
语句,事务 B 会被阻塞住,直到事务 A 释放自增锁。
2、外键锁
外键主要用于引用完整性得约束检查。在InnoDB存储引擎中,对于一个外键列,如果没有显式地对这个列加索引,InnoDB存储引擎自动对其加一个索引,因为这样可以避免表锁。对于外键值的插入或更新,首先需要查询父表中的记录,即SELECT 父表.但是对于父表的SELECT 操作,不是使用一致性非锁定读的方式,因为这样会发生数据不一致的问题。因此这时使用的是 SELECT .... LOCK IN SHARE MODE 方式,即主动对父表加一个S锁,如果这时父表上已经这样加X锁,子表上的操作会被阻塞。
六、死锁
1、死锁概率
死锁应该非常少发生,若经常发生,则系统是不可用的。此外,死锁的次数应该还要少于等待,因为至少需要2次等待才会产生一次死锁,从数学角度来分析,死锁发生的概率问题
当前数据库中有n+1个线程执行,即当前总共有n+1事务,假设每个事务所做的操作相同,若每个事务由r+1个操作组成,每个操作从R行数据中随机操作一行数据,并占用对象的锁,每个事务在执行完最后一个步骤释放锁占用的所有锁资源,最后,假设nr<<R即线程操作的数据只占所有数据的一小部分
1、在上述模型下,事务获得一个锁需要等待的概率是多少?当事务获得一个锁,其他任何一个事务获得锁的情况为
(1+2+3...+r)/(r+1) ≈ r/2
2、由于每个操作为从R行数据中取一条数据,每行数据被取到的概率为1/R,因此,事务中每个操作需要等待的概率PW为
PW=NR/2R
3、事务由r个操作锁组成,因此事务发生等待的概率PW(T)为
PW(T)=1-(1-PW)r≈r*PW≈nr2/2R
死锁是由于产生概率,也就是事务互相等待发生的,如果死锁的长度为2,即两个等待的节点间发生死锁,那么其概率为
一个事务发生死锁的概率≈PW(T) 2/n≈nr4/4R2
由于大部分死锁的长度为2,因此上述的公式基本代表了一个事务发生死锁的概率。从整个系统来看,任何一个事务发生死锁的概率为
系统中任何一个事务发生死锁的概率≈n2r4/4R2
上述公式可以发现,由于nr<<R,因此,发生死锁的概率是非常低的,同时,事务发生死锁的概率与以下几点因素有关:
1、事务中事务的数量n,数量越多发生死锁的概率越大
2、每个事务操作的数量r,每个事务操作的数量越大,发生死锁的概率越大
3、操作数据的集合R,越小发生死锁的概率越大
2、如何解决死锁
1、等待直到超时
mysql事务等待超时默认时间是50秒(innodb_lock_wait_timeout=50s),如果锁超过50秒会报错。
#查看锁超时时间
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
#修改锁超时时间
SET GLOBAL innodb_lock_wait_timeout=500;
2、死锁自动检查
打开(innodb_deadlock_detect=on)配置,发生死锁主动回滚一条事务,让其他事务继续执行。默认值是:innodb_rollback_on_timeout=off。
3、手工处理
#查看哪些表被锁
show OPEN TABLES where In_use > 0;
#查看正在运行的sql进程
show processlist;
#找到info对比然后kill进程
kill id
3、降低死锁方法
1、以固定的顺序访问表和行。比如两个更新数据的事务,事务A 更新数据的顺序 为1,2;事务B更新数据的顺序为2,1。这样更可能会造成死锁。
2、大事务拆小。大事务更倾向于死锁,如果业务允许,将大事务拆小。要把SQL扫行范围搞小点
3、在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁概率。
4、降低隔离级别。如果业务允许,将隔离级别调低也是较好的选择,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。
5、为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁,死锁的概率大大增大。