侧边栏壁纸
  • 累计撰写 244 篇文章
  • 累计创建 16 个标签
  • 累计收到 0 条评论
隐藏侧边栏

MySQL 中的全局锁、表锁与行锁(共享锁、排它锁、意向锁、死锁)

kaixindeken
2021-07-27 / 0 评论 / 0 点赞 / 72 阅读 / 3,737 字

根据加锁的范围,MySQL 里面的锁可以分为全局锁、表锁和行锁。

全局锁

全局锁就是对整个数据库实例加锁,比如你可以通过如下命令:

flush tables with read lock;

为 MySQL 数据库设置全局读锁,这样一来,整个数据库就会处于只读状态。聪明如你,应该可以猜到全局读锁可能的适用场景了:当我们做数据库备份时,可以使用该锁让全库处于只读状态。

mysqldump 原理

我们知道,MySQL 官方提供了一个备份工具 —— mysqldump,当运行 mysqldump 命令不带任何额外参数时,默认会使用 --lock-tables=on,不会加全局读锁,但是会为每张表加表锁。

如果带上 --master-data/--lock-all-tables 参数时,就会给数据库加上全局读锁,此时不能对数据库进行 DDL 或者 DML 等写入操作。

而如果带上了 --single-transaction 参数,则会在备份数据之前先开启一个事务(默认是可重复读级别),从而确保在整个备份过程中使用的是第一条 SELECT 语句运行前生成的视图(ReadView),进而保证数据一致性。在可重复读的隔离级别下,多个读写事务是可以并发执行的,因此这种机制不会加全局读锁,而是通过 MVCC 机制确保数据一致性的同时不影响数据库的更新操作,这样做的好处是备份期间不会造成线上业务的停摆。当然,--single-transaction 参数仅适用于支持事务的表。

表锁

MySQL 里面有两种表级别的锁,一种是表锁,一种是元数据锁(meta data lock,简称 MDL)。

表锁

我们可以使用如下命令为指定数据表加表锁:

LOCK TABLES [表名] READ/WRITE;

其中 READ 表示加读锁,WRITE 表示加写锁,加读锁的表只能执行读操作,不能执行写操作,即阻塞所有线程的写操作,加写锁的表只能执行写操作,不能执行读操作,即阻塞其他线程的读写操作,这里的其他线程指的是执行加表锁操作的线程以外的、其他试图访问该表的线程,可以看到 MySQL 的表锁不仅会阻塞其他线程,还会阻塞当前线程。

上面介绍的 mysqldump 命令不加任何参数时生成的备份文件默认就会给所有表分别加写锁,要释放表锁,使用 UNLOCK TABLES 命令即可(客户端连接断开时也会自动释放):

LOCK TABLES `wallets` WRITE;
/*!40000 ALTER TABLE `wallets` DISABLE KEYS */;
INSERT INTO `wallets` VALUES (1,'小明',47000,'2020-10-15 15:21:40','2020-10-15 15:21:40'),(2,'小强',27000,'2020-10-15 15:22:19','2020-10-15 15:22:19');
/*!40000 ALTER TABLE `wallets` ENABLE KEYS */;
UNLOCK TABLES;

我们也可以同时给多张表加锁:

LOCK TABLES t1 READ, t2 WRITE;

元数据锁

元数据锁(MDL)是 MySQL 5.5 之后引入的,其作用是保证读写操作的正确执行:

  • 当对数据表进行 DML 操作(增删改查)时,加 MDL 读锁,阻塞其他线程对这张表进行 DDL 操作;
  • 当对数据表结构进行变更(DDL 操作)时,加 MDL 写锁,阻塞其他线程对这张表进行 DML 操作;

元数据锁会自动添加,无需显式设置。

行锁

MySQL 的行锁是由各个存储引擎自行设计和实现的,因为不是所有引擎都支持行锁,比如 MyISAM 引擎就不支持,而 InnoDB 引擎支持,这里我们主要以 InnoDB 引擎为例进行介绍。

共享锁和排它锁

InnoDB 引擎实现了两种标准的行级锁:

  • 共享行级锁(Share Lock,简称 S 锁)
  • 排他行级锁(Exclusive Lock,简称 X 锁)

S 锁允许并发读取数据,阻塞并发写数据,X 锁既阻塞并发读数据,也阻塞并发写数据,所以可以把 S 锁看作读锁,把 X 锁看作写锁。

因为读操作不会对数据库数据进行修改,所以并发执行不会有任何副作用,因此并发读取操作是允许的,但是读-写操作和写-写操作是不同同时进行的,否则会造成脏读、脏写、不可重复读和幻读等并发问题。

所以 S 锁和 X 锁之间的兼容性如下所示:

-XS
X不兼容不兼容
S不兼容兼容

即某行记录上加了 X 锁,其他线程就不能再为该行添加 X 锁和 S 锁了,相应的线程会阻塞,直到这行记录的 X 锁被释放;而如果某行记录加了 S 锁,则其他线程针对改行的写操作会阻塞,但读操作不会。

注:实际上,MySQL 数据库不同事务对同一行记录的并发读取操作是通过 MVCC 来实现的,不需要加锁,性能更好。

