当前位置：首页 > news >正文

MySQL--》理解锁机制中的并发控制与优化策略

news 来源：原创 2025/5/7 8:56:09

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

1）全局锁：锁定数据库中的所有表

2）表级锁：每次操作锁住整张表

3）行级锁：每次操作锁住对应的行数据

全局锁

表级锁

行级锁

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于就读状态，后续的DML的写语句即DDI语句已经更新操作的事务提交语句都将被阻塞，其典型的使用场景是做全库的逻辑备份，对所有表进行锁定从而获得一致性视图，保证数据的完整性。

如下当我们进行数据备份的时候不加全局锁的情况下，就会导致在备份的时候还会发生数据的插入操作导致前后数据不一致：

我们在进行数据备份的时候，可通过如下步骤进行操作，在添加全局锁之后整个数据库只能读不能写，当数据库备份完毕之后我们再释放锁即可：

接下来我们演示如何添加全局锁并进行数据备份操作，如下所示：

添加全局锁：通过执行如下命令来进行添加全局锁操作：

flush tables with read lock;

如下代码当我们设置全局锁的时候，查询操作正常执行但数据更新就不能被执行了：

数据库备份：接下来我们开始进行数据库备份，mysqldump并不是sql命令而是一个工具，这里我们直接在cmd终端允许如下命令即可，-h是地址，-u是用户名，-p是密码，后面的是要备份的数据库名称，路径是要保存的地址：

mysqldump -h localhost -uroot -p123456 mysql_test > D:/mysql_test.sql

如下我们可以看到我们的数据库已经备份成功了，来到我们保存的地址通过文件打开可以看到我们的数据已经成功被备份出来了：

释放数据库：数据成功备份之后，我们要执行如下命令对数据库进行释放全局锁：

unlock tables;

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

1）如果在主库上备份，那么在备份期间都不能执行更新操作，业务基本上就停摆了

2）如果在库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，会导致主从延迟

在InnoDB引擎中，我们可以在备份时添加参数 --single-transaction 参数来完成不加锁的一致性数据备份，如下所示：

mysqldump --single-transaction -h localhost -uroot -p123456 mysql_test > D:/mysql_test02.sql

表级锁

表级锁每次操作锁住整张表，锁定粒度大发生锁冲突的概率最高，并发度最低，应用在MySQL、InnoDB、BDB等存储引擎中，对于表级锁主要分为以下三类：

1）表锁：对于表锁主要分为两类：表共享读锁(read lock)；表独占写锁(write lock)，其语法如下所示：

-- 加锁
lock tables 表名... read/write-- 释放锁
unlock tables / 客户端锻炼连接

读锁：当我们进行读锁的时候，客户1对数据库执行了读锁操作，那其只能读不能写，客户2也是只能读不能写的，如下所示：

写锁：当我们进行写锁的时候，客户1对数据库进行了写锁操作，那其既能读又能写，但客户2却是不能进行读和写的操作，如下所示：

这里我们拿写锁进行演示，如下我们开了两个查询控制台当作两个客户在使用，客户1执行了写锁操作之后，客户2的读写都不能使用了，只有当客户1释放表锁之后，客户2才能正常使用，如下：

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

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

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

当我们要对一张表加上表锁的时候，需要遍历整张表中是否加上了行锁，如果加上了行锁再加表锁就可能产生冲突了：

对于意向锁主要分为以下两种：

1）意向共享锁（IS）：select ... lock in share mode 添加，与表锁共享锁(read)兼容，与表锁排它锁(write)互斥：

2）意向排他锁（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;

行级锁

行级锁：每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高，应用在InnoDB存储引擎中，InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

行锁(recordl lock)：锁定单个行记录的锁防止其他事务对此行进行update和delete，在rc、rr隔离级别下都支持，如下所示：

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

1）共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

2）排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

当我们正常进行增删改查的时候，所加的行锁的类型如下所示：

默认情况下，InnoDB在REPEATABLEREAD事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读

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

间隙锁(gap lock)：锁定索引记录间隙(不含该记录)确保索引记录间隙不变，防止其他事务在这个间隙进行insert产生幻读，在rr隔离级别下都支持，如下所示：

默认情况下，InnoDB在REPEATABLEREAD事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引引扫描，以防止幻读：

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

2）索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
3）索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

临键锁(next-key lock)：行锁和间隙锁组合，同时锁住数据并锁住数据前面的间隙gap，在rr隔离级别下支持，如下所示：

全局锁

表级锁

行级锁

相关文章：