文章目录
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 t.name = ‘cc’ where t.name = ‘c’;
mysql> update mylock t set t.name = 'cc' where t.name = '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 t.name = 'dd' where t.name = '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 t.name = ‘cc’ where t.name = ‘c’; // 加锁 | 查询 未修改 | |
查询 未修改 | update mylock t set t.name = ‘dd’ where t.name = ‘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 t.name = "123’ where t.id = 1;/未提交事务 | |
行锁 | update mylock t set t.age = "13’ where t.id = 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 t.id >=1 and t.id <=3; //获取写锁 | ||
update mylock t set t.age = 13 where t.id = 7; //update success | ||
update mylock t set t.age = 13 where t.id = 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将对表中的所有记录加锁,实际效果可以理解为表锁。