MySQL 中锁的面试题总结
什么是锁?MySQL 中提供了几类锁?
锁是实现数据库并发控制的重要手段,可以保证数据库在多人同时操作时能够正常运行。MySQL 提供了全局锁、行级锁、表级锁。其中 InnoDB 支持表级锁和行级锁,MyISAM 只支持表级锁。
什么是死锁?
是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的过程称为死锁。
死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的过程称为死锁。
常见的死锁案例有哪些?
- 将投资的钱拆封几份借给借款人,这时处理业务逻辑就要把若干个借款人一起锁住 select * from xxx where id in (xx,xx,xx) for update。
- 批量入库,存在则更新,不存在则插入。解决方法 insert into tab(xx,xx) on duplicate key update xx='xx'。
如何处理死锁?
对待死锁常见的两种策略:
- 通过 innodblockwait_timeout 来设置超时时间,一直等待直到超时;
- 发起死锁检测,发现死锁之后,主动回滚死锁中的某一个事务,让其它事务继续执行。
如何查看死锁?
- 使用命令 show engine innodb status 查看最近的一次死锁。
- InnoDB Lock Monitor 打开锁监控,每 15s 输出一次日志。使用完毕后建议关闭,否则会影响数据库性能。
如何避免死锁?
- 为了在单个 InnoDB 表上执行多个并发写入操作时避免死锁,可以在事务开始时通过为预期要修改的每个元祖(行)使用 SELECT ... FOR UPDATE 语句来获取必要的锁,即使这些行的更改语句是在之后才执行的。
- 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁、更新时再申请排他锁,因为这时候当用户再申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁
- 如果事务需要修改或锁定多个表,则应在每个事务中以相同的顺序使用加锁语句。在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会
- 通过 SELECT ... LOCK IN SHARE MODE 获取行的读锁后,如果当前事务再需要对该记录进行更新操作,则很有可能造成死锁。
- 改变事务隔离级别。
InnoDB 默认是如何对待死锁的?
InnoDB 默认是使用设置死锁时间来让死锁超时的策略,默认 innodblockwait_timeout 设置的时长是 50s。
如何开启死锁检测?
设置 innodbdeadlockdetect 设置为 on 可以主动检测死锁,在 Innodb 中这个值默认就是 on 开启的状态。
什么是全局锁?它的应用场景有哪些?
全局锁就是对整个数据库实例加锁,它的典型使用场景就是做全库逻辑备份。 这个命令可以使整个库处于只读状态。使用该命令之后,数据更新语句、数据定义语句、更新类事务的提交语句等操作都会被阻塞。
什么是共享锁?
共享锁又称读锁 (read lock),是读取操作创建的锁。其他用户可以并发读取数据,但任何事务都不能对数据进行修改(获取数据上的排他锁),直到已释放所有共享锁。当如果事务对读锁进行修改操作,很可能会造成死锁。
什么是排它锁?
排他锁 exclusive lock(也叫 writer lock)又称写锁。
若某个事物对某一行加上了排他锁,只能这个事务对其进行读写,在此事务结束之前,其他事务不能对其进行加任何锁,其他进程可以读取,不能进行写操作,需等待其释放。
排它锁是悲观锁的一种实现,在上面悲观锁也介绍过。
若事务 1 对数据对象 A 加上 X 锁,事务 1 可以读 A 也可以修改 A,其他事务不能再对 A 加任何锁,直到事物 1 释放 A 上的锁。这保证了其他事务在事物 1 释放 A 上的锁之前不能再读取和修改 A。排它锁会阻塞所有的排它锁和共享锁。
使用全局锁会导致什么问题?
如果在主库备份,在备份期间不能更新,业务停摆,所以更新业务会处于等待状态。
如果在从库备份,在备份期间不能执行主库同步的 binlog,导致主从延迟。
如何处理逻辑备份时,整个数据库不能插入的情况?
如果使用全局锁进行逻辑备份就会让整个库成为只读状态,幸好官方推出了一个逻辑备份工具 MySQLdump 来解决了这个问题,只需要在使用 MySQLdump 时,使用参数 -single-transaction 就会在导入数据之前启动一个事务来保证数据的一致性,并且这个过程是支持数据更新操作的。
如何设置数据库为全局只读锁?
使用命令 flush tables with read lock(简称 FTWRL)就可以实现设置数据库为全局只读锁。
除了 FTWRL 可以设置数据库只读外,还有什么别的方法?
除了使用 FTWRL 外,还可以使用命令 set global readonly=true 设置数据库为只读。
FTWRL 和 set global readonly=true 有什么区别?
FTWRL 和 set global readonly=true 都是设置整个数据库为只读状态,但他们最大的区别就是,当执行 FTWRL 的客户端断开之后,整个数据库会取消只读,而 set global readonly=true 会一直让数据处于只读状态。
如何实现表锁?
MySQL 里标记锁有两种:表级锁、元数据锁(meta data lock)简称 MDL。表锁的语法是 lock tables t read/write。
可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。
对于 InnoDB 这种支持行锁的引擎,一般不使用 lock tables 命令来控制并发,毕竟锁住整个表的影响面还是太大。
MDL:不需要显式使用,在访问一个表的时候会被自动加上。
MDL 的作用:保证读写的正确性。
在对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。
读锁之间不互斥,读写锁之间,写锁之间是互斥的,用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放,在做表结构变更的时候,一定要小心不要导致锁住线上查询和更新。
悲观锁和乐观锁有什么区别?
顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会 block 直到它拿到锁。正因为如此,悲观锁需要耗费较多的时间,另外与乐观锁相对应的,悲观锁是由数据库自己实现了的,要用的时候,我们直接调用数据库的相关语句就可以了。
说到这里,由悲观锁涉及到的另外两个锁概念就出来了,它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现,它俩都属于悲观锁的范畴。
乐观锁是用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现方式。何谓数据版本?即为数据增加一个版本标识,一般是通过为数据库表增加一个数字类型的 version 字段来实现。当读取数据时,将 version 字段的值一同读出,数据每更新一次,对此 version 值加 1。当我们提交更新的时候,判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对,如果数据库表当前版本号与第一次取出来的 version 值相等,则予以更新,否则认为是过期数据。
比如: 1、数据库表三个字段,分别是id、value、version select id,value,version from t where id=#{id} 2、每次更新表中的value字段时,为了防止发生冲突,需要这样操作
update t
set value=2,version=version+1
where id=#{id} and version=#{version}
乐观锁有什么优点和缺点?
因为没有加锁所以乐观锁的优点就是执行性能高。它的缺点就是有可能产生 ABA 的问题,ABA 问题指的是有一个变量 V 初次读取的时候是 A 值,并且在准备赋值的时候检查到它仍然是 A 值,会误以为没有被修改会正常的执行修改操作,实际上这段时间它的值可能被改了其他值,之后又改回为 A 值,这个问题被称为 ABA 问题。
InnoDB 存储引擎有几种锁算法?
- Record Lock — 单个行记录上的锁;
- Gap Lock — 间隙锁,锁定一个范围,不包括记录本身;
- Next-Key Lock — 锁定一个范围,包括记录本身。
InnoDB 如何实现行锁?
行级锁是 MySQL 中粒度最小的一种锁,他能大大减少数据库操作的冲突。
INNODB 的行级锁有共享锁(S LOCK)和排他锁(X LOCK)两种。共享锁允许事物读一行记录,不允许任何线程对该行记录进行修改。排他锁允许当前事物删除或更新一行记录,其他线程不能操作该记录。
共享锁:SELECT ... LOCK IN SHARE MODE,MySQL 会对查询结果集中每行都添加共享锁,前提是当前线程没有对该结果集中的任何行使用排他锁,否则申请会阻塞。
排他锁:select * from t where id=1 for update,其中 id 字段必须有索引,MySQL 会对查询结果集中每行都添加排他锁,在事物操作中,任何对记录的更新与删除操作会自动加上排他锁。前提是当前没有线程对该结果集中的任何行使用排他锁或共享锁,否则申请会阻塞。
优化锁方面你有什么建议?
- 尽量使用较低的隔离级别。
- 精心设计索引, 并尽量使用索引访问数据, 使加锁更精确, 从而减少锁冲突的机会。
- 选择合理的事务大小,小事务发生锁冲突的几率也更小。
- 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁。
- 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会。
- 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。
- 不要申请超过实际需要的锁级别。
- 除非必须,查询时不要显示加锁。 MySQL 的 MVCC 可以实现事务中的查询不用加锁,优化事务性能;MVCC 只在 COMMITTED READ(读提交)和 REPEATABLE READ(可重复读)两种隔离级别下工作。
- 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。