06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？
Connection连接与Session会话
通俗来讲，会话(Session)是通信双⽅从开始通信到通信结束期间的⼀个上下文（Context）。这个上下文是⼀段位于服务器端的内存：记录了本次连接的客户端机器、通过哪个应用程序、哪个用户登录等信息。
连接是客户端和服务器之间的物理通信链路
[En]
A connection is a physical communication link between the client and the server
根据加锁的范围，MySQL 里面的锁大致可以分成 全局锁、表级锁和 行锁三类。
全局锁
全局锁用于锁定整个数据库实例
[En]
A global lock is to lock the entire database instance
Flush tables with read lock (FTWRL)
当你需要让整个库处于 只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据 更新语句（数据的增删改）（DML) 、数据 定义语句（包括建表、修改表结构等）（DDL）和更新类 事务的提交语句。
全局锁的典型使用场景是，做全库 逻辑 备份。也就是把整库每个表都 select 出来存成文本。
Q： 在可重复读隔离级别下开启一个事务是一致性视图，这时由于MVCC数据是可以正常更新的，所以为什么不用这种方式？
A：官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。但存在一个问题： single-transaction 方法只适用于所有的表使用事务引擎的库。而MyISAM不支持事务
Q：既然要全库只读，为什么不使用 set global readonly=true 的方式呢？
A：
- 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如 用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。
- 二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生 异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则 数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
表级锁
表级别的锁有两种：一种是 表锁，一种是 元数据锁（meta data lock，MDL)。
lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
另一类表级的锁是 MDL（metadata lock)。
MDL作用是防止 DDL和 DML并发的冲突，
MDL 不需要显式使用，在访问一个表的时候会被自动加上。事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。并且之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞
MDL 会直到事务提交才释放， 在做表结构变更的时候，一定要小心 不要导致锁住线上查询和更新。
Q： 如何安全地给小表加字段？
A：首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。如果你要做 DDL 变更的表刚好有长事务在执行， 要考虑先暂停 DDL，或者 kill 掉这个长事务。
Q：如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？
A：这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是， 在 alter table 语句里面设定等待时间， 如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过 重试命令重复这个过程。
Q：备份一般都会在 备库上执行，你在用–single-transaction 方法 做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？
A：假设这个 DDL 是针对表 t1 的， 备份过程中几个关键的语句：
/* 在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1)*/
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/* 启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2) */
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT；
/* 设置一个保存点，这个很重要（Q3）； */
Q3:SAVEPOINT sp;
/* show create 是为了拿到表结构 (Q4) 时刻 1 */
Q4:show create table t1;
/* 正式导数据 （Q5） 时刻 2 */
Q5:SELECT * FROM t1;
/* 回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
DDL 从主库传过来的时间按照效果不同，分为四个时刻。
标题设置为一个小表，我们假设到达后，如果执行开始，执行将很快完成。
[En]
The title is set to a small table, and we assume that after arrival, if the execution starts, the execution will be completed quickly.
- 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
- 如果在”时刻 2″到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
- 如果在”时刻 2″和”时刻 3″之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象： 主从延迟，直到 Q6 执行完成。
- 从”时刻 4″开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。
07 | 行锁功过：怎么减少行锁对性能的影响？
MySQL 的行锁
MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。
行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。
两阶段锁
事务 A 持有的两个记录的行锁，都是在 commit 的时候才释放的。 事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。
在 InnoDB 事务中，行锁是 在需要的时候才加上的，但并 不是不需要了就立刻释放，而是 要等到事务结束时才释放。这个就是两阶段锁协议。
如果需要锁定事务中的多行，请尽可能放回最有可能导致锁冲突和影响并发性的锁。
[En]
If you need to lock multiple lines in your transaction, put back the locks that are most likely to cause lock conflicts and affect concurrency as far as possible.
死锁和死锁检测
当并发系统中的不同线程具有循环的资源依赖关系时，所涉及的线程都在等待其他线程释放资源，这会导致这些线程进入无限等待状态，这称为死锁。
[En]
When different threads in the concurrent system have cyclic resource dependencies, and the threads involved are waiting for other threads to release resources, it will cause these threads to enter a state of infinite waiting, which is called deadlock.
这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。
当出现死锁以后，有 两种策略：
- 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
- 另一种策略是，发起死锁 检测，发现死锁后， 主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。每当一个事务 被锁的时候， 就要看看它所依赖的线程有没有被别人锁住 n2复杂度），如此 循环，最后判断是否出现了 循环等待，也就是死锁。(死锁的四个必要条件 互斥,请求与保持,不可剥夺，循环等待)
死锁检测要耗费大量的 CPU 资源
Q:怎么解决由这种热点行更新导致的性能问题呢？
A:
- 临时把死锁检测关掉。可能会出现大量的超时，这是业务有损的。
- 控制并发度，在中间件实现， 对于相同行的更新，在进入引擎之前 排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。
- 从设计上优化，拆分一行改为逻辑上的多行，随机选一个加上，再求和
Q：如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：
- 第一种，直接执行 delete from T limit 10000;
- 第二种，在一个连接中循环执行 20 次 delete from T limit 500;
- 第三种，在 20 个连接中同时执行 delete from T limit 500。
你会选择哪种方式？为什么？
[En]
Which method do you choose? Why?
A：
第二种方式比较好。
[En]
The second way is relatively good.
第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。
第三种方式（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。
08 | 事务到底是隔离的还是不隔离的？
如果是可重复读隔离级别，事务 T 启动的时候会创建一个视图 read-view，之后事务 T 执行期间，即使有其他事务修改了数据， 事务 T 看到的仍然跟在启动时看到的一样。也就是说，一个在可重复读隔离级别下执行的事务，好像与世无争，不受外界影响。
事务需要更新一行。如果另一个事务恰好具有该行的行锁，则不能如此分离它。它将被锁定并进入等待状态。
[En]
A transaction needs to update a row. If another transaction happens to have a row lock for that row, it cannot be so detached. It will be locked and enter the waiting state * .*
Q：既然进入了等待状态，那么 等到这个事务自己获取到行锁要更新数据的时候，它 读到的值是什么呢？
例如，下面是一个只有两行的表的初始化语句。
[En]
For example, the following is an initialization statement for a table with only two rows.
mysql> CREATE TABLE t (
id int(11) NOT NULL,
k int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);
事务开始的时间点，分为两种情况：
[En]
The point in time at which the transaction starts, which is divided into two situations:
1）start transaction 时，是 第一条语句的执行时间点，就是 事务开始的时间点，第一条select语句建立一致性读的snapshot；
2）start transaction with consistent snapshot 时，则是 立即建立本事务的一致性读snapshot，当然也开始事务了；
在这个例子中，事务 C 没有显式地使用 begin/commit，表示这个 update 语句本身就是一个事务，语句完成的时候会自动提交。
事务 B 在更新了行之后查询 ;
事务 A 在一个只读事务中查询，并且时间顺序上是在事务 B 的查询之后。
结果：事务 B 查到的 k 的值是 3，而事务 A 查到的 k 的值是 1
A：
不妨做如下假设：
- 事务 A 开始前，系统里面只有一个活跃事务 ID 是 99；
- 事务 A、B、C 的版本号分别是 100、101、102，且当前系统里只有这四个事务；
- 三个事务开始前，(1,1） 这一行数据的 row trx_id 是 90。
从图中可以看到，第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。这时候， 这个数据的最新版本的 row trx_id 是 102，而 90 这个版本已经成为了历史版本。
第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。这时候，这个数据的最新版本（即 row trx_id）是 101，而 102 又成为了历史版本。
在事务 A 查询的时候，其实事务 B 还没有提交，但是它生成的 (1,3) 这个版本已经变成当前版本了。但这个版本对事务 A 必须是不可见的，否则就变成 脏读了。
事务 A 要来读数据了， 它的视图数组是 [99,100]。当然了， 读数据都是从当前版本读起的。所以，事务 A 查询语句的读数据流程是这样的：
- 找到 (1,3) 的时候，判断出 row trx_id=101，比高水位大，处于红色区域，不可见；
- 接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见；
- 再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。
总结：
对于数据版本，事务视图有三种情况，除了您自己的更新始终可见：
[En]
For a data version, there are three situations for a transaction view, except for * your own updates are always visible * :
- 版本未提交，不可见；
- 版本已提交，但是是在视图创建后提交的，不可见；
- 版本已提交，而且是在视图创建前提交的，可见。
两个”视图”的概念
在 MySQL 里，有两个”视图”的概念：
- 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
- 另一个是 InnoDB 在实现 MVCC 时用到的 一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。
它没有物理结构，用于定义在事务执行期间“我可以看到什么数据”。
[En]
It has no physical structure and is used to define “what data can I see” during transaction execution.
“快照”在 MVCC 里是怎么工作的？
在可重复可读隔离级别，事务在启动时“拍摄快照”。请注意，此快照基于整个库。
[En]
At the repeatable readable isolation level, the transaction “took a snapshot” when it started. Note that this snapshot is * based on the entire library.*
InnoDB 里面 每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按 申请顺序严格递增的。
而 每行数据也都是有多个版本的。每次 事务更新 数据的时候，都会生成一个 新的数据版本，并且把 transaction id 赋值给 这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中， 能够有信息可以直接拿到它。
也就是说，数据表中的一行记录， 其实可能有多个版本 (row)，每个版本有自己的 row trx_id。
图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25。
实际上，图 2 中的 三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的， 而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是 通过 V4 依次执行 U3、U2 算出来。
按照可重复读的定义， 一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间， 其他事务的更新对它不可见。因此，一个事务只需要在启动的时候声明说，”以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到 它的上一个版本“。当然，如果”上一个版本”也不可见，那就得继续往前找。还有 ，如果是这个事务自己更新的数据，它自己还是要认的。
在实现上， InnoDB 为每个事务构造了一个数组，用来保存 这个事务启动瞬间， 当前正在”活跃”的所有事务 ID。”活跃”指的就是， 启动了但还没提交。
数组里面事务 ID 的 最小值记为低水位，当前系统里面 已经创建过的事务 ID 的最大值加 1 记为高水位。
这个视图数组和高水位，就组成了当前事务的 一致性视图（read-view）
数据版本的可见性规则，就是基于 数据的 row trx_id 和这个一致性视图的对比结果得到的。
这个视图数组把所有的 row trx_id 分成了几种不同的情况。
- 如果落在绿色部分，表示这个版本是已提交的事务或者是 当前事务自己生成的，这个数据是可见的；
- 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
- 如果落在黄色部分，那就包括两种情况
a. 若 row trx_id 在数组中，表示这个版本是由 还没提交的事务生成的，不可见；
b. 若 row trx_id 不在数组中，表示这个版本是 已经提交了的事务生成的，可见。
有了这个声明后，系统里面随后发生的更新，就跟这个事务看到的内容无关了。因为之后的更新， 生成的版本一定属于上面的 2 或者 3(a) 的情况，而对它来说， 这些新的数据版本是不存在的，所以这个事务的快照，就是 “静态”的了。
如果有一个事务，它的低水位是 18，那么当它访问这一行数据时，就会从 V4 通过 U3 计算出 V3，所以在它看来，这一行的值是 11。
InnoDB 利用了”所有数据都有多个版本”的这个特性，实现了”秒级创建快照”的能力。
更新逻辑
Q: 事务 B 的 update 语句，如果按照一致性读，好像结果不对哦？
事务 B 的视图数组是先生成的，之后事务 C 才提交，不是应该看不见 (1,2) 吗，怎么能算出 (1,3) 来？
A：如果事务 B 在更新之前查询一次数据，这个查询返回的 k 的值确实是 1。
但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则 事务 C 的更新就丢失了。因此， 事务 B 此时的 set k=k+1 是在（1,2）的基础上进行的操作。
更新数据都是先读后写的，而这个读，只能读当前的值，称为”当前读”（current read）。
因此，在更新的时候，当前读拿到的数据是 (1,2)，更新后 生成了新版本的数据 (1,3)， 这个新版本的 row trx_id 是 101。所以，在执行事务 B 查询语句的时候，一看自己的版本号是 101，最新数据的版本号也是 101，是自己的更新，可以直接使用， 所以查询得到的 k 的值是 3。
除了 update 语句外， select 语句如果加锁，也是当前读。
所以，如果把事务 A 的查询语句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update， 也都可以读到版本号是 101 的数据，返回的 k 的值是 3。
下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）。
select k from t where id=1 lock in share mode;
select k from t where id=1 for update;
Q：假设事务 C 不是马上提交的，而是变成了下面的事务 C’，会怎么样呢？
事务 C’的不同是，更新后并没有马上提交，在它提交前， 事务 B 的更新语句先发起了。前面说过了，虽然事务 C’还没提交，但是 (1,2) 这个版本也已经生成了，并且是当前的最新版本。那么， 事务 B 的更新语句会怎么处理呢？
A：考虑两阶段锁协议，事务 C’没提交， 也就是说 (1,2) 这个版本上的写锁还没释放。而事务 B 是 当前读，必须要 读最新版本，而且必须加锁， 因此就被锁住了， 必须等到事务 C’释放这个锁，才能继续它的当前读。
事务的可重复读的能力是怎么实现的？
可重复读的核心就是 一致性读（consistent read）；而事务更新数据的时候，只能用 当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入 锁等待。
读提交的逻辑和可重复读
阅读提交的逻辑类似于可重复阅读的逻辑，主要区别是：
[En]
The logic of read submission is similar to that of repeatable readings, with the main difference being:
- 在可重复可读隔离级别，您只需在事务开始时创建一致性视图，该视图由事务中的所有其他查询共享
[En]
at the repeatable readable isolation level, you only need to create a consistency view at the beginning of the transaction, which is shared by all other queries in the transaction*
- 在读提交隔离级别下， 每一个语句执行前都会重新算出一个新的视图。”start transaction with consistent snapshot; “的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的 start transaction。
导致：
- 对于可重复的可读性，查询只确认在事务开始之前提交的数据。
[En]
for repeatable readability, the query only acknowledges data that has been committed before the transaction starts.*
- 对于读提交，查询只承认 在语句启动前就已经提交完成的数据；
Q：在读提交隔离级别下，事务 A 和事务 B 的查询语句查到的 k，分别应该是多少呢？
A：
下面是读提交时的状态图，可以看到这两个查询语句的创建视图数组的时机发生了变化，就是图中的 read view 框。（注意：这里，我们用的还是事务 C 的逻辑直接提交，而不是事务 C’）
这时，事务 A 的查询语句的视图数组是在执行这个语句的时候创建的，时序上 (1,2)、(1,3) 的生成时间都在创建这个视图数组的时刻之前。但是，在这个时刻：
- (1,3) 还没提交，属于情况 1，不可见；
- (1,2) 提交了，属于情况 3，可见。
所以，这时候事务 A 查询语句返回的是 k=2，事务 B 查询结果 k=3，能看到已提交的。。
Q：用下面的表结构和初始化语句作为试验环境，事务隔离级别是可重复读。现在，我要把所有”字段 c 和 id 值相等的行”的 c 值清零，但是却发现了一个”诡异”的、改不掉的情况。请你构造出这种情况，并说明其原理。
CREATE TABLE t (
id int(11) NOT NULL,
c int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);
A：如何构造一个”数据无法修改”的场景。
Original: https://www.cnblogs.com/ydssx7/p/16513214.html
Author: ydssx
Title: MySQL实战45讲 6,7,8
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505166/
转载文章受原作者版权保护。转载请注明原作者出处！