乐趣区

关于java:如何搞定MySQL锁全局锁表级锁行级锁这篇文章告诉你答案太TMD详细了

jiezi

概述

锁是计算机协调多个过程或线程并发拜访某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发拜访的一致性、有效性是所有数据库必须解决的一个问题，锁抵触也是影响数据库并发拜访性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加简单。

MySQL 中的锁，依照锁的粒度分，分为以下三类：

  • 全局锁：锁定数据库中的所有表。
  • 表级锁：每次操作锁住整张表。
  • 行级锁：每次操作锁住对应的行数据。

全局锁

介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 DML 的写语句，DDL 语句，曾经更新操作的事务提交语句都将被阻塞。

其典型的应用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

为什么全库逻辑备份，就须要加全就锁呢？

A. 咱们一起先来剖析一下不加全局锁，可能存在的问题。

假如在数据库中存在这样三张表: tb_stock 库存表，tb_order 订单表，tb_orderlog 订单日志表。

  • 在进行数据备份时，先备份了 tb_stock 库存表。
  • 而后接下来，在业务零碎中，执行了下单操作，扣减库存，生成订单（更新 tb_stock 表，插入 tb_order 表）。
  • 而后再执行备份 tb_order 表的逻辑。
  • 业务中执行插入订单日志操作。
  • 最初，又备份了 tb_orderlog 表。

此时备份进去的数据，是存在问题的。因为备份进去的数据，tb_stock 表与 tb_order 表的数据不统一(有最新操作的订单信息, 然而库存数没减)。

那如何来躲避这种问题呢? 此时就能够借助于 MySQL 的全局锁来解决。

B. 再来剖析一下加了全局锁后的状况

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其余的 DDL、DML 全副都处于阻塞状态，然而能够执行 DQL 语句，也就是处于只读状态，而数据备份就是查问操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保障了数据的一致性和完整性。

语法

  1. 加全局锁
flush tables with read lock;
  1. 数据备份
mysqldump -uroot –p1234 itcast > itcast.sql
  1. 开释锁
unlock tables;

特点

数据库中加全局锁，是一个比拟重的操作，存在以下问题：

  • 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
  • 如果在从库上备份，那么在备份期间从库不能执行主库同步过去的二进制日志（binlog），会导致主从提早。

在 InnoDB 引擎中，咱们能够在备份时加上参数 –single-transaction 参数来实现不加锁的一致性数据备份。

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

表级锁

介绍

表级锁，每次操作锁住整张表。锁定粒度大，产生锁抵触的概率最高，并发度最低。利用在 MyISAM、InnoDB、BDB 等存储引擎中。

对于表级锁，次要分为以下三类：

  • 表锁
  • 元数据锁（meta data lock，MDL）
  • 意向锁

表锁

对于表锁，分为两类：

  • 表共享读锁（read lock）
  • 表独占写锁（write lock）

语法：

  • 加锁：lock tables 表名 … read/write。
  • 开释锁：unlock tables / 客户端断开连接。

特点:

A. 读锁

左侧为客户端一，对指定表加了读锁，不会影响右侧客户端二的读，然而会阻塞右侧客户端的写。

测试:

B. 写锁

左侧为客户端一，对指定表加了写锁，会阻塞右侧客户端的读和写。

测试:

论断

读锁不会阻塞其余客户端的读，然而会阻塞写。写锁既会阻塞其余客户端的读，又会阻塞其余客户端的写。

元数据锁

meta data lock , 元数据锁，简写 MDL。

MDL 加锁过程是零碎自动控制，无需显式应用，在拜访一张表的时候会主动加上。MDL 锁次要作用是保护表元数据的数据一致性，在表上有流动事务的时候，不能够对元数据进行写入操作。为了防止 DML 与 DDL 抵触，保障读写的正确性。

这里的元数据，大家能够简略了解为就是一张表的表构造。也就是说，某一张表波及到未提交的事务时，是不可能批改这张表的表构造的。

