MySQL中的锁（一）

最新推荐文章于 2024-04-18 15:21:50 发布

小黑测试员

最新推荐文章于 2024-04-18 15:21:50 发布

阅读量926

点赞数 9

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/u012002125/article/details/136867459

版权

MySQL 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

MySQL中的锁是用于协调多个会话对共享资源的并发访问时，以保证数据的一致性和完整性。

一、MySQL锁的分类

按操作类型可以分为共享锁（即S锁，也叫读锁）、排他锁（即X锁，也叫写锁）

共享锁

允许多个事务同时获取锁并读取同一份数据。共享锁之间是兼容的，在进行读取操作时不会阻塞其他事务。即一个事务对数据进行读取时，其他事务也可以对该数据加共享锁，但不能加排他锁。它主要是为了支持数据的并发读取，避免重复读问题。

排他锁

只允许一个事务获取并持有该锁。排他锁可以防止并发修改操作引起的数据冲突问题，保证了数据在修改时的一致性和完整性。确保只有一个事务可以对数据进行写操作，其他读写操作都会被阻塞直到该锁被释放。

按锁的粒度可以分为全局锁、表级锁、行级锁

全局锁

涉及整个数据库实例，将其锁定为只读状态，适用于全库备份等场景，以确保数据的一致性。

表级锁

锁定整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低。包括普通的表锁和MDL（元数据锁）。表级锁适用于MyISAM这样的存储引擎。

行级锁

仅针对影响的行进行锁定，锁定粒度最小，发生锁冲突的概率最低，并发度最高。分为读锁和写锁，InnoDB存储引擎支持行级锁。

行锁的实现算法有：

记录锁（Record Lock）：记录锁通过封锁特定的索引记录来防止其他事务对这些行进行插入、更新或删除操作。

锁定索引记录之间的间隙，防止其他事务在间隙内插入数据，以避免幻读现象。

间隙锁（Gap Lock）：锁定索引记录之间的间隙，防止其他事务在间隙内插入数据，以避免幻读现象。

临键锁（Next-Key Lock）：是行锁和间隙锁的结合，既锁定索引记录也锁定其前面的间隙。

二、全局锁

全局锁就是对整个数据库实例加锁，在MySQL中给全局加锁命令是 Flush tables with read lock ，执行后整个数据库就处于只读状态，表数据的更新、删除、新增语句将被阻塞。

全局锁的使用场景

mysql> select * from user;
+----+------+------+------+---------------------+
| id | name | age  | sex  | create_time         |
+----+------+------+------+---------------------+
|  1 |      |   25 |      | 2024-03-14 21:15:09 |
|  2 |      |   30 | 女    | 2024-03-14 21:15:09 |
|  3 |      |   28 |      | 2024-03-14 21:15:09 |
|  4 |      |   22 | 女    | 2024-03-14 21:15:09 |
|  5 |      |   35 |      | 2024-03-14 21:15:09 |
|  6 | 馨    |   29 | 女    | 2024-03-14 21:15:09 |
|  7 |      |   31 |      | 2024-03-14 21:15:09 |
|  8 | 郑十   |   27 | 女    | 2024-03-14 21:15:09 |
|  9 | 十一   |   33 |      | 2024-03-14 21:15:09 |
| 10 | 十    |   26 | 女    | 2024-03-14 21:15:09 |
+----+------+------+------+---------------------+
10 rows in set (0.00 sec)

mysql> Flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set name ='xiaoming' where id=1;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql>

1、典型的使用场景是在数据库做全库逻辑备份时使用，避免备份过程中发生数据更新导致备份前后数据不一致

2、数据库迁移和复制过程中，加全局锁确保源数据在复制和迁移过程中数据的一致性。

3、数据库结构变更，如增删改索引操作时，全局锁可以防止其他线程对数据库结构做同步并发操作导致数据不一致问题

全局锁的弊端

全局锁是对整个MySQL数据库实例加锁的机制，虽然加全局锁可以保证数据库的一致性和完整性，但全局锁加锁后整个数据库的增删改都会被阻塞导致业务无法正常运行。

三、表级锁

MySQL中表锁表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。共享锁允许其他用户对同一表进行读操作，但会阻塞对同表的写请求；而排他锁则会阻塞其他用户对同一表的读和写操作。

表级锁的使用场景

1、MySQL在执行CREATE TABLE、ALTER TABLE、DROP TABLE等语句时，通常需要使用表级锁来确保在操作期间不会有其他事务对表结构进行修改，从而避免数据不一致的问题。

2、数据表更新不频繁的情况下，使用表级锁可以简化锁定机制，减少系统开销。例如，对于只读或者几乎不进行写操作的表，使用表级读锁可以允许多个事务同时读取，但在写操作时会阻塞其他所有操作，这在数据变更不频繁的情况下是可接受的。
表级锁的弊端

表级锁在对数据更新不频繁情况使用，如果表中数据会经常被增删改操作使用表锁会导致较大的并发性能瓶颈，因为表锁会阻止其他所有事务访问被锁定的表

表加读锁（共享锁）如下：

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> lock tables user read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=10;
+----+------+------+------+---------------------+
| id | name | age  | sex  | create_time         |
+----+------+------+------+---------------------+
| 10 | 十    |   26 | 女    | 2024-03-14 21:15:09 |
+----+------+------+------+---------------------+
1 row in set (0.00 sec)

mysql> update user set name ='xiaohua' where id =10;
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
mysql>

表加写锁（排他锁）如下：

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set name ='xiaohua' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+---------+------+------+---------------------+
| id | name    | age  | sex  | create_time         |
+----+---------+------+------+---------------------+
|  1 | xiaohua |   25 |      | 2024-03-14 21:15:09 |
|  2 |         |   30 | 女    | 2024-03-14 21:15:09 |
|  3 |         |   28 |      | 2024-03-14 21:15:09 |
|  4 |         |   22 | 女    | 2024-03-14 21:15:09 |
|  5 |         |   35 |      | 2024-03-14 21:15:09 |
|  6 | 馨       |   29 | 女    | 2024-03-14 21:15:09 |
|  7 |         |   31 |      | 2024-03-14 21:15:09 |
|  8 | 郑十      |   27 | 女    | 2024-03-14 21:15:09 |
|  9 | 十一      |   33 |      | 2024-03-14 21:15:09 |
| 10 | 十       |   26 | 女    | 2024-03-14 21:15:09 |
+----+---------+------+------+---------------------+
10 rows in set (0.00 sec)  

LOCK TABLES user WRITE;
Query OK, 0 rows affected (0.00 sec)

# 其他线程执行以下操作将被阻塞
mysql> select *from user;

释放锁：