一个穿着格子衬衫的男人走到他面前，手里拿着一台MacBookPro，看着稀疏的头发，还有那淡定从容的神情。我心一颤，我去了，这位是架构师，架构师来面试我技术面，心里一下子变得躁动起来，表面上很稳，心里却慌了。果然，他把我的简历拿在手里，飞快地扫了一眼，然后用眼角的余光看了我一眼，开始发问。面试官：根据你的简历，你精通Mysql的优化方法。先说说你对Mysql事务的理解。心中大喜，这简单，兄弟，我是北大（背大）的，来面试之前就做好了准备。我：嗯，数据库事务是指由一组SQL语句组成的数据库逻辑处理单元。在这组SQL操作中，要么全部执行成功，要么全部失败。我：这里简单经典的一组sql操作的例子就是transfer。在交易A中，如果要进行转账，则必须借记转账到的账户，增加转账到的账户。两个操作必须同时执行成功，才能保证数据的一致性。面试官：您刚才提到了数据一致性。你知道交易的特点吗？谈谈你的理解。我：Mysql中事务的四大特性主要包括：Atomicity、Consistent、Isalotion、Durable，简称ACID。我：原子性是指事务的原子操作。所有数据修改要么成功执行，要么全部失败。实现事务的原子性是基于基于日志的Redo/Undo机制。我：一致性是指事务执行前后的状态必须一致，可以理解为数据一致性。隔离侧重于事务之间相互隔离而不受影响，这与事务设置的隔离级别密切相关。我：持久化是指一个事务提交后，事务的状态会被持久化到数据库中，即事务被提交，数据的新增和更新也会被持久化到数据库中。我：在我的理解中，原子性、隔离性、持久化都是为了保证一致性而存在的，而一致性也是最终的目标。我心里暗自高兴。我背完之后，一般都会背很多，面试的时候会说。幸运的是，我不会被打扰。面试官：刚才你说原子性是一种基于日志的Redo/Undo机制，你能说说Redo/Undo机制吗？啊哈？说了一句不小心给自己埋了个大雷。别慌，我心里还有事，故作若有所思的停了几十秒，继续念。我：Redo/Undo机制比较简单，他们把对数据的所有更新操作都写到日志中。Me：Redolog用于记录一个数据块的修改值，可用于恢复成功事务更新后未写入数据文件的数据；Undolog用于记录数据更新前的值，保证数据更新失败回滚。我：如果数据库在执行过程中不小心崩溃了，可以通过日志回滚之前执行成功的操作，达到事务的一致性。面试官：能给个场景，说说具体的实现过程吗？我：是的，如果某个时候数据库崩溃了，崩溃前有事务A和事务B在执行，事务A已经提交，事务B还没有提交。当数据库重启进行崩溃恢复时，已提交事务的变化会通过Redolog写入数据文件，未提交的事务会通过Undolog回滚。面试官：您之前也提到了事务的隔离级别，可以谈谈吗？我：是的，Mysql中有四个级别的事务隔离，READUNCOMMITTED、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE。我：Readuncommitted会读取另一个事务未提交的数据，导致脏读问题，readcommitted解决脏读问题，同时出现不可重复读，即一个事务中任意时刻读到的数据都可能是不同的是，数据修改提交后可能会受到其他事务的影响，一般用于更新操作。我：可重复读解决了之前不可重复读和脏读的问题，但是带来了幻读的问题。幻读一般用于插入操作。我：比如：第一个事务查询一张id=100的User表，发现该数据行不存在。这时候，第二个事务又进来了，新增一个id=100的数据行，提交事务。我：这时候在第一个事务中添加一个id=100的数据行会报主键冲突。再次选择第一个事务，发现id=100的数据行已经存在，为幻读。面试官：伙计们，你能示范一下吗？我不是很好。你可以教我吗？我的电脑在这儿，你可以演示给我看。男人的嘴是骗人的鬼，我相信你是鬼，你是坏老头，出来装X总是要还的，只能含泪默默打倒。我：首先创建一个User表，最多的是一个test表，test表中有三个字段，插入两条测试数据。CREATETABLEUser(idINT(11)NOTNULLPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20),ageINTDEFAULT0)ENGINE=InnoDBDEFAULTCHARSET=gb2312;INSERTINTO`user`VALUES(1,'zhangsan',23);INSERTINTO`user`VALUES(2,'lisi',20);我：在Mysql中，可以先查看其默认的隔离级别。可以看到Mysql默认的隔离级别是REPEATABLE-READ。我：首先，让我们演示未提交的读取。首先，将默认隔离级别更改为READUNCOMMITTED。我：在设置隔离级别语句中，setglobaltransactionisolationlevelreaduncommitted，这里的global也可以换成session，global表示全局，session表示当前会话，即当前窗口有效。我：设置隔离级别后，对之前打开的session无效。您需要重新打开一个窗口来设置隔离级别才能生效。我：那开始交易吧。Mysql中启动事务有两种方式：begin/starttransaction，最后提交事务执行commit或者rollback事务。我：当执行begin/starttransaction命令时，它们不是一个事务的起点。执行它们之后的第一条SQL语句表示事务的真正开始。我：这里新开两个窗口，同时开始交易。在第一个窗口中，首先将id=1的数据行名称更新为'non-specializedprofessional'，执行成功。我：然后在第二个窗口执行两个查询，分别是窗口1更新前的查询和更新后的查询。我：第一个session产生的未提交事务的状态，会直接影响到第二个session，也就是脏读。我：读取提交也是如此。事务启动后，第一个事务先执行查询数据，然后第二个会话执行更新操作，但是还没有commit。这是第一次再次选择，数据没有变化。第二个session执行commit后，第一个session再次选择变化的数据。我：这样第一笔交易的查询结果会受到第二笔交易的影响，就是不可重复读的问题。面试官：小伙子，你能画出他执行的过程图吗？你说的让我有点困惑，我还没有完全理解。心中万马奔腾，欲哭无泪。这个面试官真的很难等。他从左边的裤兜里拿出笔，从右边的裤兜里掏出纸，开始画起来。我：这是读取提交的时间线图，读取未提交的时间线图。原理是一样的。进行第二次选择时数据已更改。这是因为面试官看了一眼我的照片，微微点头，嘴角带着若有所思的笑意，我觉得你这个坏老头，还是别刁难我了。面试官：嗯，你继续展示你的可重复阅读。我：嗯，好的，然后repeatableread，和之前一样的操作。我：把两个session打开为REPEATABLEREAD，同时打开事务，先在第一个事务中select，然后在第二个事务中更新数据行，可以发现即使第二个事务已经提交，第一个事务再次执行时，select数据没有变化，解决了不可重复读的问题。我：这里的区别是，在Mysql中，默认的不可重复读隔离级别也解决了幻读问题。我：从上面的演示可以看出，在第一个事务中，先选择了id=3的数据行。该数据行不存在，返回一个Emptyset，然后在第二个事务中插入了id=3的行。datarowandcommit，在第一个事务中再次select，数据也是没有id=3的datarow。我：最后的序列化步骤是一样的样式，结果和Mysql默认的可重复读隔离级别的结果是一样的。序列化执行过程相当于将事务的执行过程改为顺序执行。我这里不再演示了。我：这四个级别从上到下，隔离的效果在逐渐增强，但是性能越来越差。面试官：哦？性能越来越差？为什么业绩越来越差？你能告诉我为什么吗？哦，我的嘴，如果我说一个字，我会死的。现在好了。这就不得不说到底层的实现原理了。从原来的假装周到，变成了真正的周到。我：这要从Mysq锁说起。Mysql中的锁可以分为共享锁/读锁（SharedLocks）、排他锁/写锁（ExclusiveLocks）、间隙锁、行锁（RecordLocks）、表锁。我：四个隔离级别加锁肯定会消耗性能，而readuncommitted没有加任何锁，所以对它没有隔离作用，所以它的性能也是最好的。我：为了序列化，加了一把大锁。读的时候加共享锁，不能写。写的时候加排他锁，阻塞其他事务的写和读。如果其他事务长的时间写不出来，它会直接报超时，所以它的性能也是最差的，它没有并发。我：对于read-commit和repeatableread，两者的实现都是为了解决数据问题，然后一定要有一定的并发释放，所以锁机制的实现会比序列化优化很多，并且并发性会提高，所以性能也会提高。会更好。我：两者的底层实现都是用MVCC（Multi-versionConcurrencyControl）方式实现的。面试官：能先说说这几个锁的概念吗？不是很懂，请说说你的理解。我：哦，好吧，共享锁是针对同一个数据的，可以同时进行多个读操作。简单的说就是读锁，不能写，可以并行读；独占锁用于写操作。如果当前写操作没有完成，那么会阻塞其他的写锁和读锁，也就是写锁，其他的读写都被阻塞。我：行锁和表锁是从锁的粒度来分的。行锁锁定当前数据行。锁的粒度小，加锁慢，锁冲突概率小，并发度高。行锁也是MyISAM与InnoDB的区别之一，InnoDB支持行锁，支持事务。我：表锁锁粒度大，加锁速度快，开销小，但是锁冲突概率高，并发度低。我：有两种类型的间隙锁：间隙锁和下一键锁。GapLocks会对两个索引之间的区间加锁，比如select*fromUserwhereid>3andid<5forupdate，区间(3,5)之间会加GapLocks。我：Next-KeyLocks是GapLocks+RecordLocks组成一个闭区间锁select*fromUserwhereid>=3andid=<5forupdate，Next-KeyLocks会加在区间[3,5]之间.面试官：Mysql什么时候加锁？我：在数据库的增删改查中，只有增删改查才会加排它锁，但是查询不会加锁，只有在select语句后显式加share中的锁模式或更新以添加共享或排他锁。面试官：你上面提到了MVCC（多版本并发控制），能说说原理吗？我：在MVCC的实现中使用了一致性视图来支持readcommit和repeatableread的实现。我：要达到可重复读的隔离级别，只需要在事务开始时创建一个一致性视图，也叫快照。此一致的视图在后续查询中共享。后续事务将数据更改为当前事务。不可见，从而实现可重复读。我：至于readcommit，每条语句执行前都会重新计算一个新的视图。这也是MVCC实现层面repeatableread和readcommit的区别。面试官：你知道快照（视图）在MVCC底层是如何工作的吗？我：在InnoDB中，每个事务都有自己的事务id，是唯一的，增量的。我：Mysql中的每一行数据，可能有多个版本。事务每更新一次数据，都会产生一个新的数据版本，并将自己的数据id赋给当前版本的行trx_id。采访者：小伙子，你能画个图给我看看吗？我真的不明白。我能做些什么？我只好从屁股口袋里掏出笔和纸，飞快地画起来。如果这次面试失败，我就会赔钱。我浪费了两张纸和笔水。现在笔和纸有多贵？，只能全力以赴。我：如图，如果三个事务更新同一行数据，就会有三个对应的数据版本。我：其实version1和version2实际上在物理上并不存在，但是图中的U1和U2其实是undolog。v1和v2版本是根据当前的v3和undo日志计算的。采访者：那么对于快照来说，你知道它要遵循什么规则吗？我：嗯，对于一个事务视图，除了对自己的更新一直可见之外，还有三种情况：版本没有提交，不可见；版本已经提交了，但是是创建视图之后才提交的。可见的;该版本已提交，如果在创建视图之前已提交，则它是可见的。面试官：如果两个事务进行写操作，如何保证并发？我：如果事务1和事务2都需要执行更新操作，那么事务1第一次更新数据行时，先获取行锁，然后锁住数据。当事务2需要执行更新操作时，它也会获取数据行Row锁，但是已经被事务1占用了，事务2只能等待。我：如果事务1长时间不释放锁，事务2会出现超时异常。面试官：这就是update哪里有索引之后的条件吧？我：嗯，是的。面试官：没有索引怎么办？有没有办法快速定位到数据行？Me：如果没有索引，则获取所有行并加行锁，然后Mysql会再次过滤符合条件的行并释放锁，只有符合条件的行才会继续持有锁。我：这种性能消耗会比较大。面试官：嗯，这个时候，面试官看了看手表一个多小时，该吃午饭了。我想他一定是饿了，所以他不会继续问下去。两人僵持了三十秒。他终于开口了。采访者：年轻人，现在该吃午饭了。今天的采访到此结束。回去等通知。我：。.........
