MySQL数据库锁定机制

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL数据库锁定机制

1.MySQL锁定机制

各存储引擎使用三种类型锁定机制

行级锁定(row-level)

表级锁定(table-level)

页级锁定(page-level):页级介于行级锁定与表级锁定之间

2.MySQL数据库中表级锁定主要是MyISAM、memory、CSV等一些非事务性存储引擎,使用行级锁定主要是InnoDB存储引擎和NDB cluster存储引擎,页级锁定主要是BerkleyDB存储引擎

3.MyISAM表级锁定主要分为两种类型

读锁定,一个新客户端在申请获取读锁定资源的时候,需要满足两个条件:

请求锁定的资源当前没有被写锁定

写锁定等待队列中没有更高优先级的写锁定在等待

4.  MySQL 中主要分 4 中队列来维护这两种锁定:两个存放当前正在锁定的读和写锁定信息,另外两个存放等待中的读写锁定西信息,如下:

Current read-lock queue (lock->read)

Pending read-lock queue (lock->read_wait)

Current write-lock queue (lock->write)

Pending write-lock queue (lock->write_wait)

5. InnoDB 的行级锁定分为四种类型

共享锁 (有叫做:读锁)

允许一个事务去读一行,阻止其他事务获得相同数据的排它锁。

排他锁 (有叫做:写锁)

允许获得排它锁的事务更新数据,阻止其他事务

意向共享锁

意向排他锁

6.InnoDB间隙锁

InnoDB 的锁定是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间标记锁定信息实现的。这种锁定方式被称为 “NEXT-KEY locking”(间隙锁)

间隙锁弱点:锁定一个范围之后,即使某些不存在的键值也会被无辜锁定,造成锁定的时候无法插入键值锁定内的任何数据。

通过索引实现锁定的方式存在其他几个较大的性能隐患:

当 Query 无法利用索引的时候,InnoDB 会放弃使用 行级锁定 而改用 表级锁定 ,造成并发性能降低;

当 Query 使用的索引并不包含所有过滤条件时,数据检索使用到的索引键中的数据可能有部分不属于 Query 的结果集行列,但是也会被锁定,因为间隙锁锁定的是一个范围,而不是具体的索引键。

当 Query 在使用索引定位数据的时候,如果使用的索引键一样但访问的数据行不同 (索引只是过滤条件的一部分), 他们一样会被锁定。

7. MyISAM 表锁优化建议

缩短锁定时间

尽量减少大的复杂 Query,将复杂 Query 拆分成几个小的 Query 执行。

尽可能地建立足够高效的索引,让数据检索更迅速。

尽量让 MyISAM 存储引擎的表只存放必要的信息,控制字段类型。

利用合适的机会优化 MyISAM 表数据文件。

分离能并行的操作

concurrent_insert = 2,无论 MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间,都允许在数据文件尾部进行 Concurrent Insert。

concurrent_insert = 1,MyISAM 存储引擎表数据文件中间不存在空闲空间的时候,可以从文件尾部进行 Concurrent Insert。

concurrent_insert = 0, 无论 MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间,都不允许 Concurrent Insert。(读锁时,不允许插入)

MyISAM 并非只能完全的串行化,MyISAM 存储引擎还有一个特性 Concurrent Insert(并发插入)的特性。

MyISAM 存储引擎有一个控制是否打开 Concurrent insert 功能的参数选项: concurrent_insert 可以设置为 0/1/2:具体如下:

合理利用读写优先级

表级锁定 默认情况下写优先级大于读,如果读操作多的时候,可以设置读优先级高,可设置参数 low_priority_updates = 1。

8.  InnoDB 行锁优化建议

尽可能让所有的数据检索都通过索引来完成,从而避免 InnoDB 因为无法通过索引键加锁而升级为表级锁定

合理设计索引,让 InnoDB 在索引键上加锁的时候尽可能准确,尽可能地缩小锁定范围,避免造成不必要的锁定而影响其他 Query 的执行。

