数据库MVCC和锁

最新推荐文章于 2024-11-21 13:22:23 发布

nanyan_xixi

最新推荐文章于 2024-11-21 13:22:23 发布

阅读量1.1k

点赞数

分类专栏： mysql数据库面试问题记录文章标签：数据库 mysql sql

本文链接：https://blog.youkuaiyun.com/nanyan_xixi/article/details/125961754

版权

面试问题记录同时被 2 个专栏收录

5 篇文章

订阅专栏

mysql数据库

2 篇文章

订阅专栏

1.数据库并发一致性问题

丢失修改：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
读脏数据：T1修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
不可重复读：T1 读取一个数据，T2对该数据做了修改。如果 T1 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
幻读：T1 读取某个范围的数据，T2在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

丢失修改、读脏数据、不可重复读着重于数据修改，幻读着重于新增或者删除。
读脏数据发生在数据没有提交前，其他任务读取了这个数据。
解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

2.数据库的四种隔离级别

读未提交（READ UNCOMMITTED）：事务中的修改，即使没有提交，对其它事务也是可见的。不能解锁读脏数据的问题。
读已提交（READ COMMITTED）：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。可以解决读脏数据的问题。
可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同样数据的结果是一样的。解决重复读的问题。
串行化（SERIALIZABLE）：强制事务串行执行。需要加锁实现，而其它隔离级别通常不需要。解决幻影读问题。

oracle/sqlServer的默认隔离级别是读已提交，mysql的默认隔离级别是可重复读。
查询数据库的隔离级别：

show variables

修改数据库的隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;//等级就是上面的几种

3.MVCC(Multi-Version Concurrency Control)

3.1 视图

在MySQL里，有两个“视图”的概念：

视图（View）：一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view … ，而它的查询方法与表一样。
一致性读视图（Consistent read view）：在MVCC时使用，即consistent read view，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。

3.2 快照读和当前读

快照读：不加锁的非阻塞读，简单的select操作，读取的数据库的课件版本（可能是历史版本），RC隔离级别每一个select创建一个快照读，RR隔离级别在事务开启的第一次select创建快照读，之后的select都用同一个快照读。
当前读：读取的是记录数据的最新版本，并且，当前读返回的记录都会加锁，保证其它事务不会再并发的修改这条记录。如，特殊的读操作，插入、更新、删除操作，属于当前读，需要加锁。

select … lock in share mode;
select … for update;
insert into table values (…);
update table set … where …;
delete from table where …;

事务的隔离级别实际上都是定义了当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”，需要加锁实现。

3.3 隐藏列

innodb引擎每个数据行有几个隐藏列：

事务ID（DATA_TRX_ID）：标记了最新更新这条行记录的transaction id，每处理一个事务，其值自动递增；
回滚指针（DATA_ROLL_PTR）：指向当前记录项的rollback segment的undo log记录，找之前版本的数据就是通过这个指针；
删除标识（DELETE BIT）：用于标识该记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候；

UPDATE事务过程的具体执行过程：
begin->用排他锁锁定该行->记录redo log->记录undo log->修改当前行的值，写事务编号，回滚指针指向undo log中的修改前的行
因为insert时，原始的数据并不存在，所以回滚时把insert undo log丢弃即可，而update undo log则必须遵守上述过程

下面分别以select、delete、 insert、 update语句来说明
SELECT
Innodb检查每行数据，确保他们符合两个标准：
1、InnoDB只查找版本早于当前事务版本的数据行(也就是数据行的版本必须小于等于事务的版本)，这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行
2、行的删除操作的版本一定是未定义的或者大于当前事务的版本号，确定了当前事务开始之前，行没有被删除
符合了以上两点则返回查询结果。
INSERT
InnoDB为每个新增行记录当前系统版本号作为创建ID。
DELETE
InnoDB为每个删除行的记录当前系统版本号作为行的删除ID。
UPDATE
InnoDB复制了一行。这个新行的版本号使用了系统版本号。它也把系统版本号作为了删除行的版本。

说明
insert操作时 “创建时间”=DB_ROW_ID，这时，“删除时间 ”是未定义的；
update时，复制新增行的“创建时间”=DB_ROW_ID，删除时间未定义，旧数据行“创建时间”不变，删除时间=该事务的DB_ROW_ID；
delete操作，相应数据行的“创建时间”不变，删除时间=该事务的DB_ROW_ID；
select操作对两者都不修改，只读相应的数据

4.数据库锁

4.1 锁类型

意向共享锁（intention shared lock, IS锁）：事务有意向对表中某些数据行加共享锁。
共享锁（S锁）：又称读锁，事务T1如果对A对象加了共享锁，事务T1可以对A进行读取，但是不能更新。其他事务也可以对A继续加共享锁，但是不能加排他锁。
意向排他锁（intention exclusive lock, IX锁）：事务有意向对表中某些数据行加排他锁。
排他锁（X锁）：又称写锁，事务T1如果对对象A加了排他锁，事务T1可以读取A可以修改A，其他事务不能对A加任何锁，直到事务T1释放。

4.2 锁粒度

表锁：锁住整张表，阻塞其他用户对该表的所有读写操作。并发性能低，发生所冲突的概率高，但是开销小，加锁速度快。
行锁：锁住一整行数据，行锁并发度高，发生冲突的概率降低，但是开销大，加锁速度慢，会出现死锁。在innodb索引中，除了单个sql组成的事物外，锁是逐步获得的，所以可能发生死锁。另外，在update多条数据的时候，会自动给命中的行加上行锁，但是不是一次性把所有行都加上锁，而是update一条给一条数据行加锁。

发生表锁的一些场景：
执行ALTER TABLE 语句，保证 DDL 对数据不产生影响；
SQL语句中没有使用到索引；

4.3 实现行锁的三种算法

记录锁（recode locks）：锁定单个行记录上的锁，但是锁住的是索引，不是记录本身。如果表没有主键索引，innodb会自动创建一个隐藏的主键索引，所以聚簇索引依然可以用。
间隙锁（gap locks）：锁住索引前后的间隙，左开右闭的区间。间隙锁防止有新的数据被插入，也防止已经存在的数据被更新成间隙内的数据。
next-key locks：next-key locks是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。MVCC 不能解决幻读的问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

4.4 间隙锁解决主从同步问题

1、事务a先delete id<6，然后在commit前；
2、事务b直接insert id=3，并且完成commit；
3、事务a进行commit；
此时binlog记录的日志是：事务b先执行，事务a在执行（binlog记录的是commit顺序）
那么主库此时表里面有id=3的记录，但是从库是先插入再删除，从库里面是没有记录的。
这就导致了主从数据不一致。
为了解决这个bug，所以RR级别引入了间隙锁。