面试题:数据库锁

张贤 2020年03月05日 129次浏览

数据库锁的分类

  • 按照锁的粒度划分,可分为表级锁、行级锁、页级锁
    MyISAM 支持表级锁,InnoDB 支持表级锁和行级锁,BDB 支持页级锁(不常见),介于表级锁和行级锁之间。关于页,和数据库底层的存储结构有关,有机会讲一下页。
  • 按照锁级别划分,可分为读锁(共享锁)、写锁(排它锁)
  • 按照加锁方式划分,可分为自动锁、显示锁
  • 按照操作划分,可分为 DML 锁、DDL 锁
  • 按照使用方式划分,可分为乐观锁、悲观锁。其中乐观锁并发度较高,不会产生死锁,其思想在编程语言中常用到,如 JAVA 底层的 CAS。
  • 乐观锁:
    读锁又被称为共享锁,写锁又被称为排它锁。

    在进行增删改时,MySQL 会默认加上写锁(排它锁);一个事务在进行查询时,MySQL 会默认加上读锁(共享锁),加上读锁时,其他事务依然可以读取数据,但不能修改数据(不能加上写锁)。但是在 InnDB 中,MySQL 对 select 做了优化,可以实现非阻塞读,并且通过 MVCC + Gap Lock 在可重复读的隔离级别下避免了幻读,详细分析请看下文。

一个事务加上写锁时,其他事务既不能查询(不能加上读锁),也不能增删改(不能加上写锁),而是会阻塞住,等待其他事务释放写锁。

MyISAM 和 InnoDB 关于锁方面的区别是什么

  • M有ISAM 默认用的是表级锁,不支持行级锁
  • InnoDB 默认用的是行级锁,也支持表级锁

MySQL 的事务默认是二段提交的,MySQL 有一个系统变量 autocommit 变量,控制,默认为 on,
InnoDB ;在不使用索引操作数据的时候,使用的是表锁;在使用索引操作数据时用的是行级锁以及 Gap 锁(普通非唯一索引时用到)。
InnoDB 还支持表级的意向锁,意向锁包括共享读锁(IS),排他写锁(IX)。但是行级锁未必一定比表级锁好,锁的粒度越小,代价越高,死锁概率越大。表锁只需要扫描表头就可以,而行级锁需要扫描每一行,开销更大。
InnoDB 必须有且仅有一个聚集索引,数据是根据聚集索引来存储的,数据和聚集所以存放在同一个文件中,通过聚集索引查询数据效率很高,只需要查询一棵树。而非聚集索引存放在另外的文件中,通过非聚集索引查询需至少需要查询两棵树,第一次是根据非聚集索引查询到对应的主键,第二次是根据主键索引查询数据。
而 MyISAM中,数据和索引是分离的,索引 B+ 树的叶子节点保存的是指向数据的指针。因此 MyISAM 在增删改的系统中,也就是纯检索的系统中,性能要好于 InnoDB。

MyISAM 适合的场景:

  • 频繁执行全表 count 语句。MyISAM 会在表头存储一个变量记录当前表中数据的行数,在执行 count() 时直接读取该变量即可,而对 InnoDB 表的执行 count() 时则需要遍历全表(可能是遍历某个索引,上一篇文章分析过了)。
  • 对数据进行增删改的频率不高,查询非常频繁
  • 没有事务

InnoDB 适合的场景:

  • 数据增删改都相当频繁。InnoDB 在进行增删改时,只会锁住对应的行,并发度较高。而MyISAM 的表在进行增删改时会锁住整张表。
  • 可靠性要求比较高,要求支持事务。D

数据库事务的四大特性(ACID)

  • 原子性(Atomic):事务要么全做,要么全都不做失败回滚
  • 一致性(Consistency):从一个一致状态转变到另一个一致状态,含义是完整性约束
  • 隔离性(Isolation):
  • 持久性(Durability):当系统或者介质发生故障时,数据库要确保已提交的更新不能丢失,对已提交事务的更新能恢复,主要体现在数据库的恢复性上。一旦一个事务被提交,DDMS 要提供适当的冗余,使其耐得住故障。在 InnoDB 中,会将所有对数据库的修改操作保存在redo log 中。

事务隔离级别,以及各级别下的并发访问问题

  • 更新丢失--主流数据库上在更新数据时都会加上写锁,因此 MySQL 所有事务隔离级别在数据库层面上均可避免该问题
  • 脏读--在 READ-COMMITED 级别下可以避免该问题
  • fd
    不可重复读侧重于对同一条数据的修改,幻读侧重于新增和删除数据。
    事务隔离级别越高,安全性越高,串行度越高,并发度越低。
    Oracle 默认的隔离级别是 READ-COMMITED,MySQL 默认的隔离级别是 REPEATABLE READ。

InnoDB 可重复读隔离级别下如何避免幻读