要为某行记录显式添加 S 锁,可以通过在普通 SQL 语句之后加上 LOCK IN SHARE MODE 实现:

SELECT ... LOCK IN SHARE MODE;

这样一来,查询结果集中的所有行就会被添加 S 锁,其他事务就不能在这些行上设置 X 锁进行写入操作,直到当前事务提交将 S 锁释放。

要为某行记录显式添加 X 锁,可以通过在普通 SQL 语句之后加上 FOR UPDATE 实现:

SELECT ... FOR UPDATE;

这样一来,查询结果集中的所有行就会被添加 X 锁,其他事务就既不能对这些行进行读取操作,也不能对这些行进行写入操作,直到当前事务提交将 X 锁释放。

意向锁

除了行锁外,InnoDB 引擎还支持表锁,为了更好地支持表锁与行锁这两个不同粒度的锁,InnoDB 还支持一种额外的上锁方式 —— 意向锁(Intention Lock)。

根据读写这两种不同操作,意向锁也分为意向共享锁(Intention Share Lock,简称 IS 锁)和意向排他锁(Intention Exclusive Lock,简称 IX 锁)。

意向锁将锁定的对象(数据库、表、页、行)分为多个粒度,当想要对细粒度的数据进行加锁时,那么首先需要对粗粒度的对象添加意向锁:

1.jpeg

如果事务 A 需要对表 A 某个数据页上的某行记录添加 X 锁,则需要分别对相应数据库、表、页添加意向排他锁 IX,添加成功后才会对这行记录添加 X 锁。此时事务 B 想要向表 A 中添加表级别的排他锁,由于表 A 中已经存在 IX 锁,与表级别的 X 锁并不兼容,故事务 B 等待,直到表 A 中 IX 锁的释放。

综上,我们可以给出所有不同锁之间的兼容关系:

-IXISX(表级别)S(表级别)
IX兼容兼容不兼容不兼容
IS兼容兼容不兼容兼容
X(表级别)不兼容不兼容不兼容不兼容
S(表级别)不兼容兼容不兼容兼容

两阶段锁协议

我们以上篇教程介绍版本链时使用的两个事务为例:

1.jpeg

在事务 A 和事务 B 中存在对同一条记录的更新操作,两个事务对同一条表记录的写操作是不可能同时执行的,此外事务 B 的写操作也不能出现在事务 A 对该记录的写操作事务提交之前,否则就可能出现脏写这种严重的问题,InnoDB 底层正是通过行锁机制来保证不同事务的写操作并发安全的。

在 InnoDB 底层会在需要的时候添加行锁,比如这里事务 A 开始执行 UPDATE 操作之前给 id=1 这条记录添加 X 锁,然后在事务提交之后释放这个 X 锁,接着事务 B 才能对这条记录加 X 锁,执行更新操作,阻塞其它线程访问这条记录。

为什么要到事务提交后才释放行锁呢,这很好理解 —— 为了避免脏写,因为如果事务 A 未提交就释放了 X 锁,事务 B 拿到这个 X 锁进行更新操作,并在事务 A 提交前成功提交,而事务 A 后续执行出现问题回滚这条记录的所有更新,就出现了脏写了。

我们把 InnoDB 事务中这种按需添加行锁,最后所有行锁在事务提交时释放的机制,叫做两阶段锁协议。

死锁及解决方案

上面是针对同一条记录的更新,如果是两个事务出现相互依赖,比如事务 A 需要等待事务 B 释放行锁才能继续往下操作,同时事务 B 需要等待事务 A 释放行锁才能继续往下操作,就可能出现死锁,比如下面这种情形:

SQL 语句执行序列事务 A事务 B
1BEGIN;
2UPDATE walltes SET balance = balance + 2000 WHERE id = 1;BEGIN;
3 UPDATE walltes SET balance = balance + 2000 WHERE id = 2;
4UPDATE walltes SET balance = balance + 2000 WHERE id = 2;
5UPDATE walltes SET balance = balance + 2000 WHERE id = 1;

事务 B 在等待事务 A 释放 id=1 的行锁,事务 A 在等待事务 B 释放 id=2 的行锁,而我们知道根据两阶段锁协议,行锁需要在事务提交后才能释放,因此这两个事务陷入相互依赖的死循环,进而出现死锁。

出现死锁后,通常有两种解决方案:

  • 配置超时时间:MySQL 提供了一个 innodb_lock_wait_timeout 配置项(默认是 50s,这也太长了)用于设置死锁等待的超时时间,超过这个时间后,死锁中第一个被锁住的线程会自动退出,这样其他线程就可以继续执行了;
  • 发起死锁检测:MySQL 提供了一个 innodb_deadlock_detect 配置项用于设置是否开启死锁检测(默认值是 ON,表示开启),开始死锁检测后,系统如果发现存在死锁,会回滚陷入死锁的某个事务,从而让其他事务继续执行下去。

由于默认超时时间太长了,而具体配置成多少合适又很难拿捏(比如存在慢查询),所以通常选择第二种方式处理死锁。

0

评论区