行锁 | 表锁 | 页锁 | |
---|---|---|---|
MyISAM | √ | ||
BDB | √ | √ | |
InnoDB | √ | √ |
全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。
当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句 (数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。
全局锁的典型使用场景是,做全库逻辑备份。
为什么不使用set global readonly=true的方式呢?
表级锁是MySQL中锁定粒度最大的一种锁,表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
特点:开销小,加锁快;不会出现死锁;锁定粒度大,发出锁冲突的概率最高,并发度最低。
MySQL表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。
隐式上锁(默认):
select(共享锁)
insert、update、delete(排他锁)
显式上锁/解锁(手动):
lock table table_name read;(共享锁)
lock table table_name write;(排他锁)
unlock table table_name;(解锁单表)
unlock tables;(解锁所有表)
排查:
show open tables;(查看)
分析
mysql> show status like 'table%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Table_locks_immediate | 100 |
| Table_locks_waited | 11 |
+----------------------------+---------+
table_locks_waited:因表级锁争用而等待的次数
table_locks_immediate:产生表级锁定的次数
行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,但加锁的开销也最大。
特点:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
行级锁分为共享锁和排他锁。
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。
意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
注意事项:
窗口A先修改了id为3的用户信息后,还没有提交事务,此时窗口B再更新同一条记录,然后就提示Lock wait timeout exceeded; try restarting transaction ,由于窗口A迟迟没有提交事务,导致锁一直没有释放,就出现了锁冲突,而窗口B一直在等待锁,所以出现了超过锁定超时的警告了。
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:
mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
+-------------------------------+-------+
InnoDB 的行级锁定状态变量不仅记录了锁定等待次数,还记录了锁定总时长,每次平均时长,以及最大时长,此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下: InnoDB_row_lock_current_waits:当前正在等待锁定的数量; InnoDB_row_lock_time:从系统启动到现在锁定总时间长度; InnoDB_row_lock_time_avg:每次等待所花平均时间; InnoDB_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间; InnoDB_row_lock_waits:系统启动后到现在总共等待的次数;
还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行,并分析锁争用的原因。具体方法如下:
mysql> create table InnoDB_monitor(a INT) engine=InnoDB;
然后就可以用下面的语句来进行查看:
mysql> show engine InnoDB status;
监视器可以通过发出下列语句来停止查看:
mysql> drop table InnoDB_monitor;
设置监视器后,会有详细的当前锁等待的信息,包括表名、锁类型、锁定记录的情况等,便于进行进一步的分析和问题的确定。默认情况下每15秒会向日志中记录监控的内容,如果长时间打开会导致.err文件变得非常的巨大,所以用户在确认问题原因之后,要记得删除监控表以关闭监视器,或者通过使用“–console”选项来启动服务器以关闭写日志文件。
指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。
事务A | 事务B |
---|---|
begin;update t set k=k+1 where id=1 | begin; |
update t set k=k+1 where id=2 | |
update t set k=k+1 where id=2 | |
update t set k=k+1 where id=1 |
事务A在等待事务B释放id=2的行锁,而事务B在等待事务A释放id=1的行锁。 事务A和事务B在互相等待对方的资源 释放,就是进入了死锁状态。
MyISAM中是不会产生死锁的,因为MyISAM总是一次性获得所需的全部锁,要么全部满足,要么全部等待。而在InnoDB中,锁是逐步获得的,就造成了死锁的可能。
在MySQL中,行级锁并不是直接锁记录,而是锁索引。当两个事务同时执行,一个锁住了主键索引,在等待其他相关索引。另一个锁定了非主键索引,在等待主键索引。这样就会发生死锁。
当出现死锁以后,有两种策略:
产生条件:
解除死锁:
避免死锁:
1、加锁顺序一致。如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。
2、尽可能一次锁定所需数据行。在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;
3、使用较低的隔离级别。对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率。
4、合理使用索引,减少不必要的索引。
5、保持简短的事务,单次操作数量不宜过多。
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。BDB支持页级锁。
特点:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
悲观锁(Pessimistic Locking),悲观锁是指在数据处理过程,使数据处于锁定状态,一般使用数据库的锁机制实现。
现在互联网高并发的架构中,受到fail-fast思路的影响,悲观锁已经非常少见了。
思考:表锁、行锁、页锁是悲观锁吗?
MySQL默认使用自动提交,使用悲观锁,必须关闭MySQL的自动提交。
set autocommit=0;
select…for update是MySQL提供的实现悲观锁的方式,可以保证当前的数据不会被其它事务修改。
//0.开始事务
begin;
//1.查询出商品库存信息
select stock from tb where id=1 for update;
//2.扣减商品库存
update tb set stock=stock-1 where id = 1;
//3.提交事务
commit;
注意事项:
乐观锁认为数据一般情况下不会造成冲突,所以在数据进行提交更新的时候,才会正式对数据的冲突与否进行检测。
一般利用数据版本号(version)/时间戳机制实现。
数据库表增加一个数字类型的 “version” 字段,读取数据时同时读取version字段,更新数据时对version值+1。提交更新时,判断数据库version值与第一次读取的version值是否相等,相等予以更新,否则更新失败。
//1.查询出商品库存信息
select (stock,version) from tb where id=1;
//2.扣减商品库存
update tb set stock=stock-1,version=version+1 where id=1 and version=#{version};
采用version号作为乐观锁,则每次只有一个事务能更新成功,业务感知上就是大量操作失败。优化后的sql减小锁力度,从而提升吞吐。
update tb set stock=stock-1 where id = 1 and stock-1>=0
注意事项:
共享锁又叫读锁(shared locks,S锁),语法如下:
select … lock in share mode;
排它锁又叫写锁(exclusive locks,X锁),语法跟悲观锁一样:
select ... for update
InnoDB中的S锁和X锁是行锁,当事务需要遍历所有行的锁持有情况,首先需要判断表A是否有表级锁,然后再判断表A每一行是否有行级锁,性能较低。
SELECT * FROM A for update; (加X锁)
意向锁(Intention Locks)是表锁,多用在innoDB中,是由数据库自己维护的加锁标识,可以快速判断表中是否有记录被上锁,避免遍历,提高加锁效率。
意向锁的主要作用是提升存储引擎性能,检查行锁前先检查意向锁是否存在,如果存在则阻塞线程。
意向锁分为意向共享锁(IS锁)和意向排它锁(IX锁)
意向锁协议
记录锁锁的是表中的某一条记录,记录锁的出现条件必须是精准命中索引并且索引是唯一索引,如主键id
间隙锁又称之为区间锁,每次锁定都是锁定一个区间,隶属行锁。
当我们查询数据用范围查询或者相等条件查询时,查询条件命中索引,请求共享或排他锁并且没有查询到符合条件的记录,此时就会将查询条件中的范围数据进行锁定(即使是范围库中不存在的数据也会被锁定)。对于键值在条件范围内但并不存在的记录,称为间隙,InnoDB也会为这些间隙加锁,即间隙锁。
比如数据库中只有1,4两条数据,如果查找不存在的3,则会导致(1,4)区间锁定。
在窗口A中我们根据id做一个范围更改操作,不提交事务,然后在范围B中插入一条记录,该记录的id值位于窗口A中的条件范围内。程序报错:Lock wait timeout exceeded; try restarting transaction 。锁定的区间就是 (1,3]这个左开右闭的区间。
间隙锁只会出现在可重复读的事务隔离级别中,mysql5.7默认就是可重复读。
间隙锁的目的是为了防止幻读,防止其他事务在间隙中执行insert语句,或者将已有数据update到间隙中,保障数据的恢复和复制。
隔离级别 | 脏读(Dirty Read) | 不可重复读(NonRepeatable Read) | 幻读(Phantom Read) |
---|---|---|---|
未提交读(Read uncommitted) | 可能 | 可能 | 可能 |
已提交读(Read committed) | 不可能 | 可能 | 可能 |
可重复读(Repeatable read) | 不可能 | 不可能 | 可能 |
可串行化(Serializable ) | 不可能 | 不可能 | 不可能 |
在InnoDB下,间隙锁的产生需要满足三个条件:
mysql的行锁默认就是使用的临键锁,临键锁是由记录锁和间隙锁共同实现的。
间隙锁的触发条件是命中索引,范围查询或等值查询没有匹配到相关记录。而临键锁恰好相反,临键锁的触发条件也是查询条件命中索引,不过,临键锁有匹配到数据库记录。
间隙锁所锁定的区间是一个左开右闭的集合,而临键锁锁定是当前记录的区间和下一个记录的区间。
现在数据库中只有三条数据1、5、7,当修改范围为1~8时,则锁定的区间为(1,+∞),锁定额不单是查询范围,并且还锁定了当前范围的下一个范围区间,此时,查询的区间8,在数据库中是一个不存在的记录值,并且,如果此时的查询条件是小于或等于8,也是一样的锁定8到后面的区间。
现在数据库有三条数据id分别是1、5、7,查询条件改为大于1小于7。由于7在数据库中是已知的记录,所以此时的锁定后,只锁定了(1,7],7之后的数据都没有被锁定。我们还是可以正常插入id为8的数据及其后面的数据。
所以,临键锁锁定区间和查询范围后匹配值很重要,如果后匹配值存在,则只锁定查询区间,否则锁定查询区间和后匹配值与它的下一个值的区间。
为什么临键锁后匹配会这样呢?
mysql的索引是基于B+树实现的,每个树节点上都有多个元素,即关键字数,当我们的索引树上只有1、5、7时,我们查询1~8,这个时候由于树节点关键字中并没有8,所以就把8到正无穷的区间范围都给锁定了。
如果数据库中id有1、5、7、10,此时我们再模糊匹配id为1~8的时候,由于关键字中并没有8,所以找比8大的,也就找到了10,根据左开右闭原则,此时10也是被锁定的,但是id为11的记录还是可以正常进行插入的。
参考:
探索Mysql锁机制(一)乐观锁&悲观锁:https://www.jianshu.com/p/ed896335b3b4
探索Mysql锁机制(二)共享锁&排他锁&意向锁&间隙锁:https://www.jianshu.com/p/904f52bde904
MySQL中锁详解(行锁、表锁、页锁、悲观锁、乐观锁等):https://www.cnblogs.com/jpfss/p/8890250.html
深入理解数据库行锁与表锁:https://zhuanlan.zhihu.com/p/52678870
MySQL中的行级锁,表级锁,页级锁:https://www.hollischuang.com/archives/914
Mysql表锁、行锁、页锁:https://www.bbsmax.com/A/MAzAn0xpJ9/
如何基于MySQL的行锁来实现悲观锁?:https://www.jianshu.com/p/512d877dff39
MySQL锁:https://processon.com/view/6038a4566376896cd60a0e7a?fromnew=1#outline
面试问题(mysql是行级锁还是表级锁):https://blog.csdn.net/hxpjava1/article/details/79407961
mysql面试必问的东西mysql锁:https://www.jianshu.com/p/ef2779b62980