MySQL 数据存储和优化------MySQL事务和锁 ---- （架构---索引---事务---锁---集群---性能---分库分表---实战---运维）持续更新

本文只是整个系列笔记的第三章：MySQL事务和锁，只解释事务和锁相关知识。

1. ACID特性

1. 关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足4个特性（ACID）：原子性（Atomicity）、一致性（Consistency）、隔离性（lsolation）、持久性（Durability）
2. ACID关系图如下，4个特性有3个和WAL（预写式机制，就是先写日志，再写磁盘）有关，需要Redo、Undo日志保证。
   

1. 事务是一个原子操作单元，对其数据的修改，要么都成功，要么都失败。
2. 每一个写事务，都会先修改BufferPool，从而产生对应Redo/Undo日志，Buffer Pool中，页被刷盘之前，这些日志信息都会写入到日志文件中，如果Buffer Pool中脏页没有刷成功，数据库挂了，数据库重新启动之后，可以通过Redo日志恢复保证原子性，保证脏页写的数据不丢失。如果脏页刷新成功，此时数据库挂了，就需要Undo来实现原子性。
3. 保证被正确的、完整修改的脏页刷新到磁盘，就是原子性，通过Redo实现。如果中间某个操作失败，那么所有数据都会通过Undo日志回滚到事务开始之前的状态。

1. 指一个事务一旦提交，对数据库中数据的修改是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失。
2. 一个“提交”动作，会触发多个操作：binlog落地、发送binlog、存储引擎提交、flush_logs、check_point（刷盘脏页等操作）、事务提交标记等等。这些都是数据库保证其数据完整性、持久性的手段。
   
3. MySQL的持久性也与WAL技术相关，redo log在系统发生Crash（死机）重启之类的情况时，可以修复数据，从而保障事务的持久性，通过原子性可以保证逻辑上的持久性，通过存储引擎的数据刷盘可以保证物理上的持久性。

1. 指事务之间相互独立，互不影响，一个事务内部的操作及使用的数据对其它的并发事务是隔离的。
2. InnoDB支持4种隔离性（隔离级别，后面会详细讲），从低到高依次是：读未提交、读已提交、可重复读、可串行化。另外，锁和多版本控制（MVCC）技术就是用于保障隔离性的（后面会详细介绍）

1. 指事务从一个正确的状态到另一个正确的状态。就是事务开始之前，是一个正确的状态，事务结束之后，是另一个正确的状态。比如银行转账的例子，A和B共有1000块，A给B转账后，A和B依然共有1000块。
2. 一致性包括两方面：约束一致性、数据一致性

1. 约束一致性：创建表结构时所指定的外键、Check、唯一索引等约束，可惜在MySQL中不支持Check。
2. 数据一致性：综合性的规定，由原子性、持久性、隔离性共同保证的结果，不单单依赖于某一种技术

3. 这个概念比较抽象，其实就是通过原子性、隔离性、持久性这3个特性来保证数据完整性，这3个特性又通过Redo/Undo保证。还有逻辑上的一致性，包括唯一索引、外键约束、check约束，这些都属于业务逻辑的范畴，需要写sql的人来自己保证。因为MySQL顶多提示您，“这么做不对，修改的数据涉及外键”等等。
   

2. 并发

2.1 事务控制的发展

1. 并发事务带来的问题：更新丢失（多个线程同时修改同一行数据，自己提交的可能被别人提交覆盖，或回滚覆盖），脏读（另一个事务修改了但没提交的数据被读到），不可重复读（读到其它事务修改，但是已提交的数据，update，前后内容不一致），幻读（读到其它事务新插入的数据，insert，前后数据条目不一致）
2. 排队：最简单的解决思路，完全串行化（按顺序，序列化）执行所有数据库操作，不需要加锁，也就是全局排队。串行化进行所有事务单元，数据库某一个时刻只处理一个事务。特点是强一致性，但是处理能力就低了，完全是单线程的了。
   
3. 排它锁（互斥锁）：既需要安全，又不能接受排队串行化的处理能力不高，那么引入锁后，就支持并发处理事务的能力了。事务之间涉及到相同数据项时，会使用排它锁，先进入的事务将独占这个锁处理数据，其它事务只能被阻塞，等待前面事务执行完毕后释放锁。（下图例子中，当事务1先抢到锁后，事务2就需要阻塞，当事务1整体结束（读、写、读），锁释放后，事务2拿到锁，才能开始事务（读、写））
   
