MySQL

MySQL服务器逻辑架构图

它是由C和C++编写的，对于数据库连接还得明确以下两个概念：

• ODBC(Open Database Connectivity):是用于连接数据库管理系统的接口协议，与操作系统、编程语言、数据库系统无关，只需要使用该数据库提供的OBJC driver。
• JDBC(Java DatabBase Connectivity):是由JAVA语言提供的数据库接口API，可以用于连接实现了JDBC driver的数据库，比如：MySQL提供的mysql-connector-java.jar

ACID特性

• Atomicity - In a transaction involving two or more discrete pieces of information, either all of the pieces are committed or none are.
• Consistency - A transaction either creates a new and valid state of data, or, if any failure occurs, returns all data to its state before the transaction was started.
• Isolation - A transaction in process and not yet committed must remain isolated from any other transaction. Each transaction behaves as if it’s the only one that is executing in the system.
• Durability - Once the system commits a transaction, that transaction must persist in the system, even if the system crashes or loses power

InnoDB存储引擎的行结构

InnoDB表数据的组织方式为主键聚簇索引，二级索引（非聚簇索引）中采用的是(索引键值, 主键键值)的组合来唯一确定一条记录。InnoDBl默认为每个索引行添加了4个隐藏的字段：

• DB_ROW_ID：InnoDB引擎中一个表只能有一个主键,用于聚簇索引,如果表没有定义主键会选择第一个非Null的唯一索引作为主键,如果还没有,生成一个隐藏的DB_ROW_ID作为主键构造聚簇索引。
• DB_TRX_ID：最近更改该行数据的事务ID。
• DB_ROLL_PTR：undo log的指针,用于记录之前历史数据在undo log中的位置。
• DELETE BIT：索引删除标志,如果DB删除了一条数据,是优先通知索引将该标志位设置为1,然后通过(purge)清除线程去异步删除真实的数据。

Undo Log

当我们对记录做了变更操作时就会产生Undo记录，Undo记录默认被记录到系统表空间(ibdata)中，但从5.6开始，也可以使用独立的Undo表空间。Undo记录中存储的是老版本数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录。当版本链很长时，通常可以认为这是个比较耗时的操作。
为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚段（Rollback Segment，简称Rseg）的方式来维护undo log的并发写入。回滚段实际上是一种 Undo 文件组织方式，每个回滚段又有多个undo log slot。
结合行结构，如果有两个事务先后修改同一行数据，如下图所示：

Redo Log

例如：数据库中A=1，现在需要update A=3
那么整个步骤如下：

1. 事务开始
2. 记录A=1到undo log
3. 修改A=3
4. 将undo log写入磁盘
5. 将A=3数据写入磁盘
6. 事务提交

这个就是undo log工作流程，也就是在数据库断电或者crash的时候，在进行恢复的时候，把undo log里面的数据写回到数据库，这样就让数据回滚了。这样实现了事务的原子性，同时保证了数据的一致性。但是，这样每个操作都会进行磁盘IO的写入，频繁的磁盘IO对性能是很大的降低。

引入redo log实现持久性，这个时候就在考虑如果只需要将日志写入磁盘，将数据缓存在内存中，一定时间后再进行更新。
例如：数据库中A=1,B=2,需要update A=3,B=4

1. 事务开始
2. 记录A=1到undo log
3. 修改A=3
4. 记录A=3到redo log
5. 记录B=2到undo log
6. 修改B=4
7. 记录B=4到redo log
8. 将redo log顺序写入磁盘
9. 事务提交

整个过程中，数据修改都是在内存中，极大提升磁盘IO速度，而且将redo log提前写入磁盘，这就是Write-Ahead-Logging(WAL)。
如果整个事务执行的过程系统崩溃或者断电了，在系统重启的时候，恢复机制会将redo log中已提交的事务重做，保证事务的持久性；而undo log中未提交的事务进行回滚，保证事务的原子性。

Redo Log由两部分组成，一是内存中的redo log buffer，是易失的。二是redo log file，是持久的。参数innodb_flush_log_at_trx_commit用来控制redo log buffer刷新到redo log file的策略：
0：表示事务提交时不进行写redo log file的操作，这个操作仅在master thread中完成（master thread每隔1秒进行一次fsync操作）。
1：默认值，表示每次事务提交时进行写redo log file的操作。
2：表示事务提交时将redo log写入文件，不过仅写入文件系统的缓存中，不进行fsync操作

与Mysql中Binlog进行比较：

