命令启动mysql命令_史上最详细MySQL全局锁和表锁

1.全局锁

全局锁锁定整个数据库实例。 MySQL提供了增加全局读锁的方法。 该命令是带读锁刷新 (FTWRL)。

当需要使整个库处于只读状态时，可以使用该命令。 之后，其他线程的以下语句将被阻塞：数据更新语句（添加、删除和修改数据）、数据定义语句（包括创建表、修改表结构等）和更新事务的提交语句。

1.1 全局锁使用场景

全局锁的一个典型使用场景就是对整个数据库做一个逻辑备份()。再次主从的时候了

就是说把整个数据库的每一个表都取出来，保存为文本。

过去有一种方法是使用FTWRL来确保没有其他线程会更新数据库，然后备份整个数据库。 请注意，在备份过程中整个库是完全只读的。

数据库只读状态的危害：

注意：以上逻辑备份不包含 --- 参数。

看来加全局锁并不是一个好主意。 但仔细想一想，为什么要锁定备份呢？ 我们来看看如果不加锁会出现什么情况？

1.2 不加锁带来的问题

如手机卡、购买套餐信息

这里分为两个表（余额表），（费率套餐表）

步：

1、表中用户A的余额为：300

表中数据 用户A的包裹：空

2. 启动备份。 备份过程中，首先备份表。 表备份完成后，此时用户余额为300。

3、此时用户购买了100元的资费套餐，餐食购买完成，购买成功写入套餐表，备份期间数据成功。

4. 备份完成

可以看到，备份的结果是表中的数据没有变化，表中的数据已经接近购买了资费套餐100了。

如果此时用这个备份文件来恢复数据，用户A就赚了100，用户是不是很舒服？ 但考虑一下公司的利益。

也就是说，在没有加锁的情况下，备份系统备份的数据库不是一个逻辑时间点，数据在逻辑上是不一致的。

1.3 为什么需要全局读锁（FTWRL）？

有人可能会疑惑，官方的逻辑备份工具是什么？ 当使用参数---时，在导入数据之前会启动一个事务，以确保获得一致的快照视图。 由于MVCC的支持，这个过程中数据可以正常更新。

为什么我们需要 FTWRL？

一致的读取很好，但前提是引擎支持此隔离级别。 例如，对于不支持事务的引擎，如果备份过程中有更新，则始终只能获取到最新的数据，这就破坏了备份的一致性。 这时候我们就需要用到FTWRL命令了。

因此， - 方法仅适用于使用库的事务引擎的所有表。 如果有些表使用不支持事务的引擎，那么就只能通过FTWRL方法来完成备份。 这通常是 DBA 要求业务开发人员使用覆盖的原因之一。

1.4 全局锁的两种方法

1. 刷新写读锁

2.设置=true

既然你希望整个库是只读的，为什么不使用 set =true 呢？ 确实可以将整个库置于只读状态，但我还是推荐大家使用FTWRL方法，主要有以下几个原因：

首先，在某些系统中， 的值会用于其他逻辑，比如判断一个库是主库还是备库。 所以修改变量的方式影响比较大，我不建议大家使用。

其次，异常处理机制存在差异。 如果执行FTWRL命令后客户端异常断开，MySQL会自动释放全局锁，整个库恢复到可以正常更新的状态。 将整个库设置为 后，如果客户端出现异常，数据库会一直处于该状态，这会导致整个库长期处于不可写状态，风险较高。

第三，超级用户的权限无效。

注：业务更新不仅是增删改数据（DML），还可能是添加字段、修改表结构（DDL）等操作。 无论采用哪种方式，库全局锁定后，如果向库中任意表添加字段，该库就会被锁定。

即使没有全局锁定，添加字段也并不总是一帆风顺。 还有表级锁。

2.表级锁

MySQL中有两种表级锁：一种是表锁，另一种是元数据锁（meta data lock，MDL）。

2.1 表锁

锁定表名读取； #该表可以读取。 不能在ddl和dml中添加、删除或修改。 只能读取表数据。

锁定表名读取； # 既不能读也不能写

表锁的语法是lock...read/write。 与FTWRL类似，当客户端断开连接时，可以主动释放锁，也可以自动释放锁。 需要注意的是，锁语法不仅限制了其他线程的读写，也限制了本线程后续的操作对象。

例如，如果语句lock t1读，t2写； 在某个线程A中执行，其他线程写t1和读写t2的语句会被阻塞。 同时，线程A在执行之前只能进行读t1和读写t2的操作。 连对t1的写入都不允许，自然也就无法访问其他表了。

在更细粒度的锁出现之前，表锁是最常用的处理并发的方式。对于支持行锁的引擎，一般不使用lock命令来控制并发。 毕竟锁全表的影响还是太大了。

