「MySQL」MySQL事务处理与并发控制

1. MySQL事务

1. 事务: 数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；事务是一组不可再分割的操作集合(工作逻辑单元)；
2. 事务的特性(ACID)：
   • 原子性（Atomicity，或称不可分割性）：最小的工作单元，整个工作单元要么一起提交成功，要么全部失败回滚
   • 一致性（Consistency）：事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则， 不会因为出现系统意外等原因导致状态的不一致
   • 隔离性（Isolation，又称独立性）：一个事务所操作的数据在提交之前，对其他事务的可见性设定（一般设定为不可见）
   • 持久性（Durability）：事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失
3. 事务的开启与提交模式
   • 若参数autocommit=0，自动开启手动提交
   • 若参数autocommit=1（系统默认值），又分为两种状态：
     • 自动开启自动提交：用户的每一个操作都是一个完整的事务周期。
     • 手动开启手动提交：从用户执行start transaction命令到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。
       • begin 或者 start transaction – 开启事务
       • commit 或者 rollback – 事务提交或回滚

1.1 事务的隔离级别

• 查看/设置隔离级别
  • 查看：SELECT @@tx_isolation
  • 设置：set tx_isolation='xxx'

1. 读未提交（Read Uncommitted）
   • 事务未提交对其他事务也是可见的，脏读（dirty read）
2. 读提交（Read Committed）–解决脏读问题
   • 一个事务开始之后，只能看到自己提交的事务所做的修改，不可重复读（nonrepeatable read）
3. 可重复读（Repeatable Read）–解决不可重复读问题
   • 在同一个事务中多次读取同样的数据结果是一样的，这种隔离级别未定义解决幻读的问题
4. 串行化（Serializable）–解决所有问题
   • 最高的隔离级别，通过强制事务的串行执行，但是会导致大量超时以及锁争用问题

Mysql默认采用REPEATABLE_READ隔离级别，Oracle默认采用READ_COMMITTED隔离级别。
事务的隔离级别的实现：锁、MVCC（多版本并发控制 Multiversion Currency Control）。

1.2 事务的七大传播行为

Spring在TransactionDefinition接口中规定了7种类型的事务传播行为。事务传播行为是Spring框架独有的事务增强特性，他不属于的事务实际提供方数据库行为。

• 事务传播行为用来描述由某一个事务传播行为修饰的方法被嵌套进另一个方法的时事务如何传播。
  • @Transactional(propagation = Propagation.REQUIRED)

1. 第一类：运行在同一个事务
   • **REQUIRED**（required）：默认，支持当前事务，如果当前没有事务，就新建一个事务。
   • SUPPORTS（supports）：支持当前事务，如果当前没有事务，就不使用事务(以非事务方式执行)
   • MANDATORY（mandatory）：支持当前事务，如果当前没有事务，就抛出异常
2. 第二类：运行在不同事务
   • **REQUIRES_NEW**（requires new）：新建事务，如果当前存在事务，把当前事务挂起
   • NOT_SUPPORTED(not supported)：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起
   • NEVER（never）：以非事务方式执行，如果当前存在事务，则抛出异常
3. 第三类：嵌套执行–即外层事务如果失败，内层事务要么回滚到保存点要么回滚到初始状态
   • **NESTED**（nested）：如果当前事务存在，则嵌套事务执行

2. 锁

锁是用于管理不同事务对共享资源的并发访问，InnoDB存储引擎支持行锁和表锁（InnoDB表锁是另类的行锁）

• InnoDB行锁
  • 共享锁（读锁）：Shared Locks
  • 排它锁（写锁）：Exclusive Locks
• InnoDB表锁
  • 意向锁共享锁（IS）：Intention Shared Locks
  • 意向锁排它锁（IX）：Intention Exclusive Locks
  • 自增锁：AUTO-INC Locks
• 行锁的算法
  • 记录锁 Record Locks
  • 间隙锁 Gap Locks
  • 临键锁 Next-key Locks

2.1 共享锁(Shared) & 排他锁(Exclusive)

它们都是标准的行级锁。

• 共享锁（S锁）：读锁，读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰，但是只能读不能修改;
  • 加锁： select * from users WHERE id=1 LOCK IN SHARE MODE;
  • 解锁：**commit或rollback**
• 排他锁（X锁）：写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。
  • 加锁： select * from users WHERE id=1 FOR UPDATE;
  • delete/update/insert 默认上 X 锁
  • 解锁：**commit或rollback**

注意：所谓共享锁、排他锁其实均是锁机制本身的策略，通过这两种策略对锁做了区分。

• InnoDB的行锁是通过给索引上的索引项加锁来实现的。
• 只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB 将使用表锁（锁住索引的所有记录）

2.2 意向锁(Intention) & 自增锁(AUTO-INC)

它们都是标准的表级锁。

