MySQL逻辑架构
image
并发控制
-
读写锁
- sharelock共享锁，exclusivelock排他锁
-
锁粒度
-
table lock
尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如，ALTER TABLE之类的语句会使用表锁，而忽略引擎的锁机制
-
row lock
缩小锁的粒度，提高并发性能，InnoDB还有XtraDB
-
事务
一组原子性的SQL查询，或者说一个独立的工作单元。
ACID
银行通常是解释事务必要性的一个例子。假设一个银行的数据系统有两张表checking支票表和saving储蓄表，现在要从用户Jane的支票账户转200美元到她的储蓄账户，至少需要三个步骤：
- 检查支票账户余额高于200美元
- 从支票账户余额中减少200美元
- 在储蓄账户中增加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目前处理死锁：将持有最少行级排他锁的事务进行回滚。
锁的行为和顺序跟存储引擎有关，相同的语句在不同的引擎不同
双重原因：
- 数据冲突
- 存储引擎的实现方式
死锁发生后，只有部分或者完全回滚其中一个事务，才能打破死锁。事务型系统是无法避免，所以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：
- 为插入一条新记录，保存当前版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识
优点：提高了性能
缺点：增大了存储
网友评论