4. 读写锁：上面排它锁，事务1拿到锁后，无论是读还是写，都独占这条数据，其它事务必须等待它执行完所有操作，这样对效率还是有影响的。从而引入读写锁，进一步细化锁的颗粒度，区分读写操作（读读，全是读、读写，有读有写、写写，全是写），让读读之间不加锁，这样两个读事务就可以同时被执行。

1. 下图例子中，事务1先抢到锁，执行读操作，此时事务2也是读操作，那么事务2不阻塞，一起执行。然后事务1执行写操作，等待事务2执行完读，事务1将执行写，事务2将被阻塞。
   
2. 也就是说，只能实现读和读的并行（同一时间点，干不同或相同的事），不能实现读写、读读的并行。

5. MVCC（多版本控制）：上面的方案，仅仅实现读和读的并行，依然是不满足要求的，所以引入了MVCC，我们接下来会详细讲它。

2.2 MVCC（多版本控制）

MVCC：依然是Copy on Write的读写思想，但是MVCC支持读读、读写（写读）的并行。为了保证一致性，写和写的并行是不可以的。也就是说，读和写操作，不会受到影响，可以并行。但两个写操作并行，会无法保证一致性。

1. 事务1开始写操作时，会先copy一个写操作之前的记录副本，其它事务读取时，会读取这个副本。
2. 也就是用一点空间，换取其它事务不会因为此记录正在被写，而无法读。实现的读写的并行。

如果想要进一步解决写和写的冲突，可以使用乐观锁或悲观锁，后面会讲。

MVCC（Multi Version Concurrency Control，多版本控制），指在数据库中为实现高并发的数据访问，对数据进行多版本处理，通过事务的可见性来保障事务能看到自己应该看到的数据版本。很巧妙地将稀缺资源的独占互斥转换为并发，大大提高数据库的吞吐量以及读写性能。

1. 每次事务修改操作前，都会在Undo日志记录修改之前的数据状态和事务号，该备份记录可以用于其它事务读取，也可以进行必要时的数据回滚。

MVCC实现，读不加锁，读写不冲突。读多写少的系统应用中，读写不冲突非常重要，极大提升系统并发性能。所以现阶段几乎所有关系型数据库都支持MVCC。不过目前MVCC只在Read Commited（读已提交）和Repeatable Read（可重复读）两种隔离级别下工作

1. MVCC中，读操作分为快照读（Snapshot Read）和当前读（Current Read）

1. 快照读：读取的是记录的快照版本（可能是历史版本），不用加锁。也就是不加事务的读操作（select）。并且快照读因为不加事务不加锁，在不同隔离级别下，读到的东西也不一样。

1. RC(Read Commited)隔离级别：每次读的时候，都会创建一个read build视图，所以如果这时候有其它写事务提交，读到的内容会不一致，就会发生读已提交。
2. RR(Repeatable Read)隔离级别：第一次读的时候，创建read build快照视图，后续不会再创建新的快照视图，就返回第一次读的。实现可重复读。

2. 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发修改这条记录。就是加事务的读操作，读的时候加锁（select … for update 或 select … lock in share mode,其实insert/update/delete操作也会先进行当前读，然后对读取的记录修改。）

下图中有一行InnoDB的记录，我们讲一下它的更新过程，会涉及很多小细节，建议背下来。F1 ~ F6是表中字段名，1 ~ 6是其对应数据，后面3个字段是存储引擎自动建立，分别是隐含ID（自动生成的Row_id，一般表没有主键，没有唯一键，就会自动生成一个6位Row_id作为主键，以生成聚簇索引），事务号（每处理一个事务，会自动加一）和回滚指针（就是指向需要回滚时，回滚的版本）

1. 假设一条数据刚刚插入，DB_ROW_ID为1，其它两个字段为空。
   
2. 此时事务1要更改这行数据，首先会在Undo log中进行备份，具体步骤如下：