• Binary Log是在Mysql server层实现，Redo Log是在Innodb存储引擎中实现
• Binary Log是记录的SQL逻辑语句，Redo Log是以512字节的block记录的物理格式上的日志，是数据库中每个页的修改
• Binary Log只在提交的时候一次性写入，一次提交对应一次记录。redo log文件中同一个事务可能多次记录，不同事务之间的不同版本的记录会穿插写入到redo log文件中，例如可能redo log的记录方式如下：T1-1,T1-2,T2-1,T2-2,T2,T1-3,T1 。

Atomicity

Mysql的原子性由Undo Log机制保证，如果事务失败就用Undo Log进行回滚

Isolation

隔离性是为了应对多个事务并发进行时的问题，可以分为以下几个隔离级别：

• READ UNCOMMITTED（读未提交）：在此级别里，事务的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，也就是会产生脏读，还有后面提到的不可重复读和幻读问题，在实际应用中一般很少使用。
• READ COMMITTED（读已提交）：大多数数据库系统的默认隔离级别都是它，但是MySQL不是。它能够避免脏读问题，但是在一个事务里对同一条数据的多次查询可能会得到不同的结果，也就是会产生不可重复读问题。
• REPEATABLE READ（重复读）：该隔离级别是MySQL默认的隔离级别，看名字就知道它能够防止不可重复读问题，但是在一个事务里对一段数据的多次读取可能会出现多行或少行的情况，也就是会有幻读的问题。
• SERIALIZABLE（串行化）：该隔离级别是级别最高的，它通过锁来强制事务串行执行，避免了前面说的所有问题。在高并发下，可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，因为RR级别解决了所有问题。

Mysql在实现READ COMMITTED和REPEATABLE READ时用到了MVCC：

MVCC

Multiversion concurrency control，在每次事务开始时，Mysql会根据当前事务链表（当一个事务开始的时候,会将当前数据库中正在活跃的所有事务--执行begin但是还没有commit的事务--保存到一个叫trx_sys的事务链表中,事务链表中保存的都是未提交的事务,当事务提交之后会从其中删除）创建一个ReadView：

// Friend declaration
class MVCC;
/** Read view lists the trx ids of those transactions for which a consistent
read should not see the modifications to the database. */
...
class ReadView {
    ...
    private:
        // Prevent copying
        ids_t(const ids_t&);
        ids_t& operator=(const ids_t&);
    private:
        /** Memory for the array */
        value_type* m_ptr;
        /** Number of active elements in the array */
        ulint       m_size;
        /** Size of m_ptr in elements */
        ulint       m_reserved;
        friend class ReadView;
    };
public:
    ReadView();
    ~ReadView();
    /** Check whether transaction id is valid.
    @param[in]  id      transaction id to check
    @param[in]  name        table name */
    static void check_trx_id_sanity(
        trx_id_t        id,
        const table_name_t& name);
    /** Check whether the changes by id are visible.
    @param[in]  id  transaction id to check against the view
    @param[in]  name    table name
    @return whether the view sees the modifications of id. */
    bool changes_visible(
        trx_id_t        id,
        const table_name_t& name) const
        MY_ATTRIBUTE((warn_unused_result))
    {
        ut_ad(id > 0);
        if (id < m_up_limit_id || id == m_creator_trx_id) {
            return(true);
        }
        check_trx_id_sanity(id, name);
        if (id >= m_low_limit_id) {
            return(false);
        } else if (m_ids.empty()) {
            return(true);
        }
        const ids_t::value_type*    p = m_ids.data();
        return(!std::binary_search(p, p + m_ids.size(), id));
    }
    
private:
    // Disable copying
    ReadView(const ReadView&);
    ReadView& operator=(const ReadView&);
private:
    // 事务链表中最大的id
    /** The read should not see any transaction with trx id >= this
    value. In other words, this is the "high water mark". */
    trx_id_t    m_low_limit_id;
    // 事务链表中最小的id
    /** The read should see all trx ids which are strictly
    smaller (<) than this value.  In other words, this is the
    low water mark". */
    // 
    trx_id_t    m_up_limit_id;
    /** trx id of creating transaction, set to TRX_ID_MAX for free
    views. */
    trx_id_t    m_creator_trx_id;
    /** Set of RW transactions that was active when this snapshot
    was taken */
    ids_t       m_ids;
    /** The view does not need to see the undo logs for transactions
    whose transaction number is strictly smaller (<) than this value:
    they can be removed in purge if not needed by other views */
    trx_id_t    m_low_limit_no;
    /** AC-NL-RO transaction view that has been "closed". */
    bool        m_closed;
    typedef UT_LIST_NODE_T(ReadView) node_t;
    /** List of read views in trx_sys */
    byte        pad1[64 - sizeof(node_t)];
    node_t      m_view_list;
};

