阿里架构师讲面试:数据库锁-mysql并发控制

1,308 阅读11分钟

觉得有收获的话帮忙点个赞吧,让有用的知识分享给更多的人

为什么要引入数据库锁机制

数据库中多个线程并发执行事务存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性(脏读,不可重复读,幻读等)。

为了解决这个问题,加锁是一个非常重要的技术,对实现数据库并发控制是一个好的方案。简单说,当一个执行sql语句的事务想要操作表记录之前,先向数据库发出请求,对你访问的记录集加锁,在这个事务释放这个锁之前,其他事务不能对这些数据进行更新操作。

锁类型

加锁机制

乐观锁

读多写少的场景。在应用层面实现。

提交更新时检查版本号或者时间戳是否符合。读数据场景会读取对应数据版本,写入操作会升级对应数据版本。执行写操作时,会先读取数据,获取数据原始version1,然后执行写操作,将获取的version1与数据库中数据的当前version做比较,如果版本相同,则执行写操作(update语句数据库系统默认加写锁,此时其它事务无法执行写操作)。否则,重新查询结果,然后重试写操作,直到完成。

悲观锁

写并发高的场景。数据库本身实现。

表锁,行锁等。加锁后,其它事务不能查询也不能更新。比如select... from table for update;

锁粒度

我们使用Mysql一般是使用InnoDB存储引擎的。InnoDB和MyISAM有两个本质的区别:

  • InnoDB支持行锁
  • InnoDB支持事务

不同的存储引擎支持的锁粒度是不一样的:

  • InnoDB行锁和表锁都支持!
  • MyISAM只支持表锁!

为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁

  • 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

行锁

开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高。

InnoDB默认使用行锁,实现了两种标准的行锁——共享锁与排他锁;

注意:

1、除了显式加锁的情况,其他情况下的加锁与解锁都无需人工干预。

2、InnoDB所有的行锁算法都是基于索引实现的,锁定的也都是索引或索引区间;

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!

行级锁的缺点是:由于需要请求大量的锁资源,所以速度慢,内存消耗大,并且可能导致大量的锁冲突,从而影响并发性能。

Innodb行锁实现方式-锁索引,而不是记录

InnoDB存储引擎的行锁是通过锁住索引实现的,而不是记录。这是理解很多数据库锁问题的关键。

由于InnoDB特殊的索引机制,数据库操作使用主键索引时,InnoDB会锁住主键索引;使用非主键索引时,InnoDB会先锁住非主键索引,再锁定主键索引。

如下图所示,当InnoDB锁定非主键索引b时,它也会锁住其对应的主键索引,所以锁住b值为2和3的非主键索引,那么与其相关的a值为6,5的主键索引也需要被锁住。

数据库加锁机制

例如一个索引有10,11,13,20这四个值。InnoDB可以根据需要使用Record Lock将10,11,13,20四个索引锁住,也可以使用Gap Lock将(-∞,10),(10,11),(11,13),(13,20),(20, +∞)五个范围区间锁住。Next-Key Locking类似于上述两种锁的结合,它可以锁住的区间有为(-∞,10],(10,11],(11,13],(13,20],(20, +∞),可以看出它即锁定了一个范围,也会锁定记录本身(左开右闭)。

  • InnoDB 自动给修改操作加锁,给查询操作不自动加锁。
  • 当使用唯一索引来搜索唯一行的语句时,不需要间隙锁定。如下面语句的id列有唯一索引,此时只会对id值为10的行使用记录锁。
  • InnoDB通过索引来实现行锁,而不是通过锁住记录。因此,当操作的两条不同记录拥有相同的索引时,也会因为行锁被锁而发生等待。
  • 由于InnoDB的索引机制,数据库操作使用了主键索引,InnoDB会锁住主键索引;使用非主键索引时,InnoDB会先锁住非主键索引,再锁定主键索引。
  • 当查询的索引是唯一索引(不存在两个数据行具有完全相同的键值)时,InnoDB存储引擎会将Next-Key Lock降级为Record Lock,即只锁住索引本身,而不是范围。
  • InnoDB对于辅助索引有特殊的处理,不仅会锁住辅助索引值所在的范围,还会将其下一键值加上Gap Lock。
  • InnoDB使用Next-Key Lock机制来避免Phantom Problem(当前读下的幻读问题)。

等值查询,且走索引

