大家好，我是讯享网，很高兴认识大家。



前几天同事在晚上上线的时候执行sql语句造成锁表，想总结一下以避免后续发生。

　　依次执行1-6步，运行第6步生成的语句即可。

　　如果特别着急，运行 1 2 6 步 以及第6步生成的kill语句 即可。

第1步　查看表是否在使用。

如果查询结果不为空，继续后续的步骤。

　

第2步　查看数据库当前的进程，看一下有无正在执行的慢SQL记录线程。

第3步　当前运行的所有事务

　

第4步　当前出现的锁

　

第5步　锁等待的对应关系

第6步　批量删除事务表中的事务

这里用的方法是：通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后执行临时文件中生成的指令。

记得修改对应的数据库名。

这个语句执行后结果如下：

执行结果里的两个kill语句即可解决锁表。 　

　首先问几个问题：

1. MySQL里有哪些锁？
2. 如何造成锁表？
3. 如何造成死锁？
4. 全局锁加锁方法的执行命令是什么?主要的应用场景是什么?
5. 做整库备份时为什么要加全局锁?
6. MySQL的自带备份工具, 使用什么参数可以确保一致性视图, 在什么场景下不适用?
7. 不建议使用set global readonly = true的方法加全局锁有哪两点原因?
8. 表级锁有哪两种类型? 各自的使用场景是什么?
9. MDL中读写锁之间的互斥关系怎样的?
10. 如何安全的给小表增加字段?

show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户 能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。

SHOW PROCESSLIST shows which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

在第一个shell里观察

在第二个shell里执行  故意打开事务，然后执行语句不提交，占用写锁。

在第三个shell里执行  执行删除语句，造成锁等待。

这个时候 session3在等待session2释放写锁。

如果再在 第三个shell里执行

在 第二个shell里执行

就会相互等待，造成死锁。

然后在第一个shell里查看

可以看到下面的查询语句有结果，确实是锁表了。

这儿有两种观点，一种是只kill掉后面等待的那个语句。还有一种是把两个语句都kill掉。这个根据实际情况处理。

杀掉41

然后到第3个shell窗口查看，可以看到

因为第3个shell里执行的语句被kill掉了。

到这儿可以看到死锁解决了。

讯享网

但其实有个问题。第3个shell里的语句被kill掉了。但第2个shell里的语句还在执行。如果第二个shell里的事务不提交或者kill，在第3个shell里执行删除语句还会造成锁等待。

第二种观点的办法

然后同时杀掉 40 42 就可以。

根据加锁的粒度/范围，MySQL 里面的锁大致可以分成、和三类。

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

全局锁主要用在逻辑备份过程中。对于全部是 InnoDB 引擎的库，建议你选择使用  参数，对应用会更友好。

MySQL里表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁是在Server层实现的。ALTER TABLE之类的语句会使用表锁，忽略存储引擎的锁机制。

另一类表级的锁是 MDL（metadata lock)。

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

MDL是并发情况下维护数据的一致性，在表上有事务的时候,不可以对元数据经行写入操作,并且这个是在server层面实现的

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

知道了这个设定，对我们使用事务有什么帮助呢？那就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：

1. 从顾客 A 账户余额中扣除电影票价；
2. 给影院 B 的账户余额增加这张电影票价；
3. 记录一条交易日志。

MySQL数据库InnoDB存储引擎加锁过程

组合一: Read Committed 隔离级别，k列是主键，给定SQL：update t1 set update_time = now() where k = 10; 只需要将主键上 k = 10的记录加上X锁即可

组合二: Read Committed 隔离级别，k列有unique索引，unique索引上的k=10一条记录加上X锁，同时，会根据读取到的列，回主键索引(聚簇索引)，然后将聚簇索引上对应的主键索引项加X锁。

组合三: Read Committed 隔离级别，k列上有索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，然后将聚簇索引上对应的主键索引项加X锁。

组合四: Read Committed 隔离级别，若k列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

组合五: Repeatable Read 隔离级别，k列是主键列，给定SQL update t1 set update_time = now() where k = 10; 只需要将主键上 k = 10的记录加上X锁即可。

组合六: Repeatable Read 隔离级别，k列有unique索引，unique索引上的k=10一条记录加上X锁，同时，会根据读取到的列，回主键索引(聚簇索引)，然后将聚簇索引上对应的主键索引项加X锁。

组合七：Repeatable Read 隔离级别，k列有索引， 通过索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

考虑到B+树索引的有序性，满足条件的项一定是连续存放的。如果要插入一条记录，肯定会插入在相同位置，为了保证两次查询查到的值一致，MySQL选择了用GAP锁，将 查询值范围前、查询值范围、查询值范围后 三个GAP给锁起来。

GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

为了保证[8,2]与[10,3]间，[10,3]与[10,4]间，[10,4]与[40,5]不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个GAP给锁起来。

组合九：Serializable隔离级别下直接用加锁的方式来避免并行访问。

 

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

当出现死锁以后，有两种策略：

1. 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数  来设置。
2. 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数  设置为 on，表示开启这个逻辑。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

 

备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

假设这个 DDL 是针对表 t1 的， 这里我把备份过程中几个关键的语句列出来：

参考答案如下：

1. 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。

2. 如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；

3. 如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。

4. 从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

你会选择哪一种方法呢？为什么呢？

1. show engine innodb status 里面有信息，不过不是很全…
2. 5.7的reset_connection接口可以考虑一下
3. 用redis的话，为了避免超卖需要增加了很多机制来保证。修改都在数据库里执行就方便点。前提是要解决热点问题
4. 我认识几位处理问题和分析问题经验非常丰富的专家，不用懂源码，但是原理还是要很清楚的

这个问题的出现，应该是人为只要并发导致锁冲突吧？但是为什么不加单引号会死锁，加了单引号就能正常跑呢？

从并发系统性能的角度考虑，你觉得在这个事务序列里，应该先插入操作记录，还是应该先更新计数表呢？

这么设计，影院余额这一行的行锁在一个事务中不会停留很长时间。

 

 

来源https://weikeqin.com/2019/09/05/mysql-lock-table-solution/