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

为什么要有锁?

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

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

全局锁

什么是全局锁?

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

使用全局锁的场景

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

为什么要使用全局锁?

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

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

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

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

  1. 如果在时间顺序上先备份用户课程表(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
begin;
UPDATE t SET k=k+1 where id=1;
UPDATE t SET k=k+1 where id=2; begin;
UPDATE t SET k=k+2 where id=1;
commit;

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

两阶段锁在事务上的帮助

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

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

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

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

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

死锁的检查

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

事务 A 事务 B
begin;
UPDATE t SET k=k+1 where id=1;
begin;
UPDATE t SET k=k+1 where id=2;
UPDATE t SET k=k+1 where id=2;
UPDATE t SET k=k+1 where id=1;

如上图中,事务 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 读写锁互斥,造成数据库挂掉的情况。

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

参考:

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

版权声明:
作者:Joe.Ye
链接:https://www.appblog.cn/index.php/2023/04/01/database-concurrency-processing-introduction-to-mysql-locks/
来源:APP全栈技术分享
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
打赏
海报
数据库并发处理 – MySQL锁简介
为什么要有锁? 我们都是知道,数据库中锁的设计是解决多用户同时访问共享资源时的并发问题。在访问共享资源时,锁定义了用户访问的规则。根据加锁的范围,MyS……
<<上一篇
下一篇>>
文章目录
关闭
目 录