MySQL数据库锁机制（全面讲解）

热爱编程的申同学

已于 2024-08-26 15:45:56 修改

阅读量663

点赞数 25

文章标签：数据库 mysql

于 2024-08-23 20:00:57 首次发布

本文链接：https://blog.csdn.net/select_myname/article/details/141466568

版权

2.2、元数据锁（meta data lock MDL）

3.3、临键锁（next-key lock）

在这个数据驱动的时代，数据库的重要性不言而喻。尤其是当我们面对成千上万的用户同时操作数据库时，如何保证数据不会乱套，这就得聊聊MySQL的锁机制了。锁，听起来好像有点神秘，但其实它就像是数据库的一道安全门，接下来，我会和大家一起探讨一下MySQL数据库锁的点点滴滴，包括它是怎么工作的，有哪些类型，以及在实际应用中怎么用。你能对数据库锁有个更清晰的了解，以后遇到并发问题也能更从容应对。那么，让我们开始吧！

1、全局锁

MySQL的全局锁是一种用于保护整个数据库实例的锁。它是MySQL中最高级别的锁，也是最强大的锁之一。

全局锁的作用是在备份、恢复或执行某些维护任务时保护数据库的完整性。当全局锁处于激活状态时，其他会话无法对数据库做任何写操作，只能进行读操作。

全局锁的特点包括：

全局性：全局锁会锁定整个数据库实例，而不是单个表或行。
独占性：全局锁是互斥的，一次只能由一个会话持有。
高优先级：全局锁的优先级很高，其他锁无法与之竞争。

全局锁的使用场景包括：

数据库备份：在进行数据库备份时，需要保证备份数据的完整性，可以使用全局锁来锁定整个数据库，确保不会有其他会话对数据库进行写操作。
数据库恢复：在进行数据库恢复操作时，为了保证恢复的完整性，可以使用全局锁来防止其他会话对数据库进行修改。
维护任务：在进行需要独占数据库资源的维护任务时，可以使用全局锁来确保任务的顺利进行。

尽管全局锁在某些情况下非常有用，但它也存在一些限制和不足之处。全局锁会阻塞其他会话的写操作，可能会导致性能问题。因此，使用全局锁时需要谨慎考虑，并确保在必要的情况下使用。

1.1、全局锁使用语法

flush tables with read lock;

这里我启两个mysql客户端演示

在第一个客户端进行开启全局锁

在第二个客户端我们进行查询操作，可以看到查询操作是没有任何问题的，也就是说开启全局锁后，不影响查询操作

我们进行把id为2的李四这个信息的岁数改变成24，试一下

可以看到，执行sql直接卡住了，SQL被阻塞了，那我们在第一个客户端进行释放锁

unlock tables;

发现全局锁释放的瞬间，update更新操作就可以完成了。

1.2、备份数据库（不使用全局锁）

在innoDB引擎中，使用全局锁来进行备份数据是不合适的，因为加锁期间，业务就不能进行写操作，会被阻塞，那么既要保证数据一致，也要不阻塞业务SQL执行，怎么进行呢，加上参数--single-transaction

mysqldump --single-transaction -uroot -p密码 test01 > D:/test01.sql

2、表锁

MySQL表锁是一种用于控制并发访问数据库表的机制。当多个会话同时对同一个表进行读写操作时，可能会发生数据不一致或数据丢失的问题。为了解决这个问题，MySQL引入了表锁来确保数据库的一致性。

MySQL提供了两种类型的表锁：共享锁（Shared Lock，也称S锁）和排他锁（Exclusive Lock，也称X锁）。

共享锁（Shared Lock）：多个会话可以同时获取共享锁，并且可以同时读取表中的数据，但是不能进行写操作。共享锁适用于并发读取操作，它可以防止其他会话获取到排他锁，从而保证读操作的一致性。
排他锁（Exclusive Lock）：只有一个会话能够获取到排他锁，并且可以进行读写操作。排他锁适用于更新和删除操作，它可以防止其他会话同时对表进行读写操作，确保数据的一致性和完整性。

MySQL表锁的使用方法如下：

在需要对表进行读操作的地方，可以使用共享锁（LOCK TABLES tableName READ）。
在需要对表进行写操作的地方，可以使用排他锁（LOCK TABLES tableName WRITE）。
使用完毕后，需要释放锁（UNLOCK TABLES）。

需要注意的是，表锁是一种较为粗粒度的锁机制，它会锁定整个表，而不是某个具体的行或记录。因此，在高并发的情况下，表锁可能会导致性能问题，并且可能造成阻塞。为了避免这个问题，MySQL还提供了行级锁（Row Lock）和页级锁（Page Lock）等更为细粒度的锁机制，可以根据具体的业务需求选择适合的锁机制。

2.1、读写锁

加锁

lock tables 表名... read/write

释放锁

unlock tables / 客户端断开

读锁

1、本客户端可以进行读操作

2、试图修改user表中的数据，发现报错提示已经加上了读锁，不可进行修改。

3、到另一个客户端进行读操作，发现也可以读

4、使用update进行写操作，发现被阻塞了

5、在第一个客户端进行释放锁，第二个客户端修改操作成功了

