【MySQL】锁(黑马课程)_黑马程序员mysql笔记锁-CSDN博客

本文链接：https://blog.csdn.net/Blue_Pepsi_Cola/article/details/140224882

0. 锁的考察点

在这里插入图片描述

1. 概述

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

1. 锁的分类

在这里插入图片描述

1.1 属性分类

InnoDB存储引擎实现了两种标准的行级锁：共享锁(S Lock)和排他锁(X Lock)。

共享锁(S Lock):允许事务读一行数据。
排他锁(X Lock):允许事务删除或更新一行数据。

1.2 粒度分类

按照锁的粒度来分，分为以下三类

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

2. 全局锁

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

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

如果不上锁，边备份，业务还正常执行就会造成数据不一致的问题。
在这里插入图片描述

2.1 全局锁操作

mysql> flush tables with read lock;      // 加全局锁
Query OK, 0 rows affected (0.01 sec)

mysql> insert into migrations values(7,'1231231',1);    // 插入数据的时候
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

mysql> unlock tables;  // 销毁全局锁
Query OK, 0 rows affected (0.00 sec)

mysql> insert into migrations values(7,'1231231',1);  // 可以正常插入了
Query OK, 1 row affected (0.01 sec)

备份数据库(newdb3)的数据

(base) ➜  ~ mysqldump -u root -p  newdb3>/Users/fanzhen/Downloads/newdb3.sql
Enter password:

2.2.1 备份问题

全局锁特点

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

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数-single-transaction参数来完成不加锁的一致性数据备份。

在这里插入图片描述

3. 表级锁

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

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

表锁
元数据锁(meta data lock, MDL)
意向锁

3.1 表锁

对于表锁，分为两类：

表共享读锁(read lock) 简称读锁
表独占写锁(write lock) 简称写锁

3.2 语法

1. 加锁: lock tables 表名 read/write
2. 释放锁：unlock tables; 或者 客户端断开连接

3.3 表共享读锁（读锁）

如下图，当对一张表加读锁，我们可以看到，不同的客户端都可以进行查询操作，但是进行DDL与DML都会阻塞，当进行unlock tables操作时，其他进行DML、DDL操作的客户端获取资源，操作阻塞的SQL语句。

在这里插入图片描述

3.4 表独占写锁（写锁）

提示：排他锁，又称为写锁、独占锁
写锁在当前客户端既可以读也可以写，但是其他客户端既不能写也不能读。

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

mysql> lock tables  migrations  write;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from migrations;
+----+-------------------------------------------------------+-------+
| id | migration                                             | batch |
+----+-------------------------------------------------------+-------+
|  1 | 2014_10_12_000000_create_users_table                  |     1 |
|  2 | 2014_10_12_100000_create_password_resets_table        |     1 |
|  3 | 2019_08_19_000000_create_failed_jobs_table            |     1 |
|  4 | 2019_12_14_000001_create_personal_access_tokens_table |     1 |
|  5 | 2024_03_31_143916_create_posts_table                  |     1 |
|  6 | 2024_04_01_143040_create_blogs_table                  |     1 |
|  7 | dagenihao                                             |     1 |
+----+-------------------------------------------------------+-------+
7 rows in set (0.00 sec)

在这里插入图片描述

3.5 元数据锁(meta data lock, MDL)

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

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

在这里插入图片描述

