数据库并发处理 – MySQL锁简介

为什么要有锁？

我们都是知道，数据库中锁的设计是解决多用户同时访问共享资源时的并发问题。在访问共享资源时，锁定义了用户访问的规则。根据加锁的范围，MySQL 中的锁可大致分成全局锁，表级锁和行锁三类。在本篇文章中，会依次介绍三种类型的锁。在阅读本篇文章后，应该掌握如下的内容：

1. 为什么要在备份时使用全局锁？
2. 为什么推荐使用 InnoDB 作为引擎进行备份？
3. 设置全局只读的方法
4. 表级锁的两种类型
5. MDL 导致数据库挂掉的问题
6. 如何利用两段锁协议减少锁冲突
7. 如何解决死锁
8. 对于热点表，如何避免死锁检测的损耗？

全局锁

什么是全局锁？

全局锁会让整个库处于只读状态，其他线程语句（DML，DDL，更新事务类）的语句都被会阻塞。

使用全局锁的场景

在做全库逻辑备份时，会把整库进行 select 然后保存成文本。

为什么要使用全局锁？

想象这样一个场景，要备份一个购买系统，其中购买操作涉及到更新账号余额表和用户课程表。

现在进行逻辑备份，在备份过程中，一位用户购买了一门课程，这时需要在余额表扣掉余额，然后在购买的课程中加上一门课。正确的顺序肯定是先进行购买操作，减少余额和增加课程然后在进行备份。但却有可能出现这样的问题：

1. 如果在时间顺序上先备份余额表 (u_account)，然后用户购买(操作两张表)，再备份用户课程表(u_course)？

这时用备份的数据做恢复时，会发现用户没花钱却买了一堂课。原因在于，先备份余额表，说明用户余额不变。之后才进行购买操作，余额表减钱，课程表增加一门课程。接着备份课程表，课程表课程加一。购买操作在已经备份完的余额表后进行。

2. 如果在时间顺序上先备份用户课程表(u_course)，然后用户购买(操作两张表)，再备份余额表 (u_account)？

同样的，如果先备份课程表，课程没有增加，因为没有进行购买操作。之后进行购买操作后，余额表减钱，然后被备份。就出现了，用户花钱却没有购买成功的情况。

也就是说，不加锁的话，备份系统的得到的库不是一个逻辑时间点，这个视图是逻辑不一致。

如何解决视图逻辑不一致的问题？

对于不支持事务的引擎，像 MyISAM，通过使用Flush tables with read lock (FTWRL)命令来开启全局锁。

但使用 FTWRL 存在的问题是：

• 在主库上备份时，备份期间不能执行更新，业务基本暂停
• 在从库上备份时，备份期间从库不能执行主库同步过来的 binlog，导致主从延迟

对于支持事务并且开启一致性视图（可重复读级别）下配合上 MVCC 的功能的引擎（InnoDB），备份就很简单了。

使用官方的mysqldump工具时，加上--single-transaction选项，再导出数据前就会启动一个事务，来确保拿到一致性视图。并且由于 MVCC 的支持，同时可以进行更新操作。

全库只读设置方法的比较

为什么不推荐使用set global readonly=true，要使用FTWRL：

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

2. 在异常处理机制上有差异。

执行FTWRL命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。

将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

什么是表级锁？

表级锁的作用域是对某张表进行加锁，在 MySQL 中表级别的锁有两种，一种是表锁，一种是元数据锁（meta data lock，MDL）。

表锁

与FTWRL类似，可以使用lock tables … read/write来锁定某张表。在释放时，可以使用unlock tables来释放锁或者断开连接时，主动释放。

需要注意的是，这样方式的锁表，不但会限制其他线程的读写，也限定了自己线程的操作对象。

假如，线程 A 执行lock tables t1 read, t2 write;操作。

这时对于表 t1 来说，其他线程只能只读，线程 A 也只能只读，不能写。

对于表 t2 来说，只允许线程 A 读写，其他线程读写都会被阻塞。

元数据锁

与表锁手动加锁不同，元数据锁会自动加上。

为什么要有 MDL？

