一、表锁

表锁不依赖于储存引擎(不管你是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、为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁,死锁的概率大大增大。