对主键或唯一索引,如果select查询时where条件全部精确命中(=或者in),这种场景本身就不会出现幻读,所以只会加行记录锁。

范围查询,且走索引

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

举例来说,假如emp表中只有101条记录,其empid的值分别是 1,2,…,100,101,下面的SQL:

Select * from emp where empid > 100 for update;

是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。

很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁!

没有走索引

即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。

兼容性

共享锁(读锁)

读锁(read lock),也叫共享锁(shared lock) 允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。

事务A对某数据加读锁后,事务B如果对数据发起读操作,同样可以获得读锁,并读取数据。如果事务B对数据发起写操作,则无法获取该数据的排他锁,故无法进行写操作,需要等到所有读锁解除后才能获取写锁,执行写操作。

排他锁(写锁)

写锁(write lock),也叫排他锁(exclusive lock) 允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享锁和排他锁。

死锁

死锁,就是产生了循环等待链条,我等待你的资源,你却等待我的资源,我们都相互等待,谁也不释放自己占有的资源,导致无限等待下去。

原因一:加锁顺序

比如说,一种常见的死锁情况一般出现在如下图所示的操作场景中。

会话一的语句使用了b上的索引,因为它是非主键索引,所以会先在b索引上添加锁,再去a索引上加锁。而会话二的语句恰恰相反,会先在索引a上加锁,再去索引b加锁。这种情况下,就可能出现死锁。

原因二:加锁顺序

比如:

//Session A
START TRANSACTION;
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
COMMIT;
//Thread B
START TRANSACTION;
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";
COMMIT;复制代码

当线程A执行到第一条语句UPDATE account SET p_money=p_money-100 WHERE p_name=”tim”;锁定了p_name=”tim”的行数据;并且试图获取p_name=”bill”的数据;

,此时,恰好,线程B也执行到第一条语句:UPDATE account SET p_money=p_money+100 WHERE p_name=”bill”;

锁定了 p_name=”bill”的数据,同时试图获取p_name=”tim”的数据;

此时,两个线程就进入了死锁,谁也无法获取自己想要获取的资源,进入无线等待中,直到超时!

如何避免和处理

  • innodb_lock_wait_timeout 等待锁超时回滚事务:

直观方法是在两个事务相互等待时,当一个等待时间超过设置的某一阀值时,对其中一个事务进行回滚,另一个事务就能继续执行。这种方法简单有效,在innodb中,参数innodb_lock_wait_timeout用来设置超时时间。

  • wait-for graph算法来主动进行死锁检测:

innodb还提供了wait-for graph算法来主动进行死锁检测,每当加锁请求无法立即满足需要并进入等待时,wait-for graph算法都会被触发。innodb_deadlock_detect

  • 应用层面加锁建议

这个问题的结论取决于事务 A 在执行完两条 update 语句后,持有哪些锁,以及在什么时候释放。你可以验证一下:实际上事务 B 的 update 语句会被阻塞,直到事务 A 执行 commit 之后,事务 B 才能继续执行。

知道了这个答案,你一定知道了事务 A 持有的两个记录的行锁,都是在 commit 的时候才释放的。

也就是说,在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。

知道了这个设定,对我们使用事务有什么帮助呢?那就是,如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

假设你负责实现一个电影票在线交易业务,顾客 A 要在影院 B 购买电影票。我们简化一点,这个业务需要涉及到以下操作:

从顾客 A 账户余额中扣除电影票价;

给影院 B 的账户余额增加这张电影票价;

记录一条交易日志。

也就是说,要完成这个交易,我们需要 update 两条记录,并 insert 一条记录。当然,为了保证交易的原子性,我们要把这三个操作放在一个事务中。那么,你会怎样安排这三个语句在事务中的顺序呢?

试想如果同时有另外一个顾客 C 要在影院 B 买票,那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额,需要修改同一行数据。

按照如上设定,不论你怎样安排语句顺序,所有的操作需要的行锁都是在事务提交的时候才释放的。所以,如果你把语句 2 安排在最后,比如按照 3、1、2 这样的顺序,那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待,提升了并发度。

如何上锁

  • 隐式加锁

innodb会给ddl语句隐式加锁。

  1. 显式加锁

select...from table for update;

select...from table lock in mode;

觉得有收获的话帮忙点个赞吧,让有用的知识分享给更多的人

## 欢迎关注掘金号:五点半社

## 关注微信公众号:五点半社(工薪族的财商启蒙)##