MDL 保证的就是读写的正确性，比如在查询一个表中的数据时，同时另一个线程改变了表结构，查询的结果和表结构不一致肯定不行。简单来说，MDL 就是解决 DML 和 DDL 之间同时操作的问题。

在 MySQL 5.5 引入了 MDL，在对一个进行 DML 时，会加 MDL 读锁。进行 DDL 时，会加 MDL 写锁。

读锁间不互斥，允许多个线程同时对同一张表进行 DML。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

1. 如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行
2. 如果一个线程要读，另一个线程要写。根据访问表的时间，一个操作进行完之后，另一个才可以进行

MDL 引发的问题？

给表加字段，却导致库挂了？

由于 MDL 是自动加的，并且在给表加字段或者修改字段或者加索引时，需要扫描全表的数据。所以在对大表操作时，要非常小心，以免对线上的服务造成影响。但实际上，操作小表时，也可能出问题。假设 t 是小表。按照下图所示，打开四个 session

假设有一张叫 sync_test 的表：

mysql> desc sync_test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

开启事务1，插入数据。对于事务 1 来说，自动申请了表 sync_test 的 MDL 读锁：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sync_test value (NULL, 'yuwei-c1', 1);
Query OK, 1 row affected (0.00 sec)

mysql>

开启事务2，插入数据。对于事务 2 来说，自动申请了表 sync_test 的 MDL 读锁：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sync_test value (NULL, 'yuwei-c1', 1);
Query OK, 1 row affected (0.00 sec)

mysql>

开启事务3，改变表结构。对于事务 3 来说，会申请表 sync_test 的 MDL 写锁，这时由于读写锁互斥，被阻塞：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table sync_test add z int;
(block)

开启事务 4，插入数据。对于事务 4 来说，会申请 sync_test 的 MDL 读锁，由于之前事务 3 提前申请了写锁，互斥所以被阻塞：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sync_test value (NULL, 'yuwei-dl', 1);
(block)

这时如果在这张表上的查询语句很频繁，而且客户端有重连机制，在超时后会再起一个新 session 请求，这个库的线程就很快会爆满了。

有一点需要注意，不要将 DDL 写在事务中，因为对于 DDL 操作是不支持 rollback 操作，所以在回滚时会出现不一致的情况。原因也可以理解，MVCC 所支持的行级别的数据，并不支持表级别的多版本控制。

如何安全的给表加资源？

通过上面的例子也可以看到，MDL 会直到事务提交才释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。在开启事务后，并没有在短时间内结束，也就是由于所谓的长事务造成的。如果想对某个表进行 DDL 的操作时，可以先查询下是否有长事务的运行（information_schema下的innodb_trx表），可以先 kill 这个事务，然后做 DDL 操作。

但有时 kill 也未必可以，在表被频繁使用时，新的事务可能马上就来了。比较理想的情况，在alter table中设定等待时间，如果在时间内拿到最好，否则就放弃，不要阻塞语句。之后再重复这个操作。

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

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

行级锁

什么是行级锁？

MySQL 的行锁是由引擎层自己实现的，不是所有的引擎都执行行锁，比如在 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能用表锁，这就造成了在同一时刻只有一个更新在执行，就影响到了业务的并发度。InnoDB 支持行锁是让 MyISAM 被取代的重要原因。

行锁就是对数据库表中行记录的锁。比如事务 A，B 同时想要更新一行数据，在更新时一定会按照一定的顺序进行，而不能同时更新。

行锁的目的就是减少像表级别的锁冲突，来提升业务的并发度。

两阶段锁协议

在 InnoDB 的事务中，行锁是在需要的时候在加上，但并不是使用完就释放，而是在事务结束后才释放，这就是两阶段锁协议。

假设有一个表 t，事务 A, B 操作表 t 的过程如下：

在事务 A 的两条语句更新后，事务 B 更新操作会被阻塞。直到事务 A 中执行 commit 操作后才能执行。

两阶段锁在事务上的帮助

由于两阶段锁的特点，在事务结束时才会释放锁，所以需要遵循的一个原则是事务中需要锁多个行时，把有可能造成锁冲突，最可能影响并发度的锁尽量向后放。

比如购买课程的例子，顾客 A 购买培训机构 B 一门课程。涉及到操作：