2.2 MDL锁

表级锁的另一种类型是MDL（锁）。 MDL不需要显式使用，它会在访问表时自动添加。 MDL的作用是保证读写的正确性。 你可以想象一下，如果一个查询正在遍历表中的数据，执行过程中另一个线程对表结构进行了更改，删除了某列，那么查询线程得到的结果与表结构不匹配，这肯定是不符合的。可能的。 的。

因此，MDL在MySQL 5.5版本中被引入。 对表进行增删改查时，添加MDL读锁； 对表进行结构更改时，添加 MDL 写锁。

虽然MDL锁是系统默认添加的，但它是一个你不能忽视的机制。

例如，在下面的例子中，我经常看到人们陷入这样的陷阱：向一个小表添加一个字段导致整个数据库崩溃。

要知道给表添加字段、修改字段或者添加索引都需要扫描整个表的数据。 在操作大表时，必须特别小心，避免影响线上服务。 事实上，即使是一块小手表，如果不小心操作，也可能会出现问题。 让我们看一下下面的操作序列，假设表 t 是一个小表。

注：表t 是 innodb 表，mysql版本是5.7.24 自动提交开启
1. sessionA:begin;select * from t limit 1; 2. sessionB:select * from t limit 1; 3. sessionC:alter table t add f int;#会mdl锁住 4. sessionD:select * from t limit 1;

显示完整 查看 mdl 锁详细信息

我们可以看到A先启动，此时会对表t加一个MDL读锁。 由于B也需要MDL读锁，所以可以正常执行。

之后C就会被阻塞，因为A的MDL读锁还没有释放，而它需要MDL写锁，所以只能被阻塞。

如果只有C本身被阻塞也没关系，但是后续所有对表t申请MDL读锁的请求也会被C阻塞。前面提到过，所有对该表的增删改查都需要首先申请MDL读锁，然后它们都被锁定，这意味着该表现在完全不可读写。

如果某个表有频繁的查询语句，并且客户端有重试机制，也就是说超时后又会发出新的请求，那么这个库的线程很快就会满。

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

注意：一般情况下，行锁都有一个锁超时时间。 但是，MDL 锁没有超时限制，只要事务未提交就会保持锁定状态。

2.2.1 如何解决这个MDL锁

上面不是说了，提交或者回滚这个事务。所以找到这个事务

如何找到这笔交易，通过查看该交易的执行时间。

# 查看事务超过60s的事务mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;trx_started 表示什么时候执行的这个事务 #查看系统当前时间mysql> select now();

交易开始时间和当前系统时间显示交易已经执行了多长时间。

查看该线程id

这个长事务的线程ID怎么处理呢？

首先看show full中的host字段； 以及谁连接了数据库。例子：我有上面的环境，进去或者/，如果不是环境，但是程序连接了，那么就会被杀掉。

2.2.2 发生在我身上的有趣的事情

上次有DBA问我导致主从延迟很大的问题怎么解决。

我问你如何解决延迟。 您知道主从延迟的具体原因吗？

他告诉我开了多线程，但是延迟还是很大，基本没用多线程。

我说你怎么知道主从延时呢？ 需要启用多线程复制才能解决。 他告诉我网上其他人的博客没有提到这个，我吐了出来。

后来我问他主从延时正常的时候做了什么操作。 他告诉我他修改了alter table结构。

然后让他看看是不是mdl锁引起的，让他显示满。 原来是mdl锁的原因。

然后告诉他或她找一笔长交易。 找到后，与开发人员讨论是否可以杀死这个长事务正在执行的操作。

注：这是别人问我这个问题时我的真实经历。 首先你需要知道你做了什么操作导致了这个结果，然后解决问题。 最根本的还是要知道原因，下次才能避免。

还有线上环境、系统版本、应用版本、遇到的问题等。 它们和你的一样吗？ 有时候，不要盲目相信他们。

2.3 如何安全地向小表添加字段？

首先，我们需要解决长事务。 如果事务没有提交，就会一直占用MDL锁。 在MySQL库的表中，您可以检查当前正在执行的事务。 如果你想通过DDL改变的表恰好有一个长事务正在执行，可以考虑先暂停DDL或者杀死这个长事务。 这就是为什么需要在非高峰时段进行ddl更改。 当然，还要考虑具体使用的ddl，参考官方的ddl。

2.4 ddl过程

获取MDL写锁

降级为MDL读锁

真正做DDL

升级到MDL写锁

释放MDL锁

如果没有锁冲突，1、2、4和5的执行时间非常短。 步骤3占用了大部分DDL时间。 在此期间，表可以正常读写数据，故称为“”

来源：