【mysql】全局锁，表锁，行锁

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

全局锁

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

全局锁的典型使用场景是，做全库逻辑备份，全局备份第1种方式：

全局锁做全库逻辑备份的劣势：

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
• 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

全局备份的第2种方法：

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

一致性读是好，但前提是引擎要支持这个隔离级别（可重复读隔离级别）

single-transaction 方法只适用于所有的表使用事务引擎的库，如果不支持，那么备份就只能通过 FTWRL 方法（ InnoDB 支持事务，而mylsam 不支持事务）

全局备份的第3种方式：

另外，全库只读，还可以使用set global readonly=true 的方式。但不建议使用原因如下：
一、readonly 的值会被用来做其他逻辑，如判断一个库是主库还是备库。
二、在异常处理机制上有差异。执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态

表级锁

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

（1）表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象【对表加**读锁（共享读锁）后，自己也不能对其进行修改（不能写，读写互斥）；自己和其他线程只能读取该表（读锁是共享的）。 当对某个表执加上写锁（独占写锁，或排他写锁）**后（lock table t2 write），该线程可以对这个表进行读写，其他线程对该表的读和写都受到阻塞】。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 支持行锁的引擎，一般不使用 lock tables 命令来控制并发。

（2）另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

元数据锁是server层的锁，表级锁，主要用于隔离DML（Data Manipulation Language，数据操纵语言，如select）和DDL（Data Definition Language，数据定义语言，如改表头新增一列）操作之间的干扰。每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

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

如何安全地给小表加字段？

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务

如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段。

kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

MariaDB 已经合并了 AliSQL 的这个功能（MySQL还不支持），所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

行锁

MyISAM被Innodb取代的原因： 1.不支持事务 2.不支持行锁（只能用表锁，意味着同一张表任何时刻只能有一个更新在执行，影响到业务并发度）

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放（MDL锁也是事务结束后再释放）。

事务 A 持有的两个记录的行锁，都是在 commit 的时候才释放的。事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。

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

死锁和死锁检测

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

事务 A 在等待事务 B 释放 id=2 的行锁（事务A持有id=1的行锁），而事务 B 在等待事务 A 释放 id=1 的行锁（事务B持有id=2的行锁，原因：行锁需要事务commit后才释放）。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置（默认50s）。
  不推荐使用该方式，一是50s等待太长，二是设置成1s太短（如是普通的锁等待，造成误伤）
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on。

主动检测死锁是一个事务被锁，就要看看它所依赖的线程有没有被别人锁住（如果并发线程太多，检测死锁耗费大量资源），如此循环，最后判断是否出现了循环等待，也就是死锁。怎么解决由这种热点行更新导致的性能问题呢？问题的症结在于，死锁检测要耗费大量的 CPU 资源，减少死锁的主要方向，控制访问相同资源的并发事务量。（1）如果业务一定不会出现死锁，关掉死锁检测，使用超时策略（2）控制并发度，在数据库服务端做并发控制，客户端做并发控制不行（600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000。），而数据库服务端做并发控制可以使用中间件或者修改MySQL源码（同一行数据更新，在进入引擎之前排队）。（3）将一行改成逻辑上的多行来减少锁冲突，如用10个记录来记录总额，加金额时，随机选一条记录来加。

总结：
高并发下避免死锁检测带来的负面影响：

1. 确保业务上不会产生死锁，直接将死锁检测关闭。（innodb 自带死锁检测）
2. 在数据库中间件中统一对更新同一行的请求进行排队，控制并发度。
3. 业务逻辑上进行优化，将一行数据分解成多行，降低写入压力。

补充知识

线程，事务，会话的关系

1、会话可以创建多个事务
比如：使用客端连接数据库，这样你就可以执行很多个事务了
2、一个事务只能由一个会话产生
在数据库里的事务，如果在执行的SQL都是由会话发起的，哪怕是自动执行的JOB也是由系统会话发起的
3、一个事务可能会产生一个或多个线程
比如RMAN备份，是可以创建多个线程可加快备份速度
4、一个线程在同一时间内只能执行一个事务
而一个线程，在没结束当前事务是无法释放资源来执行第二个事务

读锁和写锁

• 共享锁(S锁)又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S 锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
• 排他锁(X锁)又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A

写锁和读锁优先级

申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作

参考链接：
https://blog.csdn.net/q2878948/article/details/96430129

小表和大表

Oracle通过一个内部参数_small_table_threshold来定义大表和小表的界限。缺省的该参数等于2%的Buffer数量，如果表的大小小于该参数定义，Oracle认为该表为小表，否则Oracle认为该表为大表。

https://www.cnblogs.com/klb561/p/11343430.html