MySQL逻辑架构

image

并发控制

• 读写锁

  • sharelock共享锁，exclusivelock排他锁

• 锁粒度

  • table lock

    尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如，ALTER TABLE之类的语句会使用表锁，而忽略引擎的锁机制

  • row lock

    缩小锁的粒度，提高并发性能，InnoDB还有XtraDB

事务

一组原子性的SQL查询，或者说一个独立的工作单元。

ACID

银行通常是解释事务必要性的一个例子。假设一个银行的数据系统有两张表checking支票表和saving储蓄表，现在要从用户Jane的支票账户转200美元到她的储蓄账户，至少需要三个步骤：

1. 检查支票账户余额高于200美元
2. 从支票账户余额中减少200美元
3. 在储蓄账户中增加200美元

三个步骤必须包含在一个事务中，任何一个步骤失败都会回滚所有的步骤

START TRANSACTION;
SELECT ...
UPDATE ...
UPDATE ...
COMMIT;

• 原子性Atomicity：一个事务必须被视为一个不可划分的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚

• 一致性Consistency：系统总是从一个正确状态到另一个正确的状态。其实是AID赋予的性质，一般正确的状态指的是业务的正确状态。例如银行总的钱数不变

• 隔离型Isolation：通常来说，一个事务提交之前的操作对其他事务不可见。

• 持久性Durability：一旦事务提交，则其所做的操作就会永久保存在数据库中。即使系统崩溃，修改的数据也不会丢失。不可能有能做到100%持久性保证策略（如果数据库本身的持久性能够保障，那么备份又怎么能增加持久性？）

选择非事务型的引擎，可以获得更高的性能，即使存储引擎不支持事务，也可以通过LOCK TABLES语句为Application提供一定程序的保护。

隔离级别

• READ UNCOMMITED（读未提交）：事务中的修改，即使未提交，也能被读取到，容易产生Dirty Read脏读。从性能来说，它不会比其他级别好很多。

• READ COMMITED（读提交）：大多数数据库系统都是该默认级别（MySQL不是），容易造成不可重复读，两次读取的数据不一致。

• REPEATABLE READ（可重复读）：解决了脏读Dirty Read的问题，保证同一事务多次读取同样的数据一致。理论上，还是无法解决幻读Phantom Read的问题。当一个事务在读取某个范围的记录，另一个事务在该范围插入了新的记录，当之前事务再次读取，就会出现幻行Phantom Row。InnoDB和XtraDB利用MVCC解决了该问题

• SERIALIZABLE （可串行化）：强制事务串行执行，but实际上为了提高并发性能，多个可串行化事务经调度后可同时执行

image-20200718142236818

死锁

两个或多个事务在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

例子：

两个事务同时对StockPrice表操作，即使InnoDB存在RowLock

image-20200718142645299

InnoDB在检测到死锁的循环依赖，会立即返回一个错误。

• 当查询时间达到锁等待的超时设定后放弃锁请求，不太友好。

• InnoDB目前处理死锁：将持有最少行级排他锁的事务进行回滚。

锁的行为和顺序跟存储引擎有关，相同的语句在不同的引擎不同

双重原因：

1. 数据冲突
2. 存储引擎的实现方式

死锁发生后，只有部分或者完全回滚其中一个事务，才能打破死锁。事务型系统是无法避免，所以application设计时候必须考虑如何处理死锁，大多数情况下只需要重新执行死锁回滚的事务即可。

事务日志

事务日志能够提高事务的效率，采用WAL write ahead logging，先写事务日志，再刷盘。

事务日志是顺序写的，也就是append-only，实际上有研究表明，顺序写磁盘有可能会比内存随机读写更快。

具有crash-safe能力。

MySQL中的事务

支持事务的引擎：InnoDB和NDB cluster，第三方的XtraDB和PBXT。

自动提交AUTO COMMIT

MySQL默认采用自动提交，如果不是显式开启一个事务，每个查询都会被当作一个事务执行提交操作。

当前连接中，通过设置AUTO COMMIT变量来启用或禁用“自动提交”：

show variables like 'AUTOCOMMIT';
SET AUTOCOMMIT = 1;

注意事项：

• 当AUTOCOMMIT=0时候，所有查询都是在一个事务中，知道显式commit或者rollback。

• 修改AUTOCOMMIT对非事务型的表，比如MyISAM或者内存表，不会有任何影响

• 有的命令会在执行前强制执行COMMIT提交当前事务。DDL中，如果是导致大量数据改变的操作，如ALTER TABLE，还有LOCK TABLES等，需要可查询

• MySQL可通过SET TRANSACTION ISOLATION LEVEL来设置隔离级别，新的隔离级别会在下一个事务开始生效，也可在配置文件设置整个数据库的隔离级别。

  设置当前会话的隔离级别：

  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

在事务中混合使用存储引擎

MySQL的Server不管理事务，存储引擎去管。所以，不要在同一个事务中使用多个存储引擎。

如果混用了事务型和非事务型，在正常提交的状况不会有问题。

但如果需要回滚，非事务型的表上的变更就无法撤销，导致数据处于不一致的数据状态。

而且在非事务型的表上执行事务相关操作，MySQL通常不会发提醒，也不会报错，回滚时候可能会发警告。

隐式和显式锁定

InnoDB采用的是两阶段锁定协议（two phase locking protocol），事务执行过程随时可以执行锁定，但是解锁会在commit或者rollback同一时刻释放。InnoDB会根据隔离级别在需要情况下隐式锁定。

InnoDB支持显式锁定

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE   

MySQL也支持LOCK TABLES 和UNLOCK TABLES，但是在SERVER层实现的，不能替代性能。

如果从MyISAM迁移到InnoDB就不要再使用LOCK TABLES，严重影响性能

• LOCK TABLES和事务之间相互影响很麻烦，除了事务禁用了AUTOCOMMIT可以使用LOCK TABLES之外，建立不要用了，不管什么引擎

多版本并发控制MVCC

MySQL大多数的事务型引擎都不是简单的行级锁，一般实现MVCC

MVCC可认为是行级锁的变种，很多情况避免了加锁，提高并发性能。

MVCC实现，通过保存数据在某一个时间点的快照来实现。不管事务执行多长时间，每个事务看到的数据都是一致的。

不同存储引擎的MVCC实现会不一样，典型有乐观optimistic并发控制还有悲观pessimistic并发控制。

InnoDB的实现：

• 每行记录后面保存两个隐藏的列，一个是行的创建时间，一个是行的删除时间（或过期时间），时间其实是指系统版本号。每开始一个新的事务，系统版本号+1，开始时候的系统版本号作为事务的版本号。

SELECT：

• InnoDB只查找版本号早于当前版本的数据行（小于或等于），事务开始已经存在或者自身修改或插入
• 行的删除版本未定义，要么大于当前事务版本号，事务开始之前未被删除

INSERT：

• 为新插入的每一行保存当前系统版本号

DELETE：

• 为删除的每一行保存当前系统版本号作为标识

UPDATE：

• 为插入一条新记录，保存当前版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

优点：提高了性能

缺点：增大了存储

MySQL的存储引擎