MySQL- 基础概念篇 - 锁处理并发

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

这里需要跟事务进行概念区分
事务主要是为了解决操作原子性的问题（多个操作中的一步报错，所有操作都回滚），同时为了保证多线程读写同一张表的过程中的读的问题（脏读，幻读，不可重复读等），提出了事务的概念，通过使用不同的隔离级别来以最低的性能损失读取到自己需要的数据，当然，事务中也可以有用于多线程写的处理，不过这个就是通过锁实现的了
而锁是为了解决多线程对同一数据的读写问题，锁更像是在事务中使用的一种底层工具，而事务是比锁高一级的工具。

MySQL 里面的锁大致可以分成
全局锁
表级锁
行锁

全局锁就是对整个数据库实例加锁。全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
具体操作是：
命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（dml，数据的增删改）、数据定义语句（ddl，包括建表、修改表结构等）和更新类事务的提交语句。

其实通过可重复读的事务隔离级别就能在不阻塞写功能的情况下对数据库进行备份，但是并不是所有的引擎都支持事务，比如 MyISAM，所以，我们仍然需要 FTWRL，
InnoDB 支持事务、外键和行锁

为什么不使用 set global readonly=true 的方式呢？
答案是影响更大，这里就不细说了

DDL is Data Definition Language which is used to define data structures.
DML is Data Manipulation Language which is used to manipulate data itself.

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
表锁
读锁：本线程可读，本线程不可写；其他线程可读，其他线程不可写
写锁：本线程可读，本线程可写；其他线程不可读，其他线程不可写

另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。（这一段很重要，解释了为什么需要 mdl）

加读锁则所有线程可正常读元数据，不影响增删改查操作，只是不能修改表结构；加写锁则只有拥有锁的线程可以读写元数据，也就是修改表结构，其它线程不能执行任何操作，包括修改表结构与增删改查。
这个逻辑其实跟表锁的读写锁的互斥逻辑是一样的

一个长事务获取了 mdl，迟迟不释放，此时一个连接想要给表加字段，于是阻塞，此时又有一个连接想要查询表，此时因为写锁优先级很高，它没拿到锁，后面都拿不到，因此全都阻塞，此时，如果这个表是个经常查询的热点表，就会有非常多的链接阻塞，如果数据库连接池有重试机制，那么就会爆掉
直到最开始的事务退出，释放 mdl。

申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到 MDL 锁后，直到事务执行完才会将锁释放。mdl 也存在两阶段提交。

简而言之，在事务未提交时无法修改表字段，而且在存在长事务时执行修改表字段命令是一个危险的操作，可能阻塞其它增删改查请求，或导致线程爆满

所以长事务危害真的很大。
长事务一是严重威胁并发性，二是导致（undo log）回滚段不能回收长时间被占用空间

如果确实要给热点数据做表结构变更要带上超时时间拿不到写锁就放弃
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

这里可以看出涉及到多线程，锁相关的部分，不管是 java 还是 mysql，解决的办法都差不多，即为了防止死锁或者长时间卡顿，通过加入超时时间的方法来解决。
Java 里面用 trylock 也可以带上超时时间

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。一般不会使用，要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎；
要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了。
因为，原本需要用到表锁的场景都可以直接用事务替代，因为事务自动添加 MDL 锁，MDL 锁能够区别对待表结构的修改和普通增删改查，粒度更细。

当备库用–single-transaction 做逻辑备份的时候，如果从主库的 binlog 传来一个 DDL 语句会怎么样？
我们先了解备份过程。。。
首先会开启事务
然后在备份每一个表之前都会单独保存一个保存点，然后开始备份数据
先查看表结构
然后导出数据
然后回滚保存点，这是为了释放被导出表的 mdl 锁，备份一个表就建一个检查点也是为了最大程度地减少 mdl 锁的占用，不然就得在所有表都备份完事务提交时候才能释放读锁
回滚完之后再重复此过程去备份别的表
如果在查看表结构之前到达，那备份的就是 ddl 更新后的表，
如果在查看表结构之后，查询数据之前到达，此时还没开始查询，因此 ddl 语句可以获得的 mdl 的写锁，更新完之后再查询数据，这样会报错
如果在查询数据的时候到达，mdl 读锁已经被获取，不能修改，因此 ddl 语句只能阻塞到读锁释放，也就是备份完此表之后，这个时候 ddl 语句没有被备份下来。恢复的时候会发现问题
查询完数据之后到达，也会阻塞等到当前这个表备份完之后再执行。这里涉及到两阶段提交