// 客户端1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from migrations;
+----+-------------------------------------------------------+-------+
| id | migration                                             | batch |
+----+-------------------------------------------------------+-------+
|  1 | 2014_10_12_000000_create_users_table                  |     1 |
|  2 | 2014_10_12_100000_create_password_resets_table        |     1 |
|  3 | 2019_08_19_000000_create_failed_jobs_table            |     1 |
|  4 | 2019_12_14_000001_create_personal_access_tokens_table |     1 |
|  5 | 2024_03_31_143916_create_posts_table                  |     1 |
|  6 | 2024_04_01_143040_create_blogs_table                  |     1 |
|  7 | dagenihao                                             |     1 |
+----+-------------------------------------------------------+-------+
7 rows in set (0.00 sec)
// 当客户端2执行完 update migrations set migration =  'MDL' where id = 7;
mysql> select * from migrations;
+----+-------------------------------------------------------+-------+
| id | migration                                             | batch |
+----+-------------------------------------------------------+-------+
|  1 | 2014_10_12_000000_create_users_table                  |     1 |
|  2 | 2014_10_12_100000_create_password_resets_table        |     1 |
|  3 | 2019_08_19_000000_create_failed_jobs_table            |     1 |
|  4 | 2019_12_14_000001_create_personal_access_tokens_table |     1 |
|  5 | 2024_03_31_143916_create_posts_table                  |     1 |
|  6 | 2024_04_01_143040_create_blogs_table                  |     1 |
|  7 | dagenihao                                             |     1 |
+----+-------------------------------------------------------+-------+
7 rows in set (0.00 sec)
mysql> commit
    -> ;
Query OK, 0 rows affected (0.00 sec)


------------------------第二个例子-----------------------------------
mysql> bagin;
mysql> select * from migrations;
+----+-------------------------------------------------------+-------+
| id | migration                                             | batch |
+----+-------------------------------------------------------+-------+
|  1 | 2014_10_12_000000_create_users_table                  |     1 |
|  2 | 2014_10_12_100000_create_password_resets_table        |     1 |
|  3 | 2019_08_19_000000_create_failed_jobs_table            |     1 |
|  4 | 2019_12_14_000001_create_personal_access_tokens_table |     1 |
|  5 | 2024_03_31_143916_create_posts_table                  |     1 |
|  6 | 2024_04_01_143040_create_blogs_table                  |     1 |
|  7 | MDL                                                   |     1 |
+----+-------------------------------------------------------+-------+
7 rows in set (0.00 sec)

// 客户端2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from migrations;
+----+-------------------------------------------------------+-------+
| id | migration                                             | batch |
+----+-------------------------------------------------------+-------+
|  1 | 2014_10_12_000000_create_users_table                  |     1 |
|  2 | 2014_10_12_100000_create_password_resets_table        |     1 |
|  3 | 2019_08_19_000000_create_failed_jobs_table            |     1 |
|  4 | 2019_12_14_000001_create_personal_access_tokens_table |     1 |
|  5 | 2024_03_31_143916_create_posts_table                  |     1 |
|  6 | 2024_04_01_143040_create_blogs_table                  |     1 |
|  7 | dagenihao                                             |     1 |
+----+-------------------------------------------------------+-------+
7 rows in set (0.00 sec)
mysql>  update migrations set migration =  'MDL' where id = 7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

------------------------第二个例子-----------------------------------
mysql> alter table migrations add column java int; // 不能进行修改会一直阻塞

查看元数据锁

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 |
+-------------------+--------------------+----------------+---------------------+---------------+
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
| TABLE             | performance_schema | metadata_locks | SHARED_READ         | TRANSACTION   |
| SCHEMA            | performance_schema | NULL           | INTENTION_EXCLUSIVE | TRANSACTION   |
+-------------------+--------------------+----------------+---------------------+---------------+
13 rows in set (0.01 sec)

3.6 意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

说明：左侧线程A在执行的时候，先开启事物，然后执行update并会在这一行加上行锁，然后对该表加上意向锁，线程B要对这个表加上表锁，线程B要通过意向锁来决定能不能加表锁。
在这里插入图片描述

3.6.1 意向锁的种类

1.意向共享锁(IS)：由语句select...lock in share mode添加
2.意向排他锁(IX)：由insert、update、delete、select.. for update 添加

3.6.2 兼容关系

意向共享锁(IS)：与表锁共享锁(read)兼容，与表锁排它锁(write)互斥。
意向排他锁(IX)：与表锁共享锁(read)及排它锁(write)都互斥。意向锁之间不会互斥。

可以通过以下SQL查看意向锁及行锁的加锁情况。

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

3.6.3 意向锁测试

// TODO

4. 行级锁

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

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

对于行级锁，主要分为以下三类：

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

// TODO