1. 排它锁锁定该行，记录Redo log
2. 把该行修改前的值复制到Undo log,此时其它读线程可以从Undo log读取快照了。
3. 事务1，修改当前行的值，填写事务号，使回滚指针指向Undo log刚刚记录的修改前的行
   

3. 此时事务2也来操作，要执行和事务1一样的操作，那么它会先等事务1释放锁，然后剩下的和上面步骤就一样了，但是，此时Undo log中会有两条记录，并通过指针连在一起，可以通过当前记录的回滚指针回溯到该行创建时的初始内容。
   
4. 当然，事务提交后，并刷新脏页到磁盘，Undo log工作基本完成，这些记录会被工作线程给回收，具体机制，请参考第一章，MySQL架构原理。

3. 事务隔离级别

用于解决更新丢失、脏读、不可重复读、幻读的方案

1. 更新丢失：当前事务的更新操作，可能被覆盖，是一定要避免的行为。

1. 回滚覆盖，你完成了修改，其它事务与你同时修改，但是它操作失败回滚了，覆盖了你的修改。
2. 提交覆盖，你完成了修改，其它事务与你同时修改，它们的修改覆盖你的修改。

2. 脏读：读到脏数据，就是其它事务改了，但是还没有提交事务的数据，是必须避免的行为。
3. 不可重复读：读到其它事务修改的数据（正常提交事务的），第一次读和第二次读，前后内容不一致，不是重复的。就是第一次select和第二次之间，有其它线程update，导致第二次和第一次select出的结果不一样。没有特定要求，这种行为勉强可以接收
4. 幻读：读到其它事务插入的新数据（正常提交事务的），前后数据条目不一致，一般不用避免这种情况发生。

事务隔离级别，是针对支持事务的存储引擎，比如InnoDB，而MyISAM这种事务都不支持，自然没有事务隔离级别。另外，如果你的SQL没有开启事务（begin…commit，或者for update加排它锁，lock in share mode加共享锁等等），也不会进行隔离。

1. 读未提交：解决回滚覆盖类型的更新丢失，但是可能发生脏读，几乎所有系统都是不可接受的。
2. 读已提交：解决脏读，但是你的更新依然有可能被其他事务的提交覆盖掉。几乎是可以接受的。
3. 可重复读：解决更新丢失，脏读，不可重复读的问题，但是可能会发生幻读(因为MySQL引入范围锁，很大程度解决了幻读问题，也就是别人insert的数据，你不会读到。但是如果你修改了别人新提交的数据，你还是会发生幻读)，大部分系统可以接受。
4. 串行化：就是排序，强制事务排序，串行执行，解决相互冲突，但是可能导致大量超时和锁竞争现象，效率低下。

3.1 隔离级别和锁

事务隔离级别是SQL92定制的标准，是事务并发控制的整体解决方案。本质是底层锁和MVCC的封装，隐藏底层细节

1. 锁是数据库实现并发控制的基础，事务隔离性采用锁实现，对不同操作加不同锁，防止事务之间的冲突
2. 用户使用时，之间使用事务隔离级别即可，当选用的隔离级别无法解决并发问题或需求时，才有必要在开发中手动设置锁。

3.2 隔离级别控制

查看默认事务隔离级别

show variables like 'tx_isolation';
select @@tx_isolation;

设置事务隔离级别

# 不加global只针对当前窗口有效，global是全局生效，但是当前窗口无效，需要再执行一次不加global的。
set [global] tx_isolation='READ-UNCOMMITTED'
set tx_isolation='READ-COMMITTED'
set tx_isolation='REPEATABLE-READ'
set tx_isolation='SERIALIZABLE'

4. 锁机制

InnoDB引擎中，select语句默认不加锁，查询时不受锁的影响，因为MVCC机制，可以直接读取Undo Log的副本。如果需要加锁可以通过关键字for update追加排它锁，通过lock in share mode追加共享锁。而update/delete/insert操作会自动加for update

4.1 锁分类

从操作粒度可分为表级锁、行级锁、页级锁

1. 表级锁：每次操作，会锁住整张表。锁粒度较大，发生锁冲突概率较高，并发度是三种锁最低的，MyISAM、BDB、InnoDB都支持。
2. 行级锁：每次操作，锁住要操作的一行数据，锁粒度最小，发生锁冲突概率最低，并发度最高，InnoDB引擎支持。
3. 页级锁：每次操作，锁住相邻的一组记录，锁定粒度、开销和加锁时间，都界于表锁和行锁之间，并发度一般，在DBD存储引擎中应用。