尽可能减少基于范围的数据检索过滤条件,避免因间隙锁带来的负面影响而锁定了不该锁定的记录。

尽量控制事务大小,减少锁定的资源量和锁定的时间长度。

在业务环境允许的情况下,尽量使用较低级别的事务隔离,减少 MySQL 因为实现事务隔离级别所带来的附加成本。

9.  系统锁定争用情况查询

MySQL 内部有两组专用的状态变量记录系统内部资源争用情况。

表级锁定的争用状态变量

mysql> show status like ‘table%’;

Table_locks_immediate:产生表级锁定的次数;

Table_locks_waited:出现表级锁定争用而发生等待的次数

Table_locks_immediate 值大于 Table_locks_waited 5000 是比较合适的,在大就需要分析问题所在。

两个状态值都是从系统启动后开始记录,每出现一次加 1,如果这里 Table_locks_waited 状态值比较高,说明表级锁定争用严重,需进一步分析。

InnoDB 行级锁定状态变量记录

sql> show status like ‘innodb_row_lock%’;

Innodb_row_lock_current_waites:当前正在等待锁定的数量;

Innodb_row_lock_time:从系统启动到现在锁定总时间长度;

Innodb_row_lock_time_avg:每次等待所花平均时间;

Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间;

Innodb_row_lock_waits:从系统启动到现在总等待次数。

5 个状态,比较重要的是 Innodb_row_lock_time_avg(等待平均时长),Innodb_row_lock_waits(等待总次数)及 Innodb_row_lock_time(等待总时长)

10.  InnoDB 除了提供以上 5 个系统状态变量外,还提供了更为丰富的即时状态信息,实现方法如下:

创建 InnoDB Monitor 表来打开 InnoDB 的 monitor 功能

mysql > create table innodb_monitor(a int) engine=innodb;

然后执行 ”show innodb status” 查看详细信息

为什么创建 innodb_monitor 表?

创建该表就是告诉 InnoDB 我们要开始监控他的详细信息,然后 InnoDB 就会将比较详细的事务级锁定信息记录到 MySQL 的 error log 中,以便后面做进一步分析。


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
关系型数据库 MySQL
|
1月前
|
存储 SQL 关系型数据库
MySQL - 深入理解锁机制和实战场景
MySQL - 深入理解锁机制和实战场景
|
1月前
|
存储 算法 关系型数据库
MySQL - 锁机制初探
MySQL - 锁机制初探
59 0
|
1月前
|
SQL 关系型数据库 MySQL
|
9天前
|
存储 SQL 关系型数据库
MYSQL--锁机制*
MYSQL--锁机制*
|
1月前
|
存储 机器学习/深度学习 搜索推荐
深入解析矢量数据库的数据模型与索引机制
【4月更文挑战第30天】本文深入探讨了矢量数据库的数据模型和索引机制。向量数据库以高维向量表示数据,采用稀疏或密集向量形式,并通过数据编码和组织优化存储与检索。索引机制包括基于树的(如KD-Tree和Ball Tree)、基于哈希的(LSH)和近似方法(PQ),加速相似性搜索。理解这些原理有助于利用矢量数据库处理大规模高维数据,应用于推荐系统、图像搜索等领域。随着技术发展,矢量数据库将扮演更重要角色。
|
9天前
|
存储 关系型数据库 MySQL
【MySQL技术内幕】4.5-Named File Formats机制
【MySQL技术内幕】4.5-Named File Formats机制
14 2
|
1月前
|
存储 运维 负载均衡
关系型数据库引入故障转移机制
【5月更文挑战第4天】关系型数据库引入故障转移机制
35 8
关系型数据库引入故障转移机制
|
10天前
|
SQL 关系型数据库 MySQL
【MySQL】事务?隔离级别?锁?详解MySQL并发控制机制
【MySQL】事务?隔离级别?锁?详解MySQL并发控制机制
21 0
|
1月前
|
存储 关系型数据库 MySQL
MySQL的锁机制
MySQL的锁机制主要用于管理并发事务对数据的一致性和完整性的访问控制
35 4

热门文章

最新文章