MySQL/深入理解 MySQL 数据库锁
数据库锁的分类
按照锁的粒度划分,可分为表级锁、行级锁、页级锁 MyISAM 支持表级锁,InnoDB 支持表级锁和行级锁,BDB 支持页级锁(不常见),介于表级锁和行级锁之间。关于页,和数据库底层的存储结构有关,有机会讲一下页。
按照锁级别划分,可分为读锁(共享锁)、写锁(排它锁)
按照加锁方式划分,可分为自动锁、显示锁
按照使用方式划分,可分为乐观锁、悲观锁。其中乐观锁并发度较高,不会产生死锁,其思想在编程语言中常用到,如 JAVA 底层的 CAS。
乐观锁: 读锁又被称为共享锁,写锁又被称为排它锁。
在进行增删改时,MySQL 会默认加上写锁(排它锁);一个事务在进行查询时,MySQL 会默认加上读锁(共享锁),加上读锁时,其他事务依然可以读取数据,但不能修改数据(不能加上写锁)。但是在 InnDB 中,MySQL 对 select 做了优化,可以实现非阻塞读,并且通过 MVCC + Gap Lock 在可重复读的隔离级别下避免了幻读,详细分析请看下文。
一个事务加上写锁时,其他事务既不能查询(不能加上读锁),也不能增删改(不能加上写锁),而是会阻塞住,等待其他事务释放写锁。
MyISAM 和 InnoDB 关于锁方面的区别是什么
- MyISAM 默认用的是表级锁,不支持行级锁
- InnoDB 默认用的是行级锁,也支持表级锁
MySQL 的事务默认是二段提交的,MySQL 有一个系统变量 autocommit 变量,控制,默认为 on, InnoDB ;在不使用索引操作数据的时候,使用的是表锁;在使用索引操作数据时用的是行级锁以及 Gap 锁(普通非唯一索引时用到)。 InnoDB 还支持表级的意向锁,意向锁包括共享读锁(IS),排他写锁(IX)。但是行级锁未必一定比表级锁好,锁的粒度越小,代价越高,死锁概率越大。表锁只需要扫描表头就可以,而行级锁需要扫描每一行,开销更大。 InnoDB 必须有且仅有一个聚簇索引,数据是根据聚簇索引来存储的,数据和聚集所以存放在同一个文件中,通过聚簇索引查询数据效率很高,只需要查询一棵树。而非聚簇索引存放在另外的文件中,通过非聚簇索引查询需至少需要查询两棵树,第一次是根据非聚簇索引查询到对应的主键,第二次是根据主键索引查询数据。 而 MyISAM中,数据和索引是分离的,索引 B+ 树的叶子节点保存的是指向数据的指针。因此 MyISAM 在增删改的系统中,也就是纯检索的系统中,性能要好于 InnoDB。
MyISAM 适合的场景:
- 频繁执行全表 count 语句。MyISAM 会在表头存储一个变量记录当前表中数据的行数,在执行 count() 时直接读取该变量即可,而对 InnoDB 表的执行 count() 时则需要遍历全表(可能是遍历某个索引,上一篇文章分析过了)。
- 对数据进行增删改的频率不高,查询非常频繁
- 没有事务
InnoDB 适合的场景:
- 数据增删改都相当频繁。InnoDB 在进行增删改时,只会锁住对应的行,并发度较高。而MyISAM 的表在进行增删改时会锁住整张表。
- 可靠性要求比较高,要求支持事务。
数据库事务的四大特性(ACID)
- 原子性(Atomic):事务要么全做,要么全都不做失败回滚
- 一致性(Consistency):从一个一致状态转变到另一个一致状态,含义是完整性约束
- 隔离性(Isolation):
- 持久性(Durability):当系统或者介质发生故障时,数据库要确保已提交的更新不能丢失,对已提交事务的更新能恢复,主要体现在数据库的恢复性上。一旦一个事务被提交,DDMS 要提供适当的冗余,使其耐得住故障。在 InnoDB 中,会将所有对数据库的修改操作保存在redo log 中。
事务隔离级别,以及各级别下的并发访问问题
- 更新丢失--主流数据库上在更新数据时都会加上写锁,因此 MySQL 所有事务隔离级别在数据库层面上均可避免该问题
- 脏读--在 READ-COMMITED 级别下可以避免该问题
- 不可重复读侧重于对同一条数据的修改,幻读侧重于新增和删除数据。 事务隔离级别越高,安全性越高,串行度越高,并发度越低。 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 的非阻塞读如何实现
当前读就是加了锁(共享锁、排它锁均可)的增删改查语句,读取的是数据的最新版本,并且在没有提交事务之前,其他并发事务不能修改当前记录。 1
2
3
4
5SELECT...LOCK IN SHARE MODE
SELE...FOR UPDATE
UPDATE...
DELETE...
INSERT...
在 SERIABLE 隔离级别下,快照读也退化为当前读。快照读是基于并发性能的考虑而出现的。
MVCC 实现了多版本共存,读不加锁,读写不冲突,这对于读多写少的OLTP应用来说,极大地增加了系统的并发性能,是非常重要的。
非阻塞读其实就是 MVCC,而 InnoDB 实现了仿照版的MVCC,也就是伪MVCC。
InnoDB 的 undo log 中记录的内容只是串行化的结果,记录了多个事务的过程,并不是真正的多版本共存。
在比较多长事务的情况下,MVCC需要保存每个事务对应的数据版本,而这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间。
- 数据行里的隐藏字段 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 锁才行。
对于可重复读,查询只承认在事务启动前就已经提交完成的数据; 对于读提交,查询只承认在语句启动前就已经提交完成的数据;
在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。所以如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
一致性读、当前读和两段锁协议,需要串联起来理解,详情请看 《MySQL 实战 45 讲》的《事务到底是隔离的还是不隔离的?》
- ⼀个数据版本,对于⼀个事务视图来说,除了⾃⼰的更新总是可⻅以外,有三种情况:
- 版本未提交,不可⻅;
- 版本已提交,但是是在视图创建后提交的,不可⻅;
- 版本已提交,⽽且是在视图创建前提交的,可⻅。
- 更新数据都是先读后写的,⽽这个读,只能读当前的值,称为“当前读”(current read)。
- 我们在上⼀篇⽂章中提到的“两阶段锁协议”就要上场了。事务C’没提交,也就是说(1,2)这个版本上的写锁还没释放。 ⽽事务B是当前读,必须要读最新版本,⽽且必须加锁,因此就被锁住了,必须等到事务C’释放这个锁,才能继续它的当前读。
在可重复读隔离级别下,普通的查询是快照读,是不会看到别的事务插⼊的数据的。因此,幻读在“当前读”下才会出现。