【MySQL】MySQL架构与基础概念(锁概念、事务)

1 MySQL架构

最上层（客户端层）：客户端调用MySQL存储服务

第二层（服务器层：连接/线程处理、解析器、查询缓存、优化器）：MySQL核心服务，包括查询解析、分析、优化、缓存以及内置函数（日期、时间、加密等等），跨存储引擎功能在此处实现：存储过程、触发器、视图等等。

第三层（存储引擎层）：负责MySQL的存储和提取。

2 锁概念

2.1、读写锁

当多个客户并发的读取数据库或写的时候，会出现并发的问题。这是实现两种类型锁组成的锁系统解决问题。共享锁（shared lock）与排他锁（exclusive lock），也称为读锁和写锁。

2.2、锁粒度

提高共享资源并发性方式是尽量只锁定需要修改的部分，而不是所有的资源。但是加锁是需要消耗资源的，锁的各种操作，包括获得锁、检查锁是否解除、释放锁等等，都会增加系统开销。

所策略就是在锁的开销与数据的安全寻求平衡，一般的商业数据库，是在表上施加行锁(row-level lock),并且以各种复杂的方式实现。

MySQL提供了多种选择，可以实现自己的锁策略和锁粒度。锁的粒度固定在某个级别，会对特定方案生效 但是对其他场景不太支持，所以mysql更加灵活。

2.3、锁策略（表锁、行锁）

（1）表锁（table lock）

表锁是MySQL最基本的锁策略，并且开销最小的策略。表锁会锁定整张表。一个用户在对表进行写操作（插入、删除、更新等）前，需要先获取写锁，会阻塞其他用户对该表的所有读写操作。只有没有写锁的时候，其他读取的用户才能获取读锁，读锁之间是不互相阻塞的。

写锁比读锁有更高优先级，因此写锁请求，可能会被插入到读锁列的前面。

（2）行锁（row lock）

行锁可以最大程度支持并发操作（但是会带来最大的锁开销）。行锁只在存储引擎层

2.4、死锁

死锁是两个或多个事务在同一个资源上相互占用，并请求锁定对方占用的资源。导致恶性循环。解决这个问题数据库实现了死锁检测与死锁超时机制。InnoDB使用回滚，将持有最少行级锁的事务回滚。

3 事务

3.1、事务概念

事务就是一组原则性的SQL查询（独立不可分割的运行工作单元），如果数据库可以成功应用这一组查询的全部语句，就执行该组查询。如果有任何一句因为崩溃或其他原因无法执行，则全部都不会执行。

事务内部的语句，要么全部执行成功，要么都执行失败。

3.2、经典例子：银行转账

两个人转账A向B转账200元，三个步骤：

1、检查A账户余额大于等于200元

2、从A账户减去200元

3、B账户增加200元

三个步骤的操作，必须要打包在一个事务当中，如果有任何一个事务失败，那么就必须回滚所有步骤。

SQL中我们可以使用start transaction语句开启一个事务，使用commit语句提交事务将修改的数据持久化保存，如果失败则使用rollback语句撤销所有修改。

START TRANSACTION;
SELECT balance from accountA WHERE customer_id = 1;
UPDATE accountA set balance = balance - 200 where customer_id = 1;
UPDATE accountB set balance = balance + 200 where customer_id = 2;
COMMIT;

3.3、ACID原则

一个运行良好的事务处理系统，必须具备ACID标准特征

ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）。

原子性（atomicity）

一个事务必须被看做一个不可分割的最小的工作单元。整个事务中的所有操作，要么全部提交成功，要么全部失败，进行回滚。对于一个事务，不可能出现只执行其中一小部分的操作，就是事务的原子性。

一致性（consistency）

数据库总是从一个一致性的状态转移到另外一个一致性的状态。

前面银行转账例子，即使3 4句执行失败，账户也不会损失200 或 凭空多200 因为没有提交到数据库

