MySql-4 InnoDB锁机制

MySql锁 InnoDB锁机制

1 锁的介绍

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

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

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

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)

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

set autocommit = 0;

2. 线程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

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

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

4. 线程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)

5. 线程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)

7. 并发图

线程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

流程介绍：

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

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

3 行锁介绍

3.1 行锁的介绍

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

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

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

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

3. 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中情形：

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

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

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