MYSQL与MYSQL事务

MySql的逻辑架构

mysql的逻辑架构分为3层

• 第一层架构功能比较常见，负责授权认证、连接处理、安全等功能
• 第二层架构是mysql的核心功能区。查询、分析、优化、缓存以及所有的内置函数都在这一层，所有跨存储引擎的功能也在这一层
• 第三层架构为存储引擎层，负责mysql中数据的存储和提取。

相关知识点

• MYSQL的独特的存储引擎架构将查询处理和其他系统任务与数据的存储、提取相分离，这种处理和存储分离的设计可以在使用时根据性能、特性以及其他需求来选择数据存储的方式。
• MYSQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引等，可以通过配置关键字提示MYSQL的决策过程。
• 对于SELECT语句，在解析查询之前，服务器会先查询缓存，如果能够在其中找到对应的查询，服务器就不再执行解析、优化、执行的过程，直接返回缓存中的数据。

MYSQL中的事务

事务日志

预写式日志：存储引擎在修改表的时候，想将其修改行为按顺序持久化到磁盘的事务文件中，在事务提交之后将修改后的数据刷入到磁盘中。

锁粒度

提高数据库共享资源的并发性的有效方式是让锁更具有选择性，只锁定修改部分的数据，而不是所有的资源。锁定数据越少，系统的并发性越高。但是处理锁的各种操作也要花费大量时间，获取锁、检查锁、释放锁，都会增加系统的开销。因此锁策略就是在锁开销和安全性之间寻找平衡。
每个MYSQL存储引擎都可以实现自己的锁策略和锁粒度,下面两个是常见的锁

• 表锁。表锁是开销最小的锁。用户对表内数据进行写操作，首先要获取写锁，会阻塞其他用户对该表的所有操作。没有写锁时，其他用户可以获取读锁，读锁之间不阻塞。MYSQL本身也会通过各种有效的表锁来取代存储引擎的表锁。
• 行锁。行锁最大程度的支持并发，但是也占用了最高的开销。行级锁只在存储引擎层实现，服务器层不了解存储引擎中的锁实现。如 InnoDB和XtraDB引擎中实现了行级锁。

死锁

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

事务1
update person set name = "zhangsan" where id = 1;
update person set name = "lisi" where id = 2;
事务2
update person set name = "zhangsan" where id = 2;
update person set name = "lisi" where id = 1;

死锁产生原因

• 数据冲突
• 存储引擎的实现方式

数据库系统实现了死锁检测和死锁超时机制。死锁产生后，只有部分或者完全回滚其中一个事务，才能解决。

• InnoDB，检测到死锁循环后，会将持有最少行级排他锁的事务进行回滚。

事务的隔离级别

SQL标注中定义了4种隔离级别，每一个级别都定义了一个事务中所作的修改，级别低的隔离可以执行更高的并发，并且开销更低。

• READ UNCOMMITTED 读未提交。事务可以读取其他事务未提交的操作，产生脏读。
• READ COMMITTED 读已提交。事务只能看到其他事务已经提交的操作。不可重复读，可能会导致前后两次读取的结果不一致，大多数数据库默认都是这个隔离级别。
• REPEATABLE READ 可重复读。它保证了在同一事务中多次读取同样的操作结果是一致的，但是可能导致幻读，指某个范围内的记录条数不一致。MYSQL是这个隔离级别，它的InnoDB和XtraDB存储引擎通过MVCC(多版本并发控制)解决了幻读的问题。
• SERIALIZABLE 串行化。强制事务串行执行，它会在执行的每一行数据上都加锁。只有在非常需要确保数据的一致性并且可以接受没有并发的情况下才考虑该级别。

查看和设置当前隔离级别

// 查看当前隔离级别
SELECT @@tx_isolation
// 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITED
// 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED

自动提交

MYSQL中默认操作自动提交，也可以通过显示操作开启和结束一个事务。下面语句可以查看自动提交的开启状态

// 查询自动提交状态sql
SHOW VARIABLES LIKE 'AUTOCOMMIT';
// 设置自动提交
SET AUTOCOMMIT = 0;

当自动提交关闭时，默认后续操作在一个事务中，直到执行COMMIT或者ROLLBACK。

注意

• 对于非事务型的存储引擎，不支持回滚操作，相当于每一次提交都是一个事务，因此除非特殊情况不然不要和事务型存储引擎一同在事务中使用
• 所有的DDL语句、LOCK TABLES等也是默认自动提交

多版本并发控制-MVCC

MYSQL中的大多数事务型存储引擎的实现都不是靠简单的行级锁，为了提高并发性，它们大多都实现了MVCC。MVCC的实现，是通过保存数据在某个时间点的快照来实现的，不管事务需要执行多长时间，它看到的数据总是一致的；如果事务开始的时间不同，每个事务对同一张表同一时间看到的数据可能是不一样的。
可以把MVCC看做行级锁的变种，但是它在很多情况下避免了加锁的操作，因此开销更低。它在大多数情况下实现了非阻塞的读操作，写操作也只锁定必须的行。

MVCC的具体实现方式分类

通过InnoDB举例：
InnoDB在存储的每行数据后面增加了两个隐藏列，用于存放创建版本号和删除版本号。每开始一个事务，系统版本号都会递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号做比较。

• SELECT：查询的行的系统版本号小于或等于该事务的事务版本号；行的删除版本号要么未定义，要么大于当前的事务版本号
• INSERT：为插入的每一行保存当前的事务版本号作为行的系统版本号
• DELETE：为删除的每一行保存当前的事务版本号作为行的删除版本号
• UPDATE：插入一条新的数据，保存当前的事务版本号作为行版本号，保存当前的事务版本号作为之前的行删除版本号

MYSQL中的事务存储型存储引擎

• InnoDB
• NDB Cluster
• XtraDB
• PBXT