隔离性（isolation）

一个事务开始时，只能"看见"已经提交事务所做的修改。

通常而言，一个事务所做的修改在最终提交前，对其他事务是不可见的。前面例子中执行完前三条语句，第四条语句未开始时，此时有另外一个A账户向B账户的转账程序开始，其看到的账户余额并没有被减去200。

后面我们会谈到**隔离级别(isolation level)**会发现我们所说的通常而言，与其他情况。

持久性（durability）

一旦事务提交，其所作的修改就会永久保存在数据库中。即使系统崩溃，修改的数据也不会丢失。

3.4、脏读、不可重复读、幻读

脏读（Dirty Read）

事务读取到了未提交的数据。A事务读取到B事务未提交的事务修改结果。

不可重复读（non-repeatable read）

一个事务从开始到提交之前，所做的修改，对其他事务都是不可见的。在一个事务内读取表中的某一行数据，多次读取结果不同。（不一定是错误，只是某些场合不对）

幻读（Phantom Read）

某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事物再次读取该范围内的记录时，会产生幻行。简单说，就是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

4 事务的隔离级别

事务的隔离性（isolation）其实在具体业务场景中会比想象复杂很多，在SQL中定义了四种隔离级别。每一种的隔离级别都规定了一个事务A中做的修改，在其他事务内与事务之间是可见的。哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也会降低。

4.1、四种隔离级别

READ UNCOMMITED （未提交读）

（1）概念：未提交读级别，事务的修改，及时是没有提交，对其他的事物也都是可见的。

未提交读就是脏读。

（2）与脏读关系：这种事务可以读取到未提交的事务的数据，就是脏读（Dirty Read）。

（3）问题：这个级别会有很多问题，性能上，READ UNCOMMITED （未提交读）并不会比其他级别好很多，但是却缺乏其他级别的好处，非必要一般不使用。

READ COMMIT（提交读）

（1）概念：提交读满足前面的事务隔离性的基本定义，一个事务开始时，只能"看见"已经提交事务所做的修改。也即，一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的。但两次执行相同的查询，可能会得到不同的结果。

（2）与不可重复读关系：这个级别也可以叫做不可重复读。两次执行相同的查询，可能会得到不一样的结果。

REPEATABLE READ（可重复读）

（1）概念：保证在同一个事物中多次读取同样的记录，结果是一致的。(解决了脏读的问题)

（2）问题：存在幻读问题。当事务A在读取某个范围的记录，另外一个事务B在该范围内，插入了新的数据记录，之前事务A读取的事务读取该范围就会出现该幻行记录。

（3）问题解决：InnoDB和XtraDB存储的引擎，通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。

（4）默认级别：MySQL默认级别就是可重复读。

SERIALIZABLE（可串行化）

（1）概念：最高的隔离级别。通过强制事务串行化执行，禁止事务并行，避免了前面的幻读问题。

（2）问题：可串行化会在读取的每一行数据都加上锁，可能会导致大量的超时和锁争用的问题。实际应用中，很少用到这个隔离级别，只有非常需要保证数据一致性的时候才会用到。

4.2、四种隔离级别表格小结

5 MySQL中的事务

MySQL提供两种事务型存储引擎：InnoDB与NDB Cluster。

5.1、自动提交（AUTOCOMMIT)

MySQL默认采用自动提交模式。如果不显示的开始一个事务，则每个查询都被当做一次事务执行操作。

MySQL可以通过执行

SET TRANSACTION ISOLATION LEVEL

来设置隔离级别。可以识别4个隔离级别。

5.2、隐式和显示锁定

InnoDB采用两阶段锁定协议(two-phase locking protocol)。在事务执行过程中，随时可以执行锁定，锁只有在执行COMMIT或者ROLLBACK的时候才会释放。并且所有的锁是在同一时刻被释放。前面所说的都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。

