MySQL1009

MySQL

AUTOCOMMIT:MySQL默认使用自动提交模式，每个查询操作都会被当做一个事务并自动提交。不需要显式使用START TRANSACTION

MySQL数据写入

InnoDB如何进行数据写入？

tips:

• 一切的逻辑处理和读取写入都操作内存中的数据。这块内存称为Buffer Pool
  步骤：
• 将数据旧值记录到uodo log
• 将需要写入的数据，插入或者更新到Buffer Pool
• 由小线程在特定的时机从内存中把需要更新写入的数据读出来，写入磁盘（InnoDB调用OSd的 open + write函数）。

redu log

• （每次事务提交前）数据进入Buffer Pool后会将“更新写入信息”放入内存中的Redo Log Buffer中。
• “更新写入信息”从Buffer Pool刷盘到磁盘中。
• redo log写入同时，进行binglog刷盘操作。刷盘成功后告知redolog已提交信息，redo log打入commit标记
  tips：
  InnoDB刷盘策略：
  设置方法：
  
  1 ：每次事务提交前，将“更新写入信息”放入内存中的Redo Log Buffer中，与此同时添加到操作系统内存，立刻进行刷盘。
  0 ： 每次事务提交前，将“更新写入信息”放入内存中的Redo Log Buffer中，每隔1秒，进行系统内存放入和刷盘
  2 ： 每次事务提交前，将“更新写入信息”放入内存中的Redo Log Buffer和操作系统内存，等待每秒刷盘。

binlog

并发一致性问题

• 脏读
• 丢失更新
• 不可重复读
• 幻读

封锁类型

• 读写锁
• 意向锁

意向锁

• 更容易地支持多粒度封锁。
  原因：
  在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。
• IX.IS 都是表锁，表示一个事务想要在某个表中的某个数据行上加X或S锁。
  规定:
• 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
• 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。
• 事务 T 想要对表A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了，则事务 T 加 X 锁失败。
• 表级的 IX 锁和行级的 X 锁兼容

封锁协议

三级封锁协议

• 一级封锁协议
  事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。
• 二级封锁协议
  在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。
• 三级封锁协议
• 要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。

两段锁协议:加锁和解锁分为两个阶段进行。

可串行化调度:通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰，不会出现并发一致性问题。

MySQL 的 InnoDB 存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定。

多版本并发控制（MVCC）

用于实现

• 提交读
• 可重复读
• 可串行化隔离级别，需要对所有读取的行都加锁，MVCC无法实现。

MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和 CopyOnWrite 类似。

版本号

• 系统版本号 SYS_ID
• 事务版本号 TRX_ID

MVCC

uodo 日志

MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。
例子：

INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

ReadView

主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, …}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。

当前读和快照读有什么区别？

快照读（一致性非锁定读）就是单纯的 SELECT 语句(不加锁)
当前读 （一致性锁定读）就是给行记录加 X 锁或 S 锁。
当前读例子：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对读的记录加一个S锁
SELECT...FOR SHARE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

范式

• 第一范式 (1NF) ： 属性不可分。
• 第二范式 (2NF) ： 每个非主属性完全函数依赖于键码。
• 第三范式 (3NF) ： 非主属性不传递函数依赖于键码。