• 意向锁（Intention Locks）：表级别的锁。先提前声明一个意向，并获取表级别的意向锁（IS或IX），如果获取成功，才被允许对该表加行锁(S或X)。(即一个数据行加锁前必须先取得该表的意向锁)
  • 意向锁(IS、IX)是InnoDB数据操作之前自动加的，不需要用户干预
  • 意义：当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁
• 自增锁（AUTO-INC Locks）：针对自增列自增长的一个特殊的表级别锁
  • show variables like 'innodb_autoinc_lock_mode';
  • 默认取值1，代表连续，事务未提交ID永久丢失

2.3 记录锁(Record) & 间隙锁(Gap) & 临键锁(Next-key)

• 临键锁 Next-key locks：
  • 锁住记录+区间（左开右闭）
  • Innodb默认行锁算法
  • 当sql执行按照索引进行数据的检索时,查询条件为范围查找（between and、<、>等）并有数 据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间（左开右闭）
• 间隙锁 Gap locks：
  • 锁住数据不存在的区间（左开右开）
  • 当记录不存在，临键锁退化为Gap锁
  • 当sql执行按照索引进行数据的检索时，查询条件的数据不存在，这时SQL语句加上的锁即为 Gap locks，Gap只在RR事务隔离级别存在，锁住索引不存在的区间（左开右开）
• 记录锁 Record locks：
  • 锁住具体的索引项
  • 唯一性(主键/唯一)索引，条件为精准匹配，退化成Record锁
  • 当sql执行按照唯一性（Primary key、Unique key）索引进行数据的检索时，查询条件等值匹 配且查询的数据是存在，这时SQL语句加上的锁即为记录锁Record locks，锁住具体的索引项

2.4 死锁的产生与避免

• 死锁
  • 在InnoDB中，锁是逐步获得的，就造成了死锁的可能（2个或以上并发事务）
  • 每个事务都持有锁（或者是已经在等待锁）;
  • 每个事务都需要再继续持有锁；事务之间产生加锁的循环等待，形成死锁。
• 死锁的产生与避免
  1. 类似的业务逻辑以固定的顺序访问表和行。
  2. 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
  3. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概 率。
  4. 降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择
  5. 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添 加上锁（或者说是表锁）

3. MVCC(多版本并发控制)

Multiversion concurrency control (多版本并发控制)：

• MVCC 就是 同一份数据临时保留多版本的一种方式，进而实现并发控制
• 是行级锁的变种，它在普通读情况下避免了加锁操作，因此开销更低。
• MVCC 提供了时点（point in time）一致性视图。MVCC 并发控制下的读事务一般使用时间戳或者事务ID去标记当前读的数据库的状态（版本），读取这个版本的数据。读、写事务相互隔离，不需要加锁。读写并存的时候，写操作会根据目前数据库的状态，创建一个新版本，并发的读则依旧访问旧版本的数据。

3.1 MVCC逻辑流程

• 在MySQL中建表时，每个表都会有三列隐藏记录，其中和MVCC有关系的有两列
  • DB_TRX_ID：数据行的版本号
  • DB_ROLL_PT：删除版本号

1. MVCC逻辑流程-插入
   • 在插入数据的时候，会把全局事务ID记录到列DB_TRX_ID中去
2. MVCC逻辑流程-删除
   • 执行完删除SQL之后数据并没有被真正删除，而是对删除版本号(DB_ROLL_PT)做改变
3. MVCC逻辑流程-修改
   • 修改数据的时候 会先复制一条当前记录行数据，同时标记这条数据的数据行版本号为当前事务ID，最后把旧数据的删除版本号标记为新数据行版本号的值(即当前事务ID)。
4. MVCC逻辑流程-查询
   • 查找数据行版本号早于当前事务ID的数据行记录
   • 也就是说，数据行的版本号要小于或等于 当前事务ID，这样也就确保了读取到的数据是当前事务开始前已经存在的数据，或者是自身事务改变过的数据
   • 查找删除版本号要么为NULL，要么大于当前事务版本号的记录
   • 这样确保查询出来的数据行记录在事务开启之前没有被删除

MySQL解决不可重复读和脏读并不是单纯利用 MVCC 机制来实现的。

4. MySQL事务日志(Undo Log和Redo Log)

innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。
undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：

1. redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
2. undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

s

4.1 Undo Log

• Undo Log定义：
  • undo意为取消，以撤销操作为目的，返回指定某个状态的操作
  • undo log指事务开始之前，在操作任何数据之前,首先将需操作的数据备份到一个地方 (Undo Log)
• UndoLog是为了实现事务的原子性而出现的产物
• Undo Log实现事务原子性：
  • 事务处理过程中如果出现了错误或者用户执行了 ROLLBACK语句,Mysql可以利用Undo Log中的备份 将数据恢复到事务开始之前的状态
• UndoLog在Mysql innodb存储引擎中用来实现多版本并发控制
• Undo log实现多版本并发控制：
  • 事务未提交之前，Undo保存了未提交之前的版本数据，Undo中的数据可作为数据旧版本快照供 其他并发事务进行快照读

4.2 当前读 & 快照读

