mysql innodb锁监控 mysql innodb锁表

转载

mob64ca1404ed65 2023-10-11 06:50:17

文章标签 mysql innodb锁监控 mysql innodb 数据加锁 文章分类 MySQL 数据库

文章目录

MySql锁 InnoDB锁机制

1 锁的介绍
2 MySql表级锁介绍

2.1 表级锁介绍
2.2 表锁介绍
2.3 表锁:共享读锁排他写锁演示
2.4 元数据锁

2.4.1 演示

3 行锁介绍

3.1 行锁的介绍
3.2 演示

3.2.1 行锁演示
3.2.2 间隙锁带来的修改问题

4 总结

MySql锁 InnoDB锁机制

1 锁的介绍

MySQL主要包含三种类型（级别）的锁定机制：

全局锁：锁的是整个database。由MySQL的SQL layer层实现的
表级锁：锁的是某个table。由MySQL的SQL layer层实现的
行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。

按照锁的功能来说分为：共享读锁和排他写锁。
按照锁的实现方式分为：悲观锁和乐观锁（使用某一版本列或者唯一列进行逻辑控制）
表锁与行锁的区别：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

2 MySql表级锁介绍

2.1 表级锁介绍

MySql表锁有两种
一种是表锁。
一种是元数据锁（meta data lock，MDL)
MySQL 实现的表级锁定的争用状态变量：show status like ‘table%’;

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 72    |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 5     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

说明：

table_locks_immediate：产生表级锁定的次数；
table_locks_waited：出现表级锁定争用而发生等待的次数；

2.2 表锁介绍

表锁两种形式
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）
手动增加表锁,多个表同时加锁，逗号隔开

lock table 表名称 read(write),表名称2 read(write).;

查看表锁情况

show open tables;

删除表锁

unlock tables;

2.3 表锁:共享读锁排他写锁演示

场景：三个线程，线程A，C同时修改表某一列数据，线程B查询mylock数据；
mylock现表数据

+----+-------+
| id | NAME  |
+----+-------+
|  1 | 12345 |
|  2 | test  |
|  3 | c     |
|  4 | d     |
+----+-------+
4 rows in set (0.00 sec)

线程A：设置不自动提交事务

set autocommit = 0;

线程A修改mylock表某一行数据：update mylock t set = ‘cc’ where = ‘c’;

mysql> update mylock t set  = 'cc' where  = 'c';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

线程B查询mylock数据，发现数据没有更改

mysql> select * from mylock;
+----+-------+
| id | NAME  |
+----+-------+
|  1 | 12345 |
|  2 | test  |
|  3 | c     |
|  4 | d     |
+----+-------+
4 rows in set (0.00 sec)

线程C修改某一行数据

update mylock t set  = 'dd' where  = 'd';
// 当前处于等待状态

在等待的过程中，线程B在查询mylock，发现查询结果依然没有改变

mysql> select * from mylock;
+----+-------+
| id | NAME  |
+----+-------+
|  1 | 12345 |
|  2 | test  |
|  3 | cc    |
|  4 | d     |
+----+-------+
4 rows in set (0.00 sec)

线程A提交事务

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

注意：线程A提交事务，线程C可能存在两种情况:更新成功或者是等待释放锁超时,更新失败。
6. 查看表信息

mysql> select * from mylock;
+----+-------+
| id | NAME  |
+----+-------+
|  1 | 12345 |
|  2 | test  |
|  3 | cc    |
|  4 | dd    |
+----+-------+
4 rows in set (0.00 sec)

并发图

线程A	线程B	线程C
设置不自动提交事务 set autocommit = 0;
update mylock t set = ‘cc’ where = ‘c’; // 加锁	查询未修改
	查询未修改	update mylock t set = ‘dd’ where = ‘d’; // 等待锁释放
commit; //提交事务
		情况1：等待锁超时失败；情况2：获取锁，更新成功
	查询数据更新

2.4 元数据锁

概念介绍
MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

2.4.1 演示

线程A	线程B	线程C	线程D
set autocommit=0;
select * from mylock; // success
	select * from mylock; // success
		alter table mylock add age int; // 阻塞等待
			select ＊ from mylock; // 等待
commit
		update success, 释放锁
			select success

流程介绍：

我们可以看到线程 A 先启动，这时候会对表 mylock 加一个 MDL 读锁。
线程B查询需要的也是 MDL 读锁，因此可以正常执行。
线程 C 会被 blocke(阻塞)，是因为线程 A 的 MDL 读锁还没有释放，而线程 C 需要 MDL 写锁，因此只
能被阻塞。后续所有的查mylock请求都会被阻塞,直到线程C的写锁被释放。
线程D查询被阻塞。问题：思考，等待线程C释放MDL写锁,线程D查询的数据列是否包含线程C新加的列呢?答案在总结

你现在应该知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放

3 行锁介绍

3.1 行锁的介绍

MySql的行锁上由其存储引擎InnoDb自动实现的，主要分为三种：

记录锁（Record Locks）:锁定索引中一条记录。
间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
Next-Key Locks:是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

InnoDB的行级锁，按照功能来说，分为两种

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。简单来说，在事务的环境下，执行insert，update，delete语句会对操作的数据加排他写锁，导致其他线程不允许操作这些被加锁的数据。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

Innodb所使用的行级锁定争用状态查看: show status like ‘innodb_row_lock%’;

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 509066 |
| Innodb_row_lock_time_avg      | 22133  |
| Innodb_row_lock_time_max      | 51531  |
| Innodb_row_lock_waits         | 23     |
+-------------------------------+--------+
5 rows in set (0.01 sec)

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

3.2 演示

3.2.1 行锁演示

场景：线程A 与线程B，通过主键索引同时修改一条数据

线程A	线程B
set autocommit=0;// 开启事务
update mylock t set = "123’ where = 1;/未提交事务
行锁	update mylock t set t.age = "13’ where = 1;// 阻塞
commit;// 提交事务 update success
	update success

3.2.2 间隙锁带来的修改问题

场景：线程A批量修改数据，线程B修改非同集数据，线程C修改同集数据

线程A	线程B	线程C
set autocommit=0;
update mylock t set t.age = 12 where >=1 and <=3; //获取写锁
	update mylock t set t.age = 13 where = 7; //update success
		update mylock t set t.age = 13 where = 2; // 阻塞
commit; 释放写锁 update sueecss
		获取写锁，update success

4 总结

表锁与原数据锁，客户端在操作数据的时候，InnoDB默认会自动加上，开发者需要了解其原理，对数据库的操作更能顺心应手。
读锁和写锁都是行级锁，InnoDB的行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁，InnoDB行锁分为3中情形：

Record Lock：对索引项加锁。
Gap Lock：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。
Next-key Lock：前两种的结合，对记录及其前面的间隙加锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；
对于普通SELECT语句，InnoDB不会加任何锁，事务可以显示给记录集加共享锁或排他锁。
InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

InnoDB这种行锁的实现特点意味着，如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果可以理解为表锁。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。