写锁

1、在第一个客户端进行加写锁。

2、第一个客户端进行读操作，没有任何问题。

3、第一个客户端进行修改操作，也没有任何问题。

4、在第二个客户端进行读操作，发现被阻塞了。

5、在第二个客户端进行写操作，同样被阻塞了。

6、第一个客户端释放锁，第二个客户端读写都成功

2.2、元数据锁（meta data lock MDL）

元数据锁是数据库中一种特殊类型的锁，用于保护数据库的元数据。元数据是描述数据库的数据结构及其属性的信息，包括表、列、索引、触发器等对象的定义和属性。

总之，元数据锁是数据库管理系统用来保护元数据一致性的机制，它能够确保在多用户并发访问数据库时，对元数据的修改操作能够按照适当的顺序进行，从而保证数据库的正确性和稳定性，元数据锁一般由数据库管理系统自动管理，不需要用户手动操作。

在MySQL5.5中引入了MDL、当对一张表进行增删改査的时候、加MDL读锁(共享):当対表结构进行变更操作的时候、加MDL写锁（排他)。

对应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、delete、update、select ... for update	SHARED_WRITE	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table ...	EXCLUSIVE	与其他MDL都互斥

1、在第一个客户端开启事务进行select查询表

2、使用查询元数据锁信息sql，可以发现现在有一个user表，并且类型为SHARED_READ的锁

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

3、另一个客户端进行事务修改操作

4、再次查询元数据锁信息，发现又多了一个SHARED_WRITE锁

5、执行一次ALTER语句，发现被阻塞了，因为alter table属于EXCLUSIVE，与其他MDL都互斥。

6、第一个客户端进行commit提交，另一个客户端的alter table操作就执行成功了。

2.3、意向锁

意向锁是一种用于处理并发访问的锁机制，主要用于保护共享资源的访问。意向锁分为两种类型：意向共享锁和意向排他锁。

意向共享锁（IS锁）：当事务要对一个资源进行共享访问时，可以先尝试获取意向共享锁。当事务获取到意向共享锁后，其他事务也可以获取意向共享锁，但不能获取意向排他锁。这样可以减少锁冲突，提高并发性能。

意向排他锁（IX锁）：当事务要对一个资源进行排他访问时，可以先尝试获取意向排他锁。当事务获取到意向排他锁后，其他事务不能获取意向共享锁或意向排他锁。这样可以确保只有一个事务可以同时对资源进行排他操作。

意向锁的引入可以减少锁的竞争情况，提高并发性能。在使用意向锁的场景中，事务可以在访问资源之前先获取意向锁，以表示自己对该资源的访问意图。这样可以在一定程度上避免死锁和锁等待的情况。

意向共享锁（IS锁）

1、使用第一个客户端对user表加一个共享锁

2、打印锁信息表，可以发现有两个锁，第一个就是意向锁，类型为IS（意向共享锁），第二个就是行锁，锁的是id=1的数据。

3、在第二个客户端进行加读锁，发现是可以的，因为意向共享锁和读锁不排斥，是兼容的。

4、我们再对user表加写锁，发现被阻塞了，因为意向共享锁和写锁是互斥的。

意向排他锁（IX锁）

1、使用第一个客户端开启线程执行update语句

2、另启一个客户端，查询锁信息，发现有一个IX锁，也就是意向排他锁，还有一个就是我们修改的id=1的行锁。

3、使用lock tables user read 加一个读锁，发现被阻塞了。

4、使用lock tables user write加一个写锁，发现也被阻塞了，可以得出结论IX意向排他锁与读写锁都互斥。

3、行锁

MySQL数据库的行级锁是一种用于并发控制的锁机制，只有在innoDB引擎中有，它可以在多个会话同时访问同一张表的不同行时提供数据一致性和并发性保证。

行级锁是MySQL中最细粒度的锁，它只锁定表中的一行数据，而不是整个表或页面。这意味着其他会话可以同时访问同一张表的其他行，而不会被阻塞。

行级锁主要有两种类型：共享锁（S锁）和排他锁（X锁）。

共享锁（S锁）：也称为读锁，多个会话可以同时获取S锁，并且互不干扰。多个会话获取S锁后，可以并发地读取被锁定的行数据，但无法修改或删除这些行。只有当所有S锁都被释放后，其他会话才能获取X锁进行修改操作。
排他锁（X锁）：也称为写锁，只有一个会话可以获取X锁，并且其他会话无法获取任何锁。获取X锁的会话既可以读取被锁定的行数据，也可以修改或删除这些行。只有当X锁被释放后，其他会话才能获取任何锁。

行级锁的使用可以提高并发性能，因为它可以减少锁冲突。但是，行级锁也有一些限制，比如占用更多的内存和CPU资源，以及可能导致死锁等并发问题。因此，在使用行级锁时需要谨慎设计和调整，并结合具体的业务场景来决定是否需要使用。

默认情况下，InnoDB在REPEATABLE READ（可重复读）事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。

1.针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

2.InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

3.1、记录锁