• 快照读：
  • SQL读取的数据是快照版本，也就是历史版本，普通的SELECT就是快照读 innodb快照读，数据的读取将由 cache(原本数据) + undo(事务修改过的数据) 两部分组成
• 当前读：
  • SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改 UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE都是当前读

4.3 Redo Log

• Undo Log定义：
  • Redo，顾名思义就是重做。以恢复操作为目的，重现操作；
  • Redo log指事务中操作的任何数据,将最新的数据备份到一个地方 (Redo Log)
• Redo log的持久：
  • 不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo 中。具体 的落盘策略可以进行配置
• RedoLog是为了实现事务的持久性而出现的产物
• Redo Log实现事务持久性：
  • 防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。
• 一旦事务成功提交且数据持久化落盘之后，此时Redo log中的对应事务数据记录就失去了意义，所 以Redo log的写入是日志文件循环写入的

附: 58同城数据库设计30条军规

• 军规适用场景：并发量大、数据量大的互联网业务
• 解读：讲解原因，解读比军规更重要

一、基础规范

1. 必须使用InnoDB存储引擎
   • 解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高
2. 必须使用UTF8字符集 UTF-8MB4
   • 解读：万国码，无需转码，无乱码风险，节省空间
3. 数据表、数据字段必须加入中文注释
   • 解读：N年后谁tm知道这个r1,r2,r3字段是干嘛的
4. 禁止使用存储过程、视图、触发器、Event
   • 解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务 层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的 扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧
5. 禁止存储大文件或者大照片
   • 解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI 多好

二、命名规范

6. 只允许使用内网域名，而不是ip连接数据库
7. 线上环境、开发环境、测试环境数据库内网域名遵循命名规范
   • 业务名称：xxx，线上环境：xxx.db，开发环境：xxx.rdb，测试环境：xxx.tdb
   • 从库在名称后加-s标识，备库在名称后加-ss标识 线上从库：xxx-s.db 线上备库：xxx-sss.db
8. 库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止 拼音英文混用
9. 表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx

三、表设计规范

10. 单实例表数目必须小于500
11. 单表列数目必须小于30
12. 表必须有主键，例如自增主键
    • 解读：
      1. 主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和 内存的使用
      2. 主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类 型可以有效的减少索引的磁盘空间，提高索引的缓存效率
      3. 无主键的表删除，在row模式的主从架构，会导致备库夯住
13. 禁止使用外键，如果有外键完整性约束，需要应用程序控制
    • 解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响 sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景 数据库使用以性能优先

四、字段设计规范

14. 必须把字段定义为NOT NULL并且提供默认值
    • 解读：
      1. null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
      2. null这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条 件下，表中有较多空字段的时候，数据库的处理性能会降低很多
      3. null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标 识
      4. 对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、 not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记 录，查询结果就不会包含name为null值的记录
15. 禁止使用TEXT、BLOB类型
    • 解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内 存命中率急剧降低，影响数据库性能
16. 禁止使用小数存储货币
    • 解读：使用整数吧，小数容易导致钱对不上
17. 必须使用varchar(20)存储手机号
    • 解读：
      1. 涉及到区号或者国家代号，可能出现+-()
      2. 手机号会去做数学运算么？
      3. varchar可以支持模糊查询，例如：like“138%”
18. 禁止使用ENUM，可使用TINYINT代替
    • 解读：
      1. 增加新的ENUM值要做DDL操作
      2. ENUM的内部实际存储就是整数，你以为自己定义的是字符串？

五、索引设计规范

19. 单表索引建议控制在5个以内
20. 单索引字段数不允许超过5个
    • 解读：字段超过5个时，实际已经起不到有效过滤数据的作用了
21. 禁止在更新十分频繁、区分度不高的属性上建立索引
    • 解读：
      1. 更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
      2. “性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性 能与全表扫描类似
22. 建立组合索引，必须把区分度高的字段放在前面
    • 解读：能够更加有效的过滤数据

六、SQL使用规范

23. 禁止使用SELECT *，只获取必要的字段，需要显示说明列属性
    • 解读：
      1. 读取不需要的列会增加CPU、IO、NET消耗
      2. 不能有效的利用覆盖索引
24. 禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性
    • 解读：容易在增加或者删除字段后出现程序BUG
25. 禁止使用属性隐式转换
    • 解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不 能命中phone索引
26. 禁止在WHERE条件的属性上使用函数或者表达式
    • 解读：SELECT uid FROM t_user WHERE from_unixtime(day)>=’2017-02-15’ 会导致全 表扫描 正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp(‘2017-02-15 00:00:00’)
27. 禁止负向查询，以及%开头的模糊查询
    • 解读：
      1. 负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
      2. %开头的模糊查询，会导致全表扫描
28. 禁止大表使用JOIN查询，禁止大表使用子查询
    • 解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能
29. 禁止使用OR条件，必须改为IN查询
    • 解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费 更多的CPU帮助实施查询优化呢？
30. 应用程序必须捕获SQL异常，并有相应处理