从锁的操作类型，分为读锁和写锁。

1. 读锁（S锁，共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。多个共享锁可以共享这条记录，当事务1给记录添加S锁后，只能读不能写，其它事务可以对该记录追加S锁，但是不能追加X锁，只能等S锁全部释放。
2. 写锁（X锁，排它锁）：当前操作没有完成之前，会阻断其它写锁和读锁。一个记录同时只能有一个排它锁使用，共享锁也不可追加。事务1对记录加排它锁后，可以进行读写操作，其它事务不可以追加任何锁，只能等事务1的排它锁释放。
3. 意向读锁(IS锁)/意向写锁（IX锁）：IS、IX和S、X锁的区别是，S、X是针对行级的行级锁，IS、IX是针对表级的。
4. 在对表记录添加S或X锁之前，会先给整张表添加IS或IX锁，避免其它事务也对这行记录同时加行锁，加完S或X锁后，会释放IS或IX锁。

从操作的性能可分为乐观锁和悲观锁

1. 乐观锁：乐观的认为我操作过程中，不会发生冲突。简单的说就是不加锁，用其它方法保证数据一致性。一般实现方式为：对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果冲突了，则提示错误信息。
2. 悲观锁：悲观的认为我操作过程中，一定发生冲突，所以修改数据之前，为了避免同时被其它人修改，会先锁定，然后再修改。共享锁和排它锁都属于悲观锁的具体实现。

4.2 行锁原理

请一定要注意行锁的使用，并发场景下，如果要使用行锁，一定要保证索引的存在。比如where name = ‘java’。如果name没有索引，那么只要加锁，就会锁定整张表。并发量高的话，很可能就会将数据库服务器给爆破了。

另外，经过测试，没有命中索引会锁表，但是如果记录不存在，不会锁表。

InnoDB引擎中，我们可以使用行锁和表锁，行锁又分共享锁和排它锁，InnoDB行锁是通过对索引数据页上的记录加锁来实现，主要实现算法有3种：Record Lock、Gap Lock、Next-key Lock。这3种不是具体的锁种类，而是实现算法，比如我们说Record Lock锁，它的意思是，一个锁住指定一行记录的共享锁或排它锁

1. RecordLock（记录锁）：锁定单个行记录的锁。RC（Read Committed）和RR(Repeatable Read)隔离级别都支持
2. GapLock（间隙锁、范围锁）：锁定索引记录间隙，确保索引记录的间隙不变。RR隔离级别支持。假设有(1,3,5)三条数据，我们对3加间隙锁，它不但会锁住3，还会锁住(1,3)之间的间隙，和(3,5)之间的间隙，不可以破坏这个间隙，比如从(1,3)中间插入一个（2），是不允许的。
3. Next-key Lock（记录锁+范围锁）：行锁和间隙锁的组合，同时锁住数据和数据前后范围。RR隔离级别支持。

在RR(Repeatable Read)隔离级别下，支持上述三种算法。InnoDB对于记录加锁行为都先采用Next-Key Lock，但是当SQL操作含有唯一索引时，会对Next-key Lock优化，降级为RecordLock锁，仅锁住索引本身而非范围。具体细节，看下面的例子

1. select…from ：普通select，InnoDB不加锁。因为采用了MVCC机制实现非阻塞。
2. select…from lock in share mode：追加共享锁的select，InnoDB会使用Next-Key Lock共享锁进行处理，如果扫描发现是唯一索引，可以降级为RecordLock锁
3. select…from for update：追加排它锁的select，可以理解为开启了事务的select，InnoDB会使用Next-Key Lock排它锁进行处理，如果发现是唯一索引，可以降级为RecordLock锁
4. update/delete…where：直接用Next-Key Lock排它锁进行处理，索引唯一，可以降级为RecordLock排它锁
5. insert : 直接对要插入的那一行，设置RecordLock排它锁。

下面以“update t1 set name = xxx where id = 10”操作为例，分析InnoDB对不同索引加锁行为，以RR(Repeatable Read)隔离级别为例。

