十三、MySQL数据库的锁，全局锁、表锁和行锁的应用

锁是在处理并发访问数据时，用于定义访问规则的数据结构。MySQL 中的锁根据作用范围分类有全局锁，表级锁和行锁。

全局锁

当你需要对数据库进行整库备份时，为了保证备份时刻的所有数据一致性，需要确保数据库在备份期间不进行数据更改操作。

考虑一般情况做数据备份时，正在进行下单的业务，假设有一个下单完成商品表和已付款金额表，下单完成的操作包含在商品表添加商品和在金额表记录是否付款。
此时，操作的顺序是：①备份下单商品表，②下单付款，③备份已付款金额表。若数据库崩溃恢复数据，则导致数据不完整，也就是商品已经下单完成，但是并没有付款数据。

因此在备份的时候，我们需要保证备份的所有数据都是同一时刻的。++其中一个方法就是开启全局读锁++。

MySQL 开启全局读锁命令：

Flush tables with read lock

使用这个命令将整个库至于只读状态，此时数据库更新语句（数据增删改）、数据定义语句（建表语句，修改表等）和存在更新语句的事务的提交均会被阻塞。

使用该方法做整库备份的缺陷：

1. 在主库进行备份会导致更新语句阻塞，业务停摆
2. 在从库备份，会导致备份期间不能执行从主库过来的 binlog，导致主从延迟。

++方法二：使用事务来保证数据的一致性，即可重复读隔离级别下。++

mysqldump 命令可以做逻辑备份，在使用参数 -single-transaction 时，可以开启一个事务，保证数据的一致性。

使用事务备份的缺陷：

1. 备份的库中所有的表的引擎必须支持事务

表级锁

表级锁分两种，一种是表锁，一种是元数据锁（meta data lock，MDL）。

表锁

表锁的语法如下：

#加锁
lock tables ... read/write

#释放锁，关闭客户端会自动释放锁
unlock tables

# 例如现在要给表 order 添加写锁，执行完操作再释放
lock tables order read;
执行操作
unlock tables;

需要注意的是，开启的表锁并非只会限制其他线程操作，也会限制当前线程的操作，例如：线程 A 对表 B 开启读锁，对表 C 开启写锁，则其他线程不能写表B不能读写表C，而当前线程只能读表B，写表C，不能做其他操作。

元数据锁 MDL

MDL 是为了保证读写数据时的正确性，例如，在读数据时，对表结构进行修改，增加了字段或者删了一列，那么读的数据就结构不一致了。

MDL 的逻辑是在增删改查数据时，加 MDL 读锁，在对表结构修改时，加 MDL 写锁。

1. MDL 读锁之间不互斥，即允许多个线程同时拿到 MDL 读锁。
2. MDL 读写锁和 MDL 写锁之间是互斥的，也就是有线程拿到 MDL 写锁后，其他线程（包括增删改查操作和表结构修改操作）只能阻塞等待。

注意：MDL 锁是系统默认会加的，不需要手动添加。

如何正确的修改表结构
当一个表在实际使用中总是被业务频繁访问时，该如何修改表才能不导致数据库崩溃呢？

首先，数据库崩溃的原因，是由于执行表结构修改后，若还有线程在操作该表，那么当前线程会被阻塞等待，并且会导致后面所有的线程一起等待，当阻塞时间过长时就可能导致数据库崩溃。

行锁

行锁是由数据库引擎各自实现的，InnoDB 实现了行锁，但 MyISAM 引擎没有实现行锁，因此 MyISAM 引擎的表同一时刻只支持一个线程对表进行更新。

行锁，顾名思义，对数据表中的行加锁，锁粒度比表锁小，优势是可以支持更大的并发量。同一行数据同一时刻只允许一个线程修改，但不同行的数据可以同时被不同线程修改。

行锁的缺陷是会导致死锁。

两阶段加锁协议

事务具有四大特性，即原子性，一致性，隔离性和持久性。为了保证事务执行过程是串行化的，保证事务的隔离性，在对一行数据进行操作时，加锁，在事务结束时释放锁。

注意，事务执行过程中，对于一行数据，是需要修改的时候则加行锁，但是并不是用完就释放的，而是要到事务结束才释放行锁，这是为了保证事务的隔离性。这就是两阶段锁协议。

因此，根据两阶段锁协议，在一个事务中，将热点数据的操作放在最后一步将会获得更好的性能，即热点数据的锁被占有的时间越短越好。

死锁和死锁检测

行锁虽然提高了并发量提高了整体性能，但是它在某些情况下会导致死锁，性能便会大大降低。

死锁是指两个线程在互相等待对方解锁资源的现象，例如，线程 A 拿到了资源 B，线程 C 拿到了资源 D，此时线程 A 需要资源 D 才能继续往下执行，而线程 C 需要拿到资源 B 才能继续往下执行，此时两个线程就走进了互相等待互不放手的死胡同。

如何解决死锁：

1. 设置等待超时时间。InnoDB 参数 innodb_lock_wait_timeout 可以设置获取资源时的等待超时时间，一旦超过时间则放弃等待并且释放自己所持有的锁。
2. 发起死锁检测，参数 innodb_deadlock_detect 设置为 on 时，表示开启死锁检测逻辑。

InnoDB 死锁等待时长默认是 50s，通常情况这个时长是无法忍受的，但如果把时间设置太短，则会出现误伤情况，即不是死锁等待的情况也被放弃等待。

因此一般情况都使用死锁检测策略，实际上 InnoDB 默认也是开启这个策略的。

死锁检测的性能问题和解决方案

当事务在给资源加锁时等待，就会触发死锁检测动作，检测原理是每个事务当做一个节点，当事务 A 等待事务 B 得资源时，则 A --> B，最后形成有向图，只要检测是否是环路即可，存在环路则表示存在死锁。一般检测到死锁的情况后，对持有较少的行锁的事务进行回滚即可。因此死锁检测是一个 O(n) 复杂度的操作。

考虑这样一种场景，如果有大量线程同时要更新一行数据，则对每个线程做死锁检测时，总时间复杂度就是 O(n²)。即 1000 个线程并发时，死锁检测操作是百万级别的。

1. 从业务上保证不会产生死锁，关闭死锁检测即可
2. 控制每个客户端的并发数上限，如果客户端很多的话作用不大。
3. 从业务上分散资源，如对一个频繁需要被转账的账户做优化，可以设置多个账户，随机分配并发请求即可。