记录锁（Record Lock）是一种用于数据并发控制的机制。在并发访问数据的环境中，多个用户或进程可能会同时读写同一个数据记录。为了避免数据不一致的问题，记录锁被用来确保在某一时刻只有一个用户或进程可以对同一记录进行修改。

记录锁可以分为两种类型：共享锁和排他锁。

共享锁（S锁）

语法：

select * from xxx lock in share mode;

1、第一个客户端开启事务进行加共享锁

2、查询一下锁信息表，可以发现有一个行锁，并且类型为S锁（共享锁）。

3、第一个客户端进行commit提交事务

4、再次查询锁数据表，发现已经空了，行锁被释放了

排他锁（X锁）

数据库中select ... for update 、delete 、 update 、insert这些操作，都是X锁。

1、在第一个客户端开启事务，进行update操作

2、查询一下锁数据，发现现在有一个排他锁（X锁），并且锁类型是行级锁。

3、我们在第二个客户端也更新id=2的数据，发现被阻塞了。

4、我们在第二个客户端进行更新id=1的数据，发现并没有阻塞，查询锁信息，发现有多了一个id=2的排他锁。

行锁升级为了表锁的情况

因为记录锁是作用于索引上的，innoDB肯定是有索引的，记录锁总是锁定索引记录。

行锁也有一种情况会升级为表锁，就是在我们的写操作中，where条件后没有用到索引，就会升级为表锁。

1、在第一个客户端进行开启事务，并修改name为张三的年龄。（name是没有索引的）

2、在第二个客户端进行开启事务，修改id=2的数据，并不是张三这条数据，发现被阻塞了，为什么修改的不是id=1的数据，也会被阻塞呢，原因就是行锁升级为了表锁，整个表被锁了，不管你修改那条数据。

3、打印一下锁信息，可以发现lock_type变为了TABLE，表级别的锁

4、解决方法呢，就是给name创建一个索引

5、重新开启事务，修改name为张三的数据

6、在另一个客户端进行修改名字为李四的数据，发现这次就没有被阻塞。

3.2、间隙锁

索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁

间隙锁（Gap lock）是MySQL数据库中一种用于处理并发事务的锁机制。它的目的是在事务中保护数据的一致性和完整性。

间隙锁的主要作用是锁定一段数据范围之间的间隙，即锁定两个值之间的空隙。当一个事务对数据范围中的某个数据行进行锁定时，间隙锁会阻止其他事务在这个数据范围内插入新的数据行。这样可以确保在事务修改数据时，其他事务不会在相同的数据范围内插入新的数据，从而保持数据的一致性。

1、首先准备一个唯一索引，也就是主键有间隙的数据

2、第一个客户端进行开启事务，修改id=3的数据，但是并没有这个数据

3、我们这时查询一下锁信息，发现这个时候lock_mode变为了GAP，这个就是间隙锁

4、我们在第二个客户端开启事务并插入一个id为4的数据，发现被阻塞了，也就是说主键3到5之间被加锁了。

5、客户端1提交释放锁，发现客户端2的新增操作成功了

3.3、临键锁（next-key lock）

Next-Key Lock是MySQL数据库中InnoDB存储引擎的一种锁机制，用于解决事务并发控制中的幻读问题。Next-Key Lock是一种结合了记录锁（Record Lock）和间隙锁（Gap Lock）的锁定技术。下面就来详细聊聊Next-Key Lock：

防止幻读：在事务隔离级别为可重复读（REPEATABLE READ）时，Next-Key Lock可以防止幻读现象。幻读是指在同一个事务中，连续执行两次相同的查询，可能导致第二次查询看到第一次查询未看到的行。
保证数据的一致性：通过锁定记录及其前面的间隙，Next-Key Lock确保了在事务执行过程中，其他事务无法插入符合条件的记录，从而保证了数据的一致性。
临键锁锁住的区间为记录+区间（左开右闭) ，InnoDB中行级锁是基于索引实现的，临键锁只与非唯一索引有关，在唯一索引列（包括主键列）是不存在临键锁的。

准备数据库数据如下：

CREATE TABLE `user` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

该表中有潜在的临键锁有：

(-无穷，10] 、（10，24] 、（24，32] 、（32，45]

1、查询user表并加上锁

2、另开一个客户端执行插入操作，age为26的数据，发现被阻塞了，因为26在（24，32]区间范围内。

3、查询锁信息，其中在（24,32]中存在了一把临键锁。

本篇文章就写到这里了，感兴趣的小伙伴再深入研究一下，制作不易，点赞支持呦！

热爱编程的申同学

关注

25
点赞
踩
24

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库锁机制（全面讲解）

在这个数据驱动的时代，数据库的重要性不言而喻。尤其是当我们面对成千上万的用户同时操作数据库时，如何保证数据不会乱套，这就得聊聊MySQL的锁机制了。锁，听起来好像有点神秘，但其实它就像是数据库的一道安全门，接下来，我会和大家一起探讨一下MySQL数据库锁的点点滴滴，包括它是怎么工作的，有哪些类型，以及在实际应用中怎么用。你能对数据库锁有个更清晰的了解，以后遇到并发问题也能更从容应对。那么，让我们开始吧！
复制链接

扫一扫