在 MySQL5.5 中引入了 MDL，当对一张表进行增删改查的时候，加 MDL 读锁(共享)；当对表构造进行变更操作的时候，加 MDL 写锁(排他)。

常见的 SQL 操作时，所增加的元数据锁：

对应 SQL 锁类型 阐明
lock tables xxx read/write SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select、select … lock in share mode SHARED_READ 与 SHARED_READ、SHARED_WRITE 兼容，与 EXCLUSIVE 互斥
insert、update、delete、select … for update SHARED_WRITE 与 SHARED_READ、SHARED_WRITE 兼容，与 EXCLUSIVE 互斥
alter table … EXCLUSIVE 与其余的 MDL 都互斥

演示：

当执行 SELECT、INSERT、UPDATE、DELETE 等语句时，增加的是元数据共享锁（SHARED_READ / SHARED_WRITE），之间是兼容的。

当执行 SELECT 语句时，增加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的。

咱们能够通过上面的 SQL，来查看数据库中的元数据锁的状况：

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

咱们在操作过程中，能够通过上述的 SQL 语句，来查看元数据锁的加锁状况。

mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+----------------+--------------+---------------+
| object_type | object_schema      | object_name    | lock_type    | lock_duration |
+-------------+--------------------+----------------+--------------+---------------+
| TABLE       | MySQL_Advanced     | tb_user        | SHARED_READ  | TRANSACTION   |
| TABLE       | MySQL_Advanced     | tb_user        | SHARED_READ  | TRANSACTION   |
| TABLE       | MySQL_Advanced     | tb_user        | SHARED_WRITE | TRANSACTION   |
| TABLE       | MySQL_Advanced     | user_logs      | SHARED_WRITE | TRANSACTION   |
| TABLE       | performance_schema | metadata_locks | SHARED_READ  | TRANSACTION   |
+-------------+--------------------+----------------+--------------+---------------+
5 rows in set (0.00 sec)
mysql> alter table tb_user add column java int;
... 阻塞
-- 另开一个客户端窗口
mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+------------------------+---------------------+---------------+
| object_type | object_schema      | object_name            | lock_type           | lock_duration |
+-------------+--------------------+------------------------+---------------------+---------------+
| TABLE       | MySQL_Advanced     | tb_user                | SHARED_READ         | TRANSACTION   |
| GLOBAL      | NULL               | NULL                   | INTENTION_EXCLUSIVE | STATEMENT     |
| BACKUP LOCK | NULL               | NULL                   | INTENTION_EXCLUSIVE | TRANSACTION   |
| SCHEMA      | MySQL_Advanced     | NULL                   | INTENTION_EXCLUSIVE | TRANSACTION   |
| TABLE       | MySQL_Advanced     | tb_user                | SHARED_UPGRADABLE   | TRANSACTION   |
| TABLESPACE  | NULL               | MySQL_Advanced/tb_user | INTENTION_EXCLUSIVE | TRANSACTION   |
| TRIGGER     | MySQL_Advanced     | tb_user_insert_trigger | EXCLUSIVE           | TRANSACTION   |
| TRIGGER     | MySQL_Advanced     | tb_user_update_trigger | EXCLUSIVE           | TRANSACTION   |
| TRIGGER     | MySQL_Advanced     | tb_user_delete_trigger | EXCLUSIVE           | TRANSACTION   |
| TABLE       | MySQL_Advanced     | #sql-261d_18           | EXCLUSIVE           | STATEMENT     |
| TABLE       | MySQL_Advanced     | tb_user                | EXCLUSIVE           | TRANSACTION   |
| TABLE       | performance_schema | metadata_locks         | SHARED_READ         | TRANSACTION   |
+-------------+--------------------+------------------------+---------------------+---------------+
12 rows in set (0.00 sec)

意向锁

  1. 介绍

为了防止 DML 在执行时，加的行锁与表锁的抵触，在 InnoDB 中引入了意向锁，使得表锁不必查看每行数据是否加锁，应用意向锁来缩小表锁的查看。

如果没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简略剖析一下：