行锁
行锁的重要性：
MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。
不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。
行锁可以保证，同一时间，只有一个线程在更新这行数据。
事务中更新一行数据也会获取行锁，那么另一个事务如果同时也准备更新这一行数据，那就会阻塞。

两阶段锁协议
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
知道了这个设定，对我们使用事务有什么帮助呢？那就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。这是为了尽量减少行锁的持有时间，避免其他线程（事务）等待太久。

死锁和死锁检测
事务 A 和事务 B 在互相等待对方的提交事务之后资源行锁，就是进入了死锁状态。
有两种策略
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

超时都很好理解，死锁检查是什么意思
过程示例：新来的线程 F，被锁了后就要检查锁住 F 的线程（假设为 D）是否被锁，如果没有被锁，则没有死锁，如果被锁了，还要查看锁住线程 D 的是谁，如果是 F，那么肯定死锁了，如果不是 F（假设为 B），那么就要继续判断锁住线程 B 的是谁，一直跟下去，直到发现线程没有被锁（无死锁，就是事务没提交，得等它提交）或者被 F 锁住（死锁）才会终止
每一个等待行锁的事务都要进行一遍这样的检查，这个确实是很大的消耗。
1000 个线程并发访问一行数据，第一次，999 个线程并发持有当前行锁的线程的状态，每个线程检查一次
第二轮 998 个，第三轮 997 个，1+2+3+...+（n-1），然后复杂度为 O（n2）
我觉得极客时间里的那个点赞最高的讨论像是强行往上靠
虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

这两种策略同样可以应用到 Java 编程中，Java 中也可以进行死锁检测，也可以在代码里避免死锁

怎么解决由这种热点行更新导致的性能问题呢？

首先如果你肯定没有死锁，那么关掉死锁检测也是可以的，但是这个操作有相当高的风险，一般不建议使用，因为一般情况下，检测到了死锁，就回滚事务，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着得等待锁超时，这个会等 50s，期间其他等待的线程也会超时，最终可能会出现大量的超时，这是业务有损的。

不能关闭死锁检测，那我们只能降低并发量来降低死锁检测的成本，怎么实现这一点

1、客户端做并发限制，这一点收效甚微，因为一个客户端并发量再小，客户端数量一多，并发量依然还是很高
2、MySQL 中间件做并发限制
3、修改数据库源码，排队控制并发
基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。
所有方案的目的都是为了降低高并发情况下更新热点数据时死锁检测带来的 CPU 性能消耗

除此之外还可以尝试一行拆成多行，分散并发的压力，
这其实用到的就是分段锁
java 中的 LongAdder 就是用了分段锁。

避免长事务的一种方式，我们可以把一个长事务拆分成很多个小事务，此外，善用检查点，回滚检查点也可以释放锁

常见的业务问题

在事务的可重复读的隔离级别下，' 先校验再写入”的事务，在并发环境下，校验操作可能有逻辑异常，比如库存系统中，扣库存前我们需要先查询是否还有库存，有库存的情况下才能发货，但是现在货物如果只有一个了，AB 两个线程同时发起事务，a 先查询，b 再查询，然后 a 更新货物数量为 0，b 事务因为可重复读，读到的也是数量为 1，也进行了数量更新，ab 都成功了，只剩一个货物了，但是发了两单，

这种场景可以在 MySQL 中通过 SQL 实现，
那就是在查询的时候在语句最后加上 for update，这样就获得了一个排它锁，只有等事务提交之后释放锁，其他事务才能读写

不过实际编码中尽量不要将计算逻辑放入 SQL；

但是使用 select for update 会比较影响并发的性能，所以，一般我们建议 select 语句不加锁，而是通过 update 语句添加条件，在条件不满足的时候不更新，更新行数为 0，以便在更新行数为 0 点时候进行重试。

这种需求，如果是在分布式多台机器之间，一般建议使用分布式锁保证处理是串行的，如对 A、B 这两个线程的用户的的 uid 按照字典顺序排列后取 hash 计算分布式锁 key；