通过该ReadView，新的事务可以根据查询到的每行数据最近的DB_TRX_ID来判断是否对这行数据可见：

• 当检索到的数据的事务ID小于事务链表中的最小值(数据行的DB_TRX_ID < m_up_limit_id)表示这个数据在当前事务开启前就已经被其他事务修改过了,所以是可见的。
• 当检索到的数据的事务ID表示的是当前事务自己修改的数据(数据行的DB_TRX_ID = m_creator_trx_id) 时，数据可见。
• 当检索到的数据的事务ID大于事务链表中的最大值(数据行的DB_TRX_ID >= m_low_limit_id) 表示这个数据在当前事务开启后到下一次查询之间又被其他的事务修改过,那么就是不可见的。
• 如果事务链表为空,那么也是可见的,也就是当前事务开始的时候,没有其他任意一个事务在执行。
• 当检索到的数据的事务ID在事务链表中的最小值和最大值之间，从m_low_limit_id到m_up_limit_id进行遍历，取出DB_ROLL_PTR指针所指向的回滚段的事务ID，把它赋值给trx_id_current，然后从步骤1重新开始判断，这样总能最后找到一个可用的记录。

在READ COMMITTED事务隔离级别下,每次语句执行都关闭ReadView,然后重新创建一份ReadView。
在REPEATABLE READ事务隔离级别下,事务开始后第一个读操作创建ReadView,一直到事务结束关闭。所以每次看到的数据是一样的
Mysql在REPEATABLE READ事务隔离级别下默认使用MVCC，又叫一致性非锁定读，但是会有幻读的问题：A事务开始之后，B事务插入了一行，本来A事务看不到，但是如果A事务执行了update，并且update的筛选条件里又包含了B插入的那一行，那么这一行数据最近修改的事务就会变成A，这样下次A事务再进行前文所述的可见性判断时就会包含这一行数据。又或者A事务开始后查询表发现没有要插入的数据，然后B事务插入了一条同样的数据，等A事务插入数据时会出现Duplicate entry（如果有unique index），这样就需要显示的使用一致性锁定读：

SELECT … FOR UPDATE （排它锁）
SELECT … LOCK IN SHARE MODE （共享锁）

下面先来介绍一下Mysql锁的分类：

• 共享锁（读锁）：允许事务读一行数据。
• 排他锁（写锁）：允许事务删除或更新一行数据。
• 意向共享锁（IS）：事务想要在获得表中某些记录的共享锁，需要在表上先加意向共享锁。
• 意向排他锁（IX）：事务想要在获得表中某些记录的互斥锁，需要在表上先加意向互斥锁。

Innodb实现了行级锁，但是这是基于索引的，如果一条SQL语句用不到索引的话就会使用表锁。有了意向锁，如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需要通过等待意向锁被释放就可以了。意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。有关Latch的介绍参加关于MySQL latch争用深入分析与判断
InnoDB存储引擎有3种行锁的算法，其分别是：

• Record Lock：单个行记录上的锁。
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
• Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

Mysql在REPEATABLE READ事务隔离级别下默认使用Next-Key Lock（在查询的列是唯一索引（包含主键索引）的情况下，Next-key Lock会降级为Record Lock），所以在使用一致性锁定读时，A事务执行select后，会阻止B事务的插入。

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE),UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

上面说的并发事务隔离都是只有一个事务在修改数据，另外一个事务在读取数据，但是如果两个事务都需要修改数据，且是先查询再更新就会出现丢失更新问题：

要么采用悲观加锁的方式：select quantity from items where id = 1 for update阻止事务B的修改
要么采用乐观锁（CAS：compare and swap）：select quantity,version from items where id = 1;a=q-1,v2=v1+1; update items set quantity=a, version=v2 where id = 1 and version = v1;（使用递增version避免ABA问题）
不过最好还是在一条语句中完成原子操作：update items set quantity=quantity-1 where id = 1 and quantity - 1 >= 0;

Consistency

数据库的一致性分为外部一致性和内部一致性，外部一致性由应用开发人员保证，如将A到B账户转账的多个操作放到一个事务中进行，内部一致性是由原子性和隔离性共同保证的。

Durability

Mysql的持久性由Redo Log机制保证，数据在commit之前会持久化到Redo Log中，如果系统crash，可以用它来恢复

谈谈MySQL InnoDB存储引擎事务的ACID特性
谈谈MySQL的锁
详细分析MySQL事务日志(redo log和undo log)
Mysql中的MVCC
Mysql如何保证事务性？
超全面的MySQL语句加锁分析

后面会分析如何利用MyBatis来管理MySQL的连接：
MyBatis连接管理(1)
MyBatis连接管理(2)