mysql面试
什么是事务?
事务是指对数据库进行的一组操作,该组操作满足ACID四大特性。
扩展:事务由commit提交,由rollback进行回滚。
解释ACID 特性?
原子性(Atomicity):一个事务里的全部操作构成一个不可再分的整体。全部的操作执行成功,那么事务执行成功;有一个操作执行失败,那么事务里的全部操作执行失败。
连续性(Consistency):数据库的事务不能破坏关系数据在业务逻辑上的一致性。例如,A向B转账前后,A、B账户总存款不变。
隔离型(Isolation):一个事务不应该影响其他事务的运行效果。
持续性(Durability): 事务一旦提交,即使系统故障,其对数据的修改永远保存在数据库中。
如何理解隔离性,事务的隔离级别有哪些?
隔离性是指事务之间隔离的程度,一个事务对其他事务的影响程度。
隔离级别有四个:
Read Uncommitted:事务运行过程中,其他事务可以立刻获取该事务对数据的修改结果。不隔离会产生脏读。
Read Committed:事务提交后,其他事务才能获取到该事务对数据的修改结果。不隔离会产生不可重复读。
Repeatable Read:一个事务中同一个查询,结果一致。不隔离会产生幻影读。
Serializable:多个事务串形执行。
举例如果不隔离会产生什么问题?(并发一致性问题)
脏读:事务可以读取其他事务回滚前的数据,这个数据就是脏数据。
不可重复读:同一个事务对同一条记录两次相同的查询结果不一样,这是因为其它事务修改了该记录。
幻影读:通过事务两次通用的范围内的查询结果不一样,这是由于其他事务在该范围内插入了新数据。
innodb实现对于隔离性的实现?
innodb通过锁+MVCC实现read committed和repeatable read两个隔离级别。
MVCC(多版本并发控制)
对数据的读与读,读与写,写与写事件进行并发控制时,通常采用加锁的方法。这样当同一条数据发生读与写,写与写事件时,只有一个事务能执行,其他事务都阻塞,这样mysql性能不高。MVCC解决读与写的性能问题。
MVCC在表格的每条记录中加入事务版本号,undo日志指针。Repeatable Read的MVCC执行流程示例如下:
一、 比如一个有个事务插入persion表插入了一条新记录,记录如下,name为Jerry, age为24岁,隐式主键是1,事务ID和回滚指针,我们假设为NULL
二、 现在来了一个事务1对该记录的name做出了修改,改为Tom,
-
- 在事务1修改该行(记录)数据时,数据库会先对该行加排他锁。
- 然后把该行数据拷贝到undo log中,作为旧记录,既在undo log中有当前行的拷贝副本。
- 拷贝完毕后,修改该行name为Tom,并且修改隐藏字段的事务ID为当前事务1的ID, 我们默认从1开始,之后递增,回滚指针指向拷贝到undo log的副本记录,既表示我的上一个版本就是它。
-
事务提交后,释放锁。
三、 又来了个事务2修改person表的同一个记录,将age修改为30岁。
-
- 在事务2修改该行数据时,数据库也先为该行加锁。
- 然后把该行数据拷贝到undo log中,作为旧记录,发现该行记录已经有undo log了,那么最新的旧数据作为链表的表头,插在该行记录的undo log最前面。
- 修改该行age为30岁,并且修改隐藏字段的事务ID为当前事务2的ID, 那就是2,回滚指针指向刚刚拷贝到undo log的副本记录。
-
事务提交,释放锁。
从上面,我们就可以看出,不同事务或者相同事务的对同一记录的修改,会导致该记录的undo log成为一条记录版本线性表,即链表,undo log的链首就是最新的旧记录,链尾就是最早的旧记录(当然就像之前说的该undo log的节点可能是会purge线程清除掉,像图中的第一条insert undo log,其实在事务提交之后可能就被删除丢失了,不过这里为了演示,所以还放在这里)
快照读与当前读
由于MVCC加入了快照的概念,因此产生了快照读和当前读的情况。
快照读:只用当前事务版本之前的快照数据。快照读的指令:
select * from table where ?;
当前读:使用最新的快照数据,例如update,需要读取最新的快照才能修改数据。
- select * from table where ? lock in share mode;(s锁)
- select * from table where ? for update;
- insert;
- update(=delete + insert);
- delete;
锁
mvcc只解决了当前读的脏读,不可重复度,幻影读。不能解决当前读的问题。同时解决当前读与快照读,需要使用MVCC+锁技术。
- Record Locks:锁定一个记录上的索引,而不是记录本身。如果表没有设置索引,InnoDB 会自动在主键上创建隐藏的聚簇索引,因此 Record Locks 依然可以使用。
- Gap Locks:锁定索引之间的间隙,但是不包含索引本身。
- Next-Key Locks:它是 Record Locks 和 Gap Locks 的结合,不仅锁定一个记录上的索引,也锁定索引之间的间隙。next-key详细实例 ,Next-key Locks的生效场景:
- 没有设置索引或者没有命中索引,表锁。
- 普通索引,无论哪种情况下,都只有next-key lock。
- 唯一索引在 = x ,且x存在时,降级为行锁,其它情况都是next-key lock。
Read Uncommitted
不使用MVCC,读不加锁,写加排它锁。
Read Committed
上述MVCC的流程只是Repeatable read的过程,并不是Read Committed的流程。
Read Committed采用了MVCC+行锁。
快照读时,只使用MVCC的最新版本的未加X锁的数据快照,无视数据快照的系统版本号。这与Repeatable Read永远读取旧快照不同,这样导致了不可重复读。
当前读时,对使用到数据行对应的S锁或X锁,在commit时释放锁,通过加锁解决并发问题。
Repeatable Read
Read Committed采用了MVCC + next-key Locks。
快照读时,只访问系统版本号小于等于当前版本号的快照。避免了不可重复读,幻影读。
当前读时,必须使用行锁+间隙锁解决幻影读,这样预防了其他事务对指定范围内的数据进行修改。
Repeatable Read通过无锁(快照读)和有锁(next-key Locks)分别在两种情况下解决了幻影读的问题,这种只锁住表的部分记录就可以完成串形化(强制事务串形执行)的效果,是对mysql性能的巨大优化。
并发一致性解决方案总结
2. 如何解决不可重复读?
索引
对于Innodb,主键毫无疑问是一个聚集索引。但是当一个表没有主键,或者没有一个索引,Innodb会如何处理呢。请看如下规则:
1.如果一个主键被定义了,那么这个主键就是作为聚集索引
2. 如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引
3. 如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,改列的值会随着数据的插入自增。
4.自增主键会把数据自动向后插入,避免了插入过程中的聚集索引排序问题。聚集索引的排序,必然会带来大范围的数据的物理移动,这里面带来的磁盘IO性能损耗是非常大的。 而如果聚集索引上的值可以改动的话,那么也会触发物理磁盘上的移动,于是就可能出现page分裂,表碎片横生。所以不应该修改聚集索引。
索引相关资料: 索引相关资料