首先客户端一，开启一个事务，而后执行 DML 操作，在执行 DML 语句时，会对波及到的行加行锁。

当客户端二，想对这张表加表锁时，会查看以后表是否有对应的行锁，如果没有，则增加表锁，此时就会从第一行数据，查看到最初一行数据，效率较低。

有了意向锁之后 :

客户端一，在执行 DML 操作时，会对波及的行加行锁，同时也会对该表加上意向锁。

而其余客户端，在对这张表加表锁的时候，会依据该表上所加的意向锁来断定是否能够胜利加表锁，而不必逐行判断行锁状况了。

  1. 分类
  • 动向共享锁 (IS): 由语句 select … lock in share mode 增加。 与表锁共享锁 (read) 兼容，与表锁排他锁 (write) 互斥。
  • 动向排他锁 (IX): 由 insert、update、delete、select…for update 增加 。与表锁共享锁 (read) 及排他锁 (write) 都互斥，意向锁之间不会互斥。

一旦事务提交了，动向共享锁、动向排他锁，都会主动开释。

能够通过以下 SQL，查看意向锁及行锁的加锁状况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

演示：

A. 动向共享锁与表读锁是兼容的

B. 动向排他锁与表读锁、写锁都是互斥的

行级锁

介绍

行级锁，每次操作锁住对应的行数据。锁定粒度最小，产生锁抵触的概率最低，并发度最高。利用在 InnoDB 存储引擎中。

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，次要分为以下三类：

  • 行锁（Record Lock）：锁定单个行记录的锁，避免其余事务对此行进行 update 和 delete。在 RC、RR 隔离级别下都反对。
  • 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，避免其余事务在这个间隙进行 insert，产生幻读。在 RR 隔离级别下都反对。
  • 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据后面的间隙 Gap。在 RR 隔离级别下反对。

行锁

  1. 介绍

InnoDB 实现了以下两种类型的行锁：

  • 共享锁（S）：容许一个事务去读一行，阻止其余事务取得雷同数据集的排它锁。
  • 排他锁（X）：容许获取排他锁的事务更新数据，阻止其余事务取得雷同数据集的共享锁和排他 锁。

两种行锁的兼容状况如下:

常见的 SQL 语句，在执行时，所加的行锁如下：

SQL 行锁类型 阐明
INSERT … 排他锁 主动加锁
UPDATE … 排他锁 主动加锁
DELETE … 排他锁 主动加锁
SELECT（失常） 不加任何锁
SELECT … LOCK IN SHARE MODE 共享锁 须要手动在 SELECT 之后加 LOCK IN SHARE MODE
SELECT … FOR UPDATE 排他锁 须要手动在 SELECT 之后加 FOR UPDATE
  1. 演示

默认状况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 应用 next-key 锁进行搜寻和索引扫描，以避免幻读。

  • 针对惟一索引进行检索时，对已存在的记录进行等值匹配时，将会主动优化为行锁。
  • InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时 就会降级为表锁。

能够通过以下 SQL，查看意向锁及行锁的加锁状况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

示例演示

数据筹备:

CREATE TABLE `stu` (
    `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `age` int NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
INSERT INTO `stu` VALUES (1, 'tom', 1);
INSERT INTO `stu` VALUES (3, 'cat', 3);
INSERT INTO `stu` VALUES (8, 'rose', 8);
INSERT INTO `stu` VALUES (11, 'jetty', 11);
INSERT INTO `stu` VALUES (19, 'lily', 19);
INSERT INTO `stu` VALUES (25, 'luci', 25);

演示行锁的时候，咱们就通过下面这张表来演示一下。

A. 一般的 select 语句，执行时，不会加锁。

B. select…lock in share mode，加共享锁，共享锁与共享锁之间兼容。

共享锁与排他锁之间互斥。

客户端一获取的是 id 为 1 这行的共享锁，客户端二是能够获取 id 为 3 这行的排它锁的，因为不是同一行数据。而如果客户端二想获取 id 为 1 这行的排他锁，会处于阻塞状态，认为共享锁与排他锁之间互斥。

C. 排它锁与排他锁之间互斥

当客户端一，执行 update 语句，会为 id 为 1 的记录加排他锁；客户端二，如果也执行 update 语句更新 id 为 1 的数据，也要为 id 为 1 的数据加排他锁，然而客户端二会处于阻塞状态，因为排他锁之间是互斥的。直到客户端一，把事务提交了，才会把这一行的行锁开释，此时客户端二，解除阻塞。

D. 无索引行锁降级为表锁

stu 表中数据如下:

mysql> select * from stu;
+----+-----+-------+
| id | age | name  |
+----+-----+-------+
|  1 |   1 | Java  |
|  3 |   3 | Java  |
|  8 |   8 | rose  |
| 11 |  11 | jetty |
| 19 |  19 | lily  |
| 25 |  25 | luci  |
+----+-----+-------+
6 rows in set (0.00 sec)

在两个客户端中执行如下操作:

在客户端一中，开启事务，并执行 update 语句，更新 name 为 Lily 的数据，也就是 id 为 19 的记录。而后在客户端二中更新 id 为 3 的记录，却不能间接执行，会处于阻塞状态，为什么呢？

起因就是因为此时，客户端一，依据 name 字段进行更新时，name 字段是没有索引的，如果没有索引，此时行锁会降级为表锁(因为行锁是对索引项加的锁，而 name 没有索引)。

接下来，咱们再针对 name 字段建设索引，索引建设之后，再次做一个测试：

此时咱们能够看到，客户端一，开启事务，而后仍然是依据 name 进行更新。而客户端二，在更新 id 为 3 的数据时，更新胜利，并未进入阻塞状态。这样就阐明，咱们依据索引字段进行更新操作，就能够防止行锁降级为表锁的状况。

间隙锁 & 临键锁

默认状况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 应用 next-key 锁进行搜寻和索引扫描，以避免幻读。

  • 索引上的等值查问(惟一索引)，给不存在的记录加锁时, 优化为间隙锁。
  • 索引上的等值查问(非惟一一般索引)，向右遍历时最初一个值不满足查问需要时，next-key lock 进化为间隙锁。
  • 索引上的范畴查问(惟一索引)– 会拜访到不满足条件的第一个值为止。

留神:

间隙锁惟一目标是避免其余事务插入间隙。间隙锁能够共存，一个事务采纳的间隙锁不会阻止另一个事务在同一间隙上采纳间隙锁。

示例演示

A. 索引上的等值查问 (惟一索引)，给不存在的记录加锁时, 优化为 间隙锁。

B. 索引上的等值查问(非惟一一般索引)，向右遍历时最初一个值不满足查问需要时，next-key lock 进化为间隙锁。

介绍剖析一下：

咱们晓得 InnoDB 的 B + 树索引，叶子节点是有序的双向链表。如果，咱们要依据这个二级索引查问值为 18 的数据，并加上共享锁，咱们是只锁定 18 这一行就能够了吗？并不是，因为是非惟一索引，这个构造中可能有多个 18 的存在，所以，在加锁时会持续往后找，找到一个不满足条件的值（以后案例中也就是 29）。此时会对 18 加临键锁，并对 29 之前的间隙加锁。

C. 索引上的范畴查问(惟一索引)– 会拜访到不满足条件的第一个值为止。

查问的条件为 id>=19，并增加共享锁。此时咱们能够依据数据库表中现有的数据，将数据分为三个局部：

[19]

(19,25]

(25,+∞]

所以数据库数据在加锁是，就是将 19 加了行锁，25 的临键锁（蕴含 25 及 25 之前的间隙），正无穷的临键锁(正无穷及之前的间隙)。

本文由 传智教育博学谷狂野架构师 教研团队公布。

如果本文对您有帮忙，欢送 关注 和点赞 ；如果您有任何倡议也可 留言评论 或私信，您的反对是我保持创作的能源。

转载请注明出处！

退出移动版