select for update 的解析：https://www.modb.pro/db/607519

select for update 添加的到底是什么锁？
https://www.51cto.com/article/744551.html

还有一种场景，
关注是一张表，follow，id，userid，followid
好友是一张表 friend，id，friendaid，friendbid
业务是，a 关注 b 的时候，如果 b 已经关注了 a，那 ab 就为好友，否则仅关注即可
此时如果 ab 同时关注对方，
AB 两个线程同时关注对方，同时发起事务，a 先查询 friend 表，没查到 b 关注 a，b 再查询 friend 表，没发现 a 关注 b，然后 a 添加一条数据到 follow 表，b 也添加了一条数数据到 follow 表，最终在 follow 表中，a 关注了 b，b 关注了 a，但是 ab 并没有添加到 friend 中。
与上面这个场景的不同在于，数据一开始并不存在，无法使用排它锁。

一个简单的思路就是，将 ab 相关关注，变为一条数据，并用一个状态比如 relation_ship 来表示关注的状态为 a 关注 b，对应状态 1，还是 b 关注 a 对应状态 2，还是相互关注，对应状态 3
这样，就能让行锁发挥作用，是关注操作串行化，这其实是一个非常经典的思路，通过行锁来处理并发问题。
通过 insert into 'follow' on duplicate key update 来实现。重复插入的更新效果
当插入已存在主键的记录时，将插入操作变为修改 relation_ship
insert into 'follow' on duplicate key update relation_ship=relation_ship | 1
insert into 'follow' on duplicate key update relation_ship=relation_ship | 2

具体过程就是：
这里的 AB，并不是确切的用户 a 和用户 b，而是如方程里面的变量 x，y，假设：
第一次的时候，a 的 id 位 3，b 的 id 为 4，当 a 关注 b 时，（此时 a 对应的是文中的 A，b 对应的是文中的 B）由于 A 的 id 小于 B 的 id，所以采用 insert into follow(user_id, liker_id, relation_ship) values(A, B, 1)，此时 like 表中 user_id = 3,liker_id 为 4，relation_ship = 1。
第二次的时候，当 b 关注 a 时，（此时 b 对应的是文中的 A，a 对应的是文中的 B），由于 A 的 id 大于 B 的 id，所以采用 insert into like(user_id, liker_id, relation_ship) values(B, A, 2)，B 对应的 id 为 3，A 对应的 id 为 4，relation_ship = 2,由于采用了 insert … on duplicate 语句，此时 relation_ship 采用位运算 1 ｜ 2 ，结果是 3，即用户 a 与用户 b 是相互喜欢，变成了事件 3，就会在 friend 表中添加两者相互喜欢的记录。

位运算
1 是 01，2 是 10，1|2，为 11，就是 3
同时 1|1 还是 1，2|2 还是 2，重复插入也不会报错

ON DUPLICATE KEY UPDATE 的用法解析：
如果在 INSERT 语句末尾指定了 ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个 UNIQUE 索引或 PRIMARY KEY 中出现重复值，则在出现重复值的行执行 UPDATE；
如果不会导致唯一值列重复的问题，则插入新行。例如，如果列 a 为主键或拥有 UNIQUE 索引，并且包含值 1，则以下两个语句具有相同的效果：
复制代码代码如下: INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE TABLE SET c=c+1 WHERE a=1;
如果行作为新记录被插入，则受影响行的值显示 1；如果原有的记录被更新，则受影响行的值显示 2。
这个语法还可以这样用: 如果 INSERT 多行记录 (假设 a 为主键或 a 是一个 UNIQUE 索引列):
复制代码代码如下: INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1;
执行后, c 的值会变为 4 (第二条与第一条重复, c 在原值上 +1).
复制代码代码如下: INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c); 执行后, c 的值会变为 7 (第二条与第一条重复, c 在直接取重复的值 7).
注意：ON DUPLICATE KEY UPDATE 只是 MySQL 的特有语法，并不是 SQL 标准语法！

mysql 插入数据有 3 中方式：
1.insertinto 如果主键重复会报错
2.replaceinto 如果主键或者唯一索引重复的话，会替换掉原数据
3.inserti gnore into 如果主键或则唯一索引重复，则会跳过该数据