MySQL 数据库中的锁

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。

 

1. FTWRL

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

 

全局锁的典型使用场景是，做全库逻辑备份也就是把整库每个表都 select 出来存成文本。

但是备份的过程全库处于只读状态。所以要配合 可重复读 事务隔离级别来使用。

 

2. single-transaction + 事务支持

需要全表支持事务隔离的存储引擎

 

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

 

你一定在疑惑，有了这个功能，为什么还需要 FTWRL 呢？一致性读是好，但前提是引擎要支持这个隔离级别。比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。所以，single-transaction 方法只适用于所有的表使用事务引擎的库。

 

如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

 

所以推荐使用：single-transaction 的方式来进行逻辑备份，也就是官方自带的 mysqldump 工具。但是这种办法需要存储引擎的事务隔离支持。MyISAM 存储引擎不支持事务，没办法使用，只能使用 FTWRL 命令。

 

这也是使用 InnoDB 替换 MyISAM 的原因之一（不支持事务），还有之前说的 crash-safe 能力。算上这个，已经有两个是 InnoDB 替代到 MyISAM 成为默认存储引擎的原因了。

 

3. set global readonly=true

这种方式考虑到有可能有业务场景用到做逻辑判断，改动影响较大，同时不支持异常处理机制不建议使用。

• 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。
• 二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

 

表级锁

 

1. 表锁

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

表锁：lock table xxx read|write

这种方式锁的是操作，即如果执行了 locak table read ，那么当前线程只能对表执行 read 操作，同时其他线程不能访问该表，同样当前线程也不可以访问其他数据表。

 

2. MDL 锁

另一类表级的锁是 MDL（metadata lock)，这个是 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

 

DML 和 DDL 语句之间的互斥锁。

 

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

 

表结构变更 DDL 语句的执行思考

MDL 锁引申要点，DML 与 DDL 之间互斥的关系。当对一个表结构进行变更的时候，此时是无法执行 DML 语句的，也就是增删改查无法执行，同时 MDL 锁的进入时间是在会话执行的那一刻开始，也就是说，如果此时有两个耗时 DML 语句先执行，接着执行了一个 DDL 语句，那么此刻（DDL语句执行）之后的所有 DML 语句会被阻塞（无法进行增删改查），必须等待 DDL 语句执行完。

 

如何解决这种问题

在 DDL 语句设置 MDL 锁的超时时间，超过则放弃，后面在进行重试，如此往复。

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

 

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

 

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

 

行级锁

InnoDB 存储引擎支持的一种更细粒度的锁级别

 

两阶段锁协议

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

 

在事务真正开启

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

的时候加锁，在事务结束（提交或回滚或断开连接）的时候释放。

 

 

死锁和死锁检测

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

 

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

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

 

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s。时间虽然可以配置，但是大部分情况下无法满足业务场景需要，太小，可能误杀正常锁等待情况，太大。真正的死锁发现太晚，导致不必要的损失。所以会使用第二个策略，死锁检测。

 

但是死锁检测有一个问题，他的检测时间复杂度是 O(n2)，即 1000 个连接进行一行记录更新时，死锁检测次数为 100万 次。这大大降低了数据库的执行性能。

 

怎么减少行锁对性能的影响？

1. 关闭死锁检测
2. 控制并发度，业务层面和数据库层面。业务控制在分布式场景下仍然难以控制，假如有20个服务，每个服务10个连接，一样有 200 个连接过来，也要进行 4w 次检测。另一种就是修改数据库 server 层的源码，在服务端控制并发数量。
3. 拆分更新记录，将一条记录分成多条记录的合计。如果这种方式需考虑数值变更的临界值判断，比如金额为 0 的情况。