1. 顾客 A 的余额减少
2. 培训机构 B 所在的余额增加。
3. 插入一条交易信息的操作。

对于第二个操作，当有许多人同时购买时并发度就较高，出现锁冲突的情况也较高。所以将操作 2 放置一个事务的最后就更好。

当有时并发度过大时，我们会发现一种现象 CPU 的使用率接近 100%，但事务执行数量却很少。这就可能出现了死锁。

死锁的检查

当并发系统中不同的线程出现循环的资源依赖，等待别的线程释放资源时，就会让涉及的线程处于一直等待的情况。这就称为死锁。

如上图中，事务 A 对id =1 的所在行，加入了行锁。等待 id=2 的行锁。事务 B 对 id = 2 的行，加入了行锁。等待 id=1 的行锁。事务 A，B 等待对方资源的释放。

如何解决死锁

方式一：设置死锁的等待时间innodb_lock_wait_timeout

还是 sync_test 这张表，模拟简单的锁等待情况，注意这里并不是死锁。开启两个事务 A，B。同时对 id=1 这行进行更新。

事务 A 更新操作：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update sync_test set name="dead_lock_test" where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务 B 更新操作：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update sync_test set name="dead_lock_test2" where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到事务 B 抛出了死锁等待的错误。

设置等待时间的问题：在 InnoDB 中，MySQL 默认的死锁等待时间是 50s。意味着在出现死锁后，被锁住的线程要过 50s 被能退出，这对于在线服务说，等待时间过长。但如果把值设置的过小，如果是像上述例子这样是简单的锁等待呢，并不是死锁怎么办，就会出现误伤的情况。

方式二：发起死锁检测，发现死锁后，主动回滚某个事务，让其他事务继续执行

MySQL 中默认就是打开状态，能够快速发现死锁的情况。

set innodb_deadlock_detect=on

事务 A，B 互相依赖，造成死锁的例子：

开启事务 A:

mysql> begin;
mysql> update sync_test set name="dead_lock_test1" where id = 1;

开启事务 B:

mysql> begin;
mysql> update sync_test set name="dead_lock_test3" where id = 3;

继续操作事务 A:

mysql> update sync_test set name="dead_lock_test3_1" where id = 3;

# 会出现阻塞的情况

继续操作事务 B:

mysql> update sync_test set name="dead_lock_test1_2" where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

此时事务 A 阻塞取消，执行成功。

不过检测死锁也是有额外负担的，每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。如果是所有事务都要更新同一行的场景呢？每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 1000*1000=100 万这个量级的。

所以，对于更新频繁并发量大的表，死锁检测会导致消耗大量的 CPU

如何避免死锁检测的损耗

方法一：如果保证业务一定不会出现死锁，可以临时把死锁检查关掉。

但这样存在一定的风险，因为业务设计时不会把死锁当做严重的问题，出现死锁后回滚后，再重试就没有问题了。但关掉死锁检测后，可能出现大量超时的情况。

方法二：控制并发度。

如果对于并发量能控制，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。具体来说在客户端做并发控制，但对于客户端较多的应用，也无法控制。所以并发控制在数据库服务端，如果有中间件，也可以考虑在中间件中实现。

方法三：降低死锁的概率

将一行统计的结构，拆成多行累计的结构。比如将之前某个教学机构的金额由一行拆成 10 行，总收入就等于这 10 行数据的累计。这样原来锁冲突的概率变为原来的 1/10，也就减少了死锁检测的 CPU 消耗。但在一部分行记录变成0时，代码需要特殊处理。

总结

本篇文章中，依次介绍了全局锁、表级锁和行锁的概念。

对于全局锁来说，使用 InnoDB 引擎 在 RR 级别和 MVCC 的帮助下，可以让其在备份的同时更新数据。

对于表级锁来说，对于更新热点表的表结构时，要注意 MDL 读写锁互斥，造成数据库挂掉的情况。

对于行级锁来说，合理的利用两段锁协议，降低锁的冲突。并要注意死锁发生的情况，采取合适的死锁检测手段。

参考：

• 死锁例子
• DDL-ROLLBACK-FAILED

转载至：https://www.cnblogs.com/michael9/p/12167434.html