1. 主键加锁：假设id是主键

首先，update操作，是加Next-Key Lock排它锁，此时发现id是主键，是唯一的，可以降级为RecordLock排它锁，最终，这个RecordLockX（排它）锁只锁住了id=10的这一行记录。

2. 唯一键加锁：假设id是唯一键

首先，底层索引树会有两个，一个是唯一键的，还有就是主键索引树（聚簇索引）。那么同样update，要加Next-Key Lock排它锁，此时发现是唯一键，可以降级为RecordLock排它锁。最终会将两颗索引树id = 10的记录锁住（RecordLock排它锁,先唯一键索引树，然后主键索引树）。

3. 非唯一键加锁：假设id是非唯一的键

首先同样的，两颗索引树，update操作加Next_Key Lock排它锁，找到两个id = 10的记录，会将记录本身和间隙锁住，此时(6,10)、(10,10)、(10,11)之间的间隙关系不可改变，锁完非唯一键索引，再锁聚簇索引的行记录，发现是唯一的，给聚簇索引的锁会降级为RecordLock排它锁。最终会锁住非唯一键索引的记录和范围（Next_Key Lock排它锁），以及聚簇索引的记录（RecordLock排它锁）

4. 无索引加锁：假设id字段没有索引

没有索引，update操作，加Next-Key Lock 排它锁，会对整个表的行记录和间隙加锁，相当于直接加了表锁（整张表加锁，其它任何事务操作都会阻塞，只有普通select走MVCC搞出来的副本，不会阻塞）。因为我们前面说过，InnoDB锁机制的记录锁定实现，是基于索引的。

4.3 悲观锁（Pessimistic Locking）

指在数据处理过程中，将数据处于锁定状态，一般使用数据库锁机制来实现。广义上，前面的行锁、表锁、读锁、写锁、共享锁、排它锁等都属于悲观锁范畴。

每次操作都会锁住整张表，并发度最低，常用命令如下：

1. 手动增加表锁

1. read：当前连接为整张表加读锁，整张表将只能执行读操作，当前连接和其它连接都可以执行读操作。对于写操作的情况：当前线程（连接）虽然可以执行update等DML命令进行写操作，但是会直接报错，提示当前表有读表锁，不可修改。其它线程（事务）的写操作会直接阻塞，等待表锁释放。
2. write: 当前连接为整张表加写锁，整张表排它，只有当前事务（连接）可以执行操作。其它事务所有操作都将阻塞。

2. 通俗来讲，为表加读锁，会阻止所有写操作。为表加写锁，会阻塞除当前连接外，所有其它连接的操作（无论读还是写）。当前连接不受影响，可以执行任何操作。

lock table 
	tablename1 # 表级锁分read读锁和write写锁
read|write[, # 可以同时给多个表加表级锁
	tablename2
read|write,
	...
read|write];

2. 查看表上的锁

show open tables;

3. 删除表锁

unlock tables;

和表锁一样，也分读锁和写锁，区别是行级锁只锁一行记录，处理并发能力强。行级锁的实现依靠其对应的索引，如果操作没用到索引，会锁住全表的记录。

1. 共享锁（读锁，S锁）：只能适用于查询语句，通过select语句后面跟关键字lock in share mode来使用共享锁。多个事务可以对加共享锁的数据追加共享锁，都可以执行读操作，但是都不能修改。
2. 排它锁（写锁，X锁）：SQL语句后面跟for update关键字，就可以给操作的对象加排它锁（InnoDB引擎默认给update，delete和insert加上了for update，但是select如果需要排它锁，必须后面手动跟上for update关键字）。加了排它锁后，就不可以再追加任何锁（不能与其它锁共存，而且排它锁就一个），一个事务获取了一个数据行的排它锁，其它事务就不能对该行记录做其它操作，也不能获取该行的锁。直到这个锁被释放。

请一定要注意行锁的使用，并发场景下，如果要使用行锁，一定要保证索引的存在。比如where name = ‘java’。如果name没有索引，那么只要加锁，就会锁定整张表。并发量高的话，很可能就会将数据库服务器给爆破了。

4.4 乐观锁（CAS）

