Transaction

MySQL的逻辑架构图。

最上层是连接管理、授权认证、安全等。每个客户端连接会在服务器中拥有一个线程。连接时，服务器首先对其进行认证，基于用户名、原始主机细信息和密码。成功后会进一步验证是否具有执行某个特定查询的权限。

第二层包括查询解析、分析、优化、换成以及所有内置函数（如，日期、时间、数学等），所有跨存储引擎的功能：存储过程、触发器、视图等。mysql会解析查询，创建内部数据结果（解析树），进行诸如，重新查询、决定表的读写顺序，以及如何选择合适的索引等。可以通过hint提示优化器，影响决策过程；也可以explain，请求优化器解释优化过程的各个因素。

第三层是存储引擎。

并发控制

并发控制最简单的思路是使用锁。但锁的方法其实质并不支持并发处理，因为任意时刻只有一个操作可以获得锁。

读写锁

共享锁（shared lock）和排他锁（exclusive lock）,或者叫读锁和写锁。

读锁是共享的，互不阻塞的。同一时刻可以同时读取，互不干扰。写锁则是排他的，写锁会阻塞其他的写锁和读锁，这是出于安全策略（同一时刻只有一个人在写，防止其他人读到正在写入的数据）。当修改某一部分数据的时候，就会发生锁定。

锁粒度

一种提高共享资源并发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有，也就是控制锁的粒度。

但是加锁需要消耗资源，锁的各种操作包括，获取、检查是否已经解除、释放等，都会增加系统开销。如果系统花费大量时间管理锁而不是存储数据，则系统的系统将产生很大影响。mysql支持多种锁策略，最重要的两种是表锁和行级锁。

表锁是最基本的锁策略，也是开销最小的。一个用户对表进行写操作（插入、删除、更新），就需要获得写锁，会阻止其他用户对该表的所有读写操作，只有没有写锁时，其他读取的用户才能获得读锁，读锁间不阻塞。虽然存储引擎可以管理自己的锁，但mysql会使用有效的表锁来实现不同的目的，如alter table会使用表锁而忽略存储引擎的锁机制。

行级锁可以最大程度的地支持并发处理（也带来最大的锁开销）。行级锁只在存储引擎中实现，mysql服务器层没有实现，见前文逻辑架构图。

事务

ACID

原子性（atomicity）很容易理解，不可分割。

一致性（consistency）数据一致性，从一个状态转移到另一个一致性状态，有种质量守恒定律的感觉。

隔离性（isolation）通常来说，一个事务所做的修改在最终提交前，对其他事务是不可见的。

持久性（durability）一旦事务提交，修改就永久保存。

隔离级别

READ UNCOMMITTED 未提交读

事务中的修改即使没有提交，也对其他事务是可见的。当事务读取到未提交的数据时成脏读（Dirty Read）。优点不明显，但却具备其他隔离级别的缺点。

READ COMMITTED 提交读

大多数数据库系统的默认隔离级别（但mysql不是）。它满足隔离性简单要求，只能看见已经提交事务的修改，但是不可重复读（nonrepeatable read），两次执行统一的查询，可能得到不同的结果，因为执行过程中可能有其他事务提交结果。在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。

REPEATABLE READ 可重复读

保证在同一个事务中多次读取结果一致。这是mysql默认的事务隔离级别。会产生幻读（Phantom Read），在范围读取时，读取到其他事务插入的数据。不可重复读在于update和delete，而幻读在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

为解决幻读问题，大多数事务型存储引擎实现的都不是简单的行级锁，基于提升性能考虑，使用基于乐观锁为理论基础实现的多版本并发控制（MVCC），它是行级锁的变种，在很多情况下避免了加锁操作，因此开销更低。因为MVCC没有统一的标准，所以各自的实现机制不尽相同。实现非阻塞的读操作，写操作只锁定必要的行。

InnoDB的简化版行为说明MVCC：在每行记录后面保存两个隐藏的列，一个保存行的创建时间一个保存行的删除时间，时间为系统的版本号，每开始一个新的事务，系统版本号就自动递增。

SELECT

InnoDB只查找版本小于等于当前版本号的数据行，这样可以保证事务读取的行，要么是之前已经存在的要么是自身插入或修改的
行的删除版本要么未定义，要么大于当前版本号。这可以确保数据行在事务开始前未被删除。

两者都满足，返回的是事务开始之前（或自己插入修改的）已经存在的数据且还没有被删除的。保证不会出现脏读、不可重复读和幻读。

INSERT

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

DELETE

为删除的每一行保存删除版本号。

UPDATE

插入一行新纪录，保存当前版本号；同时保存当前系统版本号到原来的行作为删除版本号。

SERIALIZABLE 可串行化

最高的隔离级别，强制事务串行执行。

隔离级别	脏读	可重复读	幻读	加锁读
READ UNCOMMITTED	YES	YES	YES	NO
READ COMMITTED	NO	YES	YES	NO
REPEATABLE READ	NO	NO	YES	NO
SERIALIZABLE	NO	NO	NO	YES

事务日志

存储引擎在修改表数据的时候只需要修改其在内存中的拷贝，然后将该行为记录持久DOA硬盘上的事务日志中，而不用每次都将修改的数据本持久到硬盘。由于事务日志采用追加的方式，所以是顺序IO，相对更快。日志持久到硬盘后，后台可以慢慢地刷回硬盘。将其称之为预写式日志（Write-Ahead Logging），修改数据需要两次写磁盘。

如果在刷新过程中出现问题，重启后，存储引擎可以自动恢复这部分数据。

自动提交

mysql默认采用自动提交（AUTOCOMMIT）模式。如果不显示地开始一个事务，则每个查询被当做一个事务执行提交操作。

show variables like 'AUTOCOMMIT';
set AUTOCOMMIT = 1;
#1和ON表示启用， 0和OFF表示停用。

当AUTOCOMMIT=0时，所有查询都在一个事务中，知道显示地执行commit或rollback。

对于非事务型的表，相当于一直处于AUTOCOMMIT启用的状态。

mysql和InnoDB支持所有的4个ANSI隔离级别，如果想要修可以使用

set session transaction isolation level read committed;

显示锁定

select ... lock in share mode
select ... for update

因为有大量的并发访问，为了预防死锁，一般应用中推荐使用一次封锁法，就是在方法的开始阶段，已经预先知道会用到哪些数据，然后全部锁住，在方法运行之后，再全部解锁。这种方式可以有效的避免循环死锁，但在数据库中却不适用，因为在事务开始阶段，数据库并不知道会用到哪些数据。

数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。

解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

begin；

insert into test ….. 加insert对应的锁

update test set… 加update对应的锁

delete from test …. 加delete对应的锁

commit; 事务提交时，同时释放insert、update、delete对应的锁

这种方式虽然无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化（串行化很重要，尤其是在数据恢复和备份的时候）的。

一些其他和schema相关的术语

范式和返范式混用

缓存表（解决获取速度慢）、汇总表（解决group by聚合）

计数器表