数据库锁按照锁的粒度划分,可分为表级锁、行级锁、页级锁;按照锁级别划分,可分为共享锁、排他锁;按照加锁方式划分,可分为自动锁、显式锁;按照操作划分,可分为DML 锁、DDL 锁;按照使用方式划分,可分为乐观锁、悲观锁。
1、MyISAM 与 InnoDB 关于锁方面的区别是什么?
MyISAM 默认用的是表级锁,不支持行级锁。
对于 insert、update、delete,MyISAM 会自动加一个表级别的写锁 (排他锁);对于 select 会自动加一个表级别的读锁 (共享锁)。
可以通过以下 SQL 显式的加读锁或写锁:
LOCK TABLES person_info_myisam READ; // MyISAM 读锁
LOCK TABLES person_info_myisam WRITE; // MyISAM 写锁
UNLOCK TABLES; // MyISAM 释放读锁
// MyISAM SELECT 默认加读锁
SELECT ... FOR UPDATE; // MyISAM SELECT 加写锁
InnoDB 默认用的是行级锁,也支持表级锁。需要注意的是,InnoDB 在 SQL 没有用到索引的时候,走的依然是表级锁,只有 SQL 用到索引的时候走的是行级锁。
在 SQL 用到索引的时候,对于 insert、update、delete,InnoDB 会自动给涉及的数据行加一个写锁 (排他锁);对于 select 不会加任何锁。
事务可以通过以下 SQL 显式的加读锁或写锁:
SELECT ... LOCK IN SHARE MODE; // InnoDB SELECT 加读锁
SELECT ... FOR UPDATE; // InnoDB SELECT 加写锁
与 MyISAM 相比,由于 InnoDB 支持事务,所以 InnoDB 使用的是二段锁,二段锁即加锁和解锁是分为两个步骤来进行的,即先对同一个事务里的一批操作分别进行加锁,然后到 commit (提交) 的时候再对事务里加上的锁进行统一的解锁。
MyISAM 适合的场景:
- 对数据进行增删改的频率不高,查询非常频繁的场景;
- 没有事务的场景。
InnoDB 适合的场景:
- 数据增删改查都相当频繁的场景;
- 可靠性要求比较高,要求支持事务的场景。
为什么 MyISAM 查询速度比 InnoDB 快?
- InnoDB 要缓存数据块,MyISAM 只缓存索引块,这中间还有换进换出的减少;
- InnoDB 寻址要映射到块,再到行,MyISAM 记录的直接是文件的 OFFSET,比 InnoDB 定位要快;
- InnoDB 还需要维护 MVCC 一致,即使你的场景没有,但他还是需要去检查和维护 MVCC 多版本并发控制 。
2、使用 MySQL 实现乐观锁?
数据库表里可以增加一个 version 字段,查询数据的时候将 version 也查询出来,数据每更新一次,version + 1,当提交更新的时候,判断当前 version 与第一次取出来的是否相等,如果相等则更新,否则认为是过期数据。这就是乐观锁的一种实现方式。
3、数据库事务的四大特性?
数据库事务的四大特性即 ACID:
说明 | |
---|---|
原子性 | 事务包含的所有操作要么全部执行,要么全部失败回滚。 |
一致性 | 事务应确保数据库的状态从一个一致状态转变为另外一个一致状态。 |
隔离性 | 多个事务并发执行时,一个事务的执行不应该影响其他事务的执行。 |
持久性 | 一个事务一旦提交,它对数据库的修改应该永久保存在数据库中。 |
4、并发访问产生的问题
问题 | 说明 |
---|---|
更新丢失 | |
脏读 | A 事务读取 B 事务尚未提交的数据,此时如果 B 事务发生回滚,那么 A 事务读取到的数据就是脏数据 |
不可重复读 | A 事务前后多次读取同一条数据 。在 A 事务第一次读取数据后,B 事务执行了更改操作,此时 A 事务第二次读取到数据时,发现和之前的数据不一样了,成为不可重复读 |
幻读 | A 事务执行读取操作,需要两次统计数据的总量,前一次查询数据总量后,B 事务执行了新增数据的操作并完成提交,此时 A 事务再读取数据总量,发现和之前统计的不一样,成为幻读。 |
不可重复读和幻读有什么区别?
不可重复读是读取了其他事务更改的数据,针对 insert、update,解决:可以使用行级锁锁定该行,A 事务多次读取操作完成后才释放该锁,这个时候才允许其他事务更改刚才的数据。
幻读是读取了其他事务新增的数据,针对 insert、delete,解决:使用表级锁,锁定整张表,A 事务多次读取数据总量之后才释放该锁,这个时候才允许其他事务新增数据。
事务隔离机制:
隔离级别问题 | 更新丢失 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|---|
未提交读 | 可避免 | 会发生 | 会发生 | 会发生 |
已提交读(RC,READ-COMMITTED) | 可避免 | 可避免 | 会发生 | 会发生 |
可重复读(RR,REPEATABLE-READ) | 可避免 | 可避免 | 可避免 | 会发生 |
串行化(SERTALIZABLE) | 可避免 | 可避免 | 可避免 | 可避免 |
5、InnoDB 可重复读 (RR) 隔离级别下如何避免幻读?
可重复读隔离级别理论上避免不了幻读,而是通过一种巧妙的方式规避了幻读。分为表象和内在:
- 表象:快照读(非阻塞读),伪 MVCC;
- 内在:next-key 锁(行锁 + gap 锁)。
当前读:
select ... lock in share mode; # 读锁(共享锁)
select ... for update; # 写锁(排他锁)
update ...; # 写锁
delete ...; # 写锁
insert ...; # 写锁
可重复读 (RR) 隔离级别下避免幻读,根本原因是事务对数据加了 next-key 锁,next-key 锁由行锁 + gap 锁组成:
- 行锁:对单行记录上的锁;
- gap 锁:gap 就是索引树中插入新记录的空隙,gap 锁即锁定一个范围,但不包括记录本身,gap 锁的目的是为了防止同一事务的两次当前读出现幻读的情况。
可重复读 (RR) 隔离级别下 gap 锁出现的场景:
不论 delete、update、select,当前读若用到主键索引或者唯一索引会用到 Gap 锁吗?
- 如果 where 条件全部命中,则不会用 gap 锁,只会加行锁;
- 如果 where 条件部分命中或者全不命中,则会加 gap 锁;
gap 锁会用在非唯一索引或者不走索引的当前读中。
MVCC
一般我们认为MVCC有以下几个特点
- 每行数据都存在一个版本,每次数据更新时都更新该版本号
- 修改时Copy出当前版本,然后随意修改,各个事务之间无干扰
- 保存时比较版本号,如果成功(commit),则覆盖原纪录,失败则放弃copy(rollback)
- 就是每行都有版本号,保存时根据版本号决定是否成功,听起来含有乐观锁的味道,因为这看起来正是,在提交的时候才能知道到底能否提交成功
而InnoDB实现MVCC的方式是:
- 事务以排它锁的形式修改原始数据
- 把修改前的数据存放于undo log,通过回滚指针与主数据关联
- 修改成功(commit)啥都不做,失败则恢复undo log中的数据
二者的本质区别是:当修改数据的时候是否要排它锁定,如果锁定了还算不算MVCC?
- Inndb的实现算不上MVCC,因为并没有实现核心的多版本共存,undo log中的内容是串行化的结果,记录多个事务的过程,不属于多版本共存。但理想的MVCC是难以实现的,当事务仅修改一行记录使用理想的MVCC模式是没有问题的,可以通过比较版本号进行回滚,但当事务影响到多行数据时,理想的MVCC无能为力
- 比如,如果事务A执行理想的MVCC,修改Row1成功,而修改Row2失败,此时需要回滚Row1,但因为Row1没有被锁定,其数据又被事务B所修改,如果此时回滚Row1的内容,则会破坏事务B的修改结果,导致事务B违反ACID