不是数据库提供的功能，需要开发者自己实现。乐观的认为这次操作，不会冲突，因此数据库操作时，不做任何处理，即不进行加锁，而是在进行事务提交时再去判断是否有冲突。

因为不涉及锁，所以处理并发的能力非常高，在高并发场景，乐观锁的使用比悲观锁要广泛。

实现原理

1. 使用版本字段（version）实现：给数据表增加一个版本version字段，每操作一次，版本号进行加1。提交事务前先记录版本号，如果操作过程中，其它事务进行操作，导致版本号发生改变，当前事务提交时，就会发现版本号不一致，从而进行相应的处理。
   

会发生ABA问题，比如一个线程读到了A这个版本号，另一个线程改为B，然后又改为A，那么对于第一个线程，版本号没有问题，提交后，就会出问题。所以使用版本号这种方案，必须配合一些原子的操作，比如只能每次+1，保证+1过程是线程安全，不让它们自由修改版本号。

2. 时间戳（Timestamp）实现：和version类似，同样给表加一个字段，类型使用timestamp时间戳，提交前先检查数据库中时间戳，提交时，再获取一次时间戳和自己提交前记录的比较，不一致就说明有冲突。

4.5 死锁

死锁的原因永远都是，哥俩互相想要对方资源，但是谁都不想先放手，结果一直僵持在那，死在那。

MySQL中，发生死锁不会就一直在那里阻塞，而是会直接报错Deadlock

1.表锁死锁

1. 当用户A访问表A（锁住表A），然后又访问表B，另一个用户B访问表B（锁住表B），然后想访问表A。此时A用户想要访问B表，但是B用户对B表加了锁，正在访问表A，结果因为表A被用户A加了锁，访问不了，无法释放表B的锁。A用户也因为想要访问表B而无法释放表A的锁。哥俩只能死在那。产生了死锁。
2. 解决方法很简单，让哥俩按顺序执行，比如永远先A后B。必须同时锁定两个资源时，要保证任何时候都按照相同的顺序来锁定资源，也就是保证A访问B表时，B没有锁定B表。

2.行锁死锁

1. 如果事务中执行没有索引条件的查询，引发全表扫描，将行级锁上升为全表锁住（等价于表锁），此时因为程序中可能无法预估所有情况，很容易发生死锁和阻塞。导致系统崩溃。
2. 解决方案，就是不要让它把整个表锁住，还能怎么办？不要使用加锁的太复杂的多表联查，用explain命令对sql执行计划进行查看，看看是不是全用了索引。
3. 另一种情况就是上面表锁死锁的情况，锁住第一行记录，想操作第二行，第二个用户锁住第二行记录，想操作第一行，就死了。
   
4. 解决方案，也是改代码，改业务逻辑，这种死锁都是逻辑问题产生的。

3. 共享锁转换为排它锁

1. A查询一条记录，然后更新该条记录，B也更新该条记录，此时B的排它锁由于A有共享锁，需要等A释放，只能排队等着。A执行更新操作时，就会发生死锁，因为A需要这个排它锁来更新，但是请求不到这个锁，因为B有一个排它锁请求，正在等待A释放共享锁。
2. 解决方案：避免同时对同一条记录的多次操作，比如登录页面，登录按钮等控件，点击完立刻让它失效，不要让用户重复点击。或者使用乐观锁避免长事务中数据库加锁开销。不过乐观锁不够安全，可能有外部系统的用户更新操作不受我们系统的乐观锁控制，造成脏数据被更新的数据库。

1. 死锁日志

1. 通过命令show engine innodb status \G;显示InnoDB的状态，从LATEST DETECTED DEADLOCK选项中可以看到死锁相关的日志信息
   
   2. 查看锁状态的变量
2. 通过命令show status like 'innodb_row_lock%'命令查看相关命令，分析系统中行锁的争夺情况。如果等待次数很多，每次等待时间也长，那么可能就需要优化了。
   

1. Innodb_row_lock_current_waits：当前正在等待锁定的数量
2. Innodb_row_lock_time：从系统启动到现在锁定的总时长
3. Innodb_row_lock_time_avg：每次等待所花费的平均时间
4. Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
5. Innodb_row_lock_waits：系统启动后到现在总共等待的次数