【MySql】06 笔记 Mysql 并发场景下的锁

一、介绍

1、出现并发访问的时候，锁就是用来实现这些访问规则的重要数据结构。

2、根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

二、全局锁

1、全局锁就是对整个数据库实例加锁。

2、MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。

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

需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数 据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。

4、如果备份数据是不加事务

1、User 在备份用户余额表时买了一件衣服,就在刚备份完时。数据备份完恢复后那么此时User 有一键衣服但是钱没减。那对于商家来说衣服买了没钱？

2、反过来,先备份衣服订单表时User, 刚备份完 买了一件服务,备份完恢复后User发现钱没了,没有衣服。

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

6、如果引擎不支持事务 => FTWRL 命令

7、set global readonly=true 为什么不用？

1. 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库 还是备库。修改 global 变量的方式影响面更大。

2. 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开， 那么MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。设置为readonly之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这 样会导致整个库长时间处于不可写状态，风险较高。

三、表级锁

1、表锁

1. 表锁的语法是 lock tables … read/write

2. 可以用 unlock tables 主动 释放锁，也可以在客户端断开的时候自动释放。

3. lock tables 语法除了会限制别 的线程的读写外，也限定了本线程接下来的操作对象。

4. 例子 线程 A 中执行 lock tables t1 read, t2 write，则其他线程写t1、读写t2 的语句都会被阻塞。同时，线程A在执行 unlock tables之前，也只能执行读t1、读写 t2的操作。连写 t1 都不允许，也不能访问其他表。

5. 一般不使用 lock tables命令来控制并发，锁住整个表的影响面还是太大。

2、元数据锁(meta data lock， MDL)。

1. MDL不需要显式使用，在访问一个表的时候会被自动加上。作用: 保证读写的正确性。

2. 读锁之间不互斥，可以有多个线程同时对一张表增删改查。

3. 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个
   线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

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

5. MDL锁是系统默认会加的给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。如果表的查询等其他语句执行频繁,客户端有重试机制。可能会导致数据库奔溃==> 线程爆满

6. A B C D 四个线程 A 读锁、 B 写锁 、C 读锁、 D写锁。 B C D都会堵塞后期换回新起会话导致数据库压力 => 奔溃

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

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

4、要变更的表是一个热点表?

在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到 也不要阻塞后面的业务语句，先放弃。之后再通过重试命令重复这个过程。

> ALTER TABLE tbl_name NOWAIT add column ...
> ALTER TABLE tbl_name N add colum ...