Next key 锁 = 行锁+ Gap 锁。Gap 锁是为了同一事务的两次当前读出现幻读的情况。Gap 锁 只在 RR 和 Serializable 下存在。

  • 使用当前读删改查查询主键,如果where 条件全部命中则不会用 Gap 锁,只会加记录锁。例如:select * from table where id in (1,3,5) 如果1,3,5全部命中,此时就算其他事务添加了数据,也不会影响该语句的第二次当前读,不会产生幻读现象,所以不会用 Gap 锁。另外使用当前读删改查查询唯一键(非聚集索引),除了给该索引所在的行加上行锁之外,也会在该行的聚集索引上加锁,防止其他并发事务通过主键来更新数据。
  • 如果 where 条件部分命中或者全都不命中,且用到了主键或者唯一键,则会加 Gap 锁。
  • Gap 锁还会用在走非唯一索引或者不走索引的当前读中。在非唯一索引中,Gap 锁的范围是左开右闭,其中表中主键的值也起到一定的作用,会锁住 非唯一索引+对应的主键的值范围内的数据。如果当前读不走索引,则会锁住表中所有的 Gap,相当于锁表,这种情况会降低并发,需要避免。
  • 为什么在不用索引的情况下,会锁住所有的 Gap?因为 MySQL 的数据是根据聚集索引的叶子节点顺序存储的。在使用其他非聚集唯一索引时,是可以找到对应的聚集索引的位置的,因此可以确定需要锁定的数据行的范围。但是使用非唯一索引或者不使用索引时,是不能精确找到对应的聚集索引的范围,因此会锁住所有的 Gap,也就是整张表。

RC(read commited)、RR(repeatable read) 级别下的 InnoDB 的非阻塞读如何实现

非阻塞读

当前读就是加了锁(共享锁、排它锁均可)的增删改查语句,读取的是数据的最新版本,并且在没有提交事务之前,其他并发事务不能修改当前记录。

SELECT...LOCK IN SHARE MODE
SELE...FOR UPDATE
UPDATE...
DELETE...
INSERT...

在 UPDATE 中会使用当前读,取得最新的数据,然后在此基础上再做更新。
快照读:在事务隔离级别低于 SERIABLE 的情况中,才有快照读,也就是不加锁的非阻塞读(select)。在 SERIABLE 隔离级别下,快照读也退化为当前读。快照读是基于并发性能的考虑而出现的。

MVCC 实现了多版本共存,读不加锁,读写不冲突,这对于读多写少的OLTP应用来说,极大地增加了系统的并发性能,是非常重要的。非阻塞读其实就是 MVCC,而 InnoDB 实现了仿照版的MVCC,也就是伪MVCC。 InnoDB 的 undo log 中记录的内容只是串行化的结果,记录了多个事务的过程,并不是真正的多版本共存。

  • 数据行里的隐藏字段 DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID(前面讲过,如果在创建 InnoDB 表时既没有指定主键,也没有指定唯一键的话,就会使用 隐藏字段 DB_ROW_ID 作为主键聚集索引)
  • undo 日志,包括 insert undo log(在事务回滚时需要,在事务提交后可以立即丢弃)、update undo log(包括 update 和 delete 操作的日志,在事务回滚和快照读时需要,只有在数据库所使用的快照中不涉及该日志记录,对应的回滚日志才会被 purge 线程删除)。当一个事务 update 一行数据之前,会先使用排它锁锁定该行,随后将原来的行记录拷贝到undo log 中作为一个历史版本,然后修改对应的值,修改该行的隐藏字段事务 ID,修改 DB_ROLL_PTR 指针,指向 undo log 中该行对应的历史版本数据,一行数据可以可以有多个历史版本,这时就形成了一个链表。
  • read view 是用来保证事务可见性的,使用了一个可见性算法。取出当前行的 DB_TRX_ID 字段,如果大于当前所有活跃事务的ID,则该行数据对当前操作是可见的。否则就沿着undo log 链表取出历史版本数据的 DB_TRX_ID,直到小于这些活跃事务为止,这样就保证我们获取的数据版本是当前可见的最稳定版本。

在 RC 隔离级别下,快照读和当前读所读取的数据是一样的,都是最新的。

在 RR 级别下,创建快照的时机决定了读取数据的版本。如果在别的事务提交之前没有读取过数据,则快照读的数据是最新的,在这种情况下,是可以避免一些幻读的,但如果是范围查询,则有可能产生幻读;如果在别的事务提交之前有读取过数据,则快照读的数据是上次读取的值,也就是数据的历史版本。

在读已提交的隔离级别下,同样的查询语句都会重新获取一次 Read View,这时如果 Read View 不同,就可能产生不可重复读或者幻读的情况,读到其他事务提交的修改。

当隔离级别为可重复读的时候,就避免了不可重复读,这是因为一个事务只在第一次 SELECT 的时候会获取一次 Read View,而后面所有的 SELECT 都会复用这个 Read View

为什么在 repeatable read 级别下的MVCC能避免不可重复读,却不能避免幻读,不是从头到尾都只用同一个read view吗?
因为通过 MVCC 读取的是数据的快照版本,是快照读。但是在执行更新操作时,不会用快照读去读取数据,而是会强制使用当前读去读取最新的数据版本,也就是获取最新版本的数据,因此避免不了幻读。需要加 Gap 锁才行。