6 多版本并发控制(MVCC)

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁，基于提升并发性能的考虑，一般同事实现了多版本并发控制(MVCC)。

可以认为MVCC是行级锁的一种变种，很多情况避免加锁操作。MVCC的实现是通过保存数据在某个时间点的快照实现，不管需要执行多长时间，每个事务看到的数据都是一致的，根据事务开始的时间不同，每个事务对同一张表，同一时间看到的数据可能不一样。

不同引擎的MVCC实现不同，典型有：乐观(optimistic)并发控制和悲观(pessimistic)并发控制。

悲观并发控制：数据库程序对数据修改持悲观态度，整个数据处理过程都会锁定，解决锁竞争问题。

乐观并发控制：乐观并发控制也叫乐观锁，但是它并不是真正的锁，很多人都会误以为乐观锁是一种真正的锁，然而它只是一种并发控制的思想。乐观并发控制其实本质上就是基于验证的协议作为乐观的并发控制机制，它会假定所有的事务在最终都会通过验证阶段并且执行成功，而锁机制和基于时间戳排序的协议是悲观的，因为它们会在发生冲突时强制事务进行等待或者回滚，哪怕有不需要锁也能够保证事务之间不会冲突的可能。

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列，一个是保存了行的创建时间，另外一个是保存行的过期时间（或删除时间）。不是实际时间值，而是版本号(system version number)。每开始一个新的事务，系统版本号都会递增。事务开始时刻系统的版本号会作为事务的版本号，用来和查询到的每行的记录的版本号进行比较。

在REPEATABLE READ隔离级别下MVCC具体操作：

保存两个额外版本号（每一个数据项都有两个时间戳，读时间戳和写时间戳）读写操作的时候会比较时间戳的值，如果时间戳小于当前值就会拒绝后回滚，给回滚的事物添加一个新的时间戳重新执行。

7 InnoDB与MyISAM存储引擎

7.1 InnoDB存储引擎

默认事务型引擎。设计用于处理大量短期事务，短期事务一般是正常提交很少回滚。

InnoDB性能和自动奔溃恢复特性，因此在非事务型存储也流行。

InnoDB采用MVCC来支持高并发，实现了4个隔离级别。默认级别是REPETABLE READ, 并且实现间隙锁策略防止幻读出现。间隙锁用于InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻行插入。间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现幻读现象。

InnoDB基于聚簇索引，聚簇索引对主键查询性能高，但是二级索引（非主键索引）中必须包含主键列，所以如果主键列很大，那么其他所有的索引都很大，因此表上索引较多，主键应该尽可能小。

7.2、MyISAM存储引擎

MyISAM不支持事务和行级锁，缺陷是奔溃后无法安全回复。

加锁与并发：MyISAM对整张表加锁，而不加行锁，所以读取的时候需要读到的所有表加共享锁，写入时则对表加排他锁。在表有读取查询的时候，可以往表中插入新的记录（并发插入）

修复：区别于奔溃恢复。检查表是否有已知错误类型。但是修复很慢，并且可能出现数据丢失。

推荐使用InnoDB

8 小结

本文主要介绍了MySQL基本概念

（1）MySQL的三层架构（客户端，服务层，存储引擎层）

（2）锁概念（表锁、行锁、锁粒度）

（3）事务与事务的隔离级别

（4）介绍InnoDB与MyISAM。

行锁，所以读取的时候需要读到的所有表加共享锁，写入时则对表加排他锁。在表有读取查询的时候，可以往表中插入新的记录（并发插入）

修复：区别于奔溃恢复。检查表是否有已知错误类型。但是修复很慢，并且可能出现数据丢失。

推荐使用InnoDB

8 小结

本文主要介绍了MySQL基本概念

（1）MySQL的三层架构（客户端，服务层，存储引擎层）

（2）锁概念（表锁、行锁、锁粒度）

（3）事务与事务的隔离级别

（4）介绍InnoDB与MyISAM。