关于mysql:浅析MySQL-InnoDB行锁

46次阅读

共计 5105 个字符,预计需要花费 13 分钟才能阅读完成。

咱们都晓得 MySQL 表引擎常见的分两种,一种 MyISAM, 一种 InnoDB,这题个别大家都会,常常在 DB 类面试题中呈现,题目常常问及两者的区别,一聊就是 MyISAM 是表锁,InnoDB 是行锁,InnoDB 反对事务,MyISAM 不反对事务等等。

明天咱们这篇文章次要来聊一下 InnoDB 的行锁(因为以后我的我的项目中次要是 InnoDB 的表引擎)。

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

一种为共享锁,又称为读锁,共享锁是指多个事务对同一数据能够共享同一把锁,都能拜访数据,然而只能读不能批改。

另一种为排他锁,又称为写锁,排他锁不能与其余锁共存,如果一个事务取得了一个数据行的排他锁,其余事务就不能获取该行的其余锁,包含共享锁和排他锁,然而获取排他锁的事务能够对该数据行读取和批改。

对于 update, delete, insert 这三种 DML 语句,InnoDB 会主动给波及到的数据行加排他锁。

对于个别的 select 语句而言,InnoDB 则不会加任何锁。

我上面来举些例子来简略解释一下上述的锁状况:
1. 建一张 test 表

create table test(id int(11),
  name varchar(255),
  alias varchar(255)
)engine = innodb default charset=utf8;

2. 给 test 表建两个单列索引

create index idx_id on test(id);
create index idx_name on test(name);

3.test 表插一些测试数据

insert into test values(1, '张三', 'zhangsan');
insert into test values(2, '李四', 'lisi');
insert into test values(3, '王五', 'wangwu');
insert into test values(4, '赵六', 'zhaoliu');
insert into test values(5, '孙七', 'sunqi');
insert into test values(6, '冯八', 'fengba');
insert into test values(7, '杨九', 'yangjiu');

4. 咱们开两个会话窗口来模仿两个事务(一个会话 1, 一个会话 2), 并且敞开两个会话的 autocommit,便于模仿两个事务

set autocommit = 0; // 会话 1 和会话 2 

5. 咱们先看一下两个会话的 select 查问(查问全量)

MySQL [wakka]> select * from test;   // 会话 1
+------+--------+----------+
| id   | name   | alias    |
+------+--------+----------+
|    1 | 张三   | zhangsan |
|    2 | 李四   | lisi     |
|    3 | 王五   | wangwu   |
|    4 | 赵六   | zhaoliu  |
|    5 | 孙七   | sunqi    |
|    6 | 冯八   | fengba   |
|    7 | 杨九   | yangjiu  |
+------+--------+----------+

MySQL [wakka]> select * from test;  // 会话 2
+------+--------+----------+
| id   | name   | alias    |
+------+--------+----------+
|    1 | 张三   | zhangsan |
|    2 | 李四   | lisi     |
|    3 | 王五   | wangwu   |
|    4 | 赵六   | zhaoliu  |
|    5 | 孙七   | sunqi    |
|    6 | 冯八   | fengba   |
|    7 | 杨九   | yangjiu  |
+------+--------+----------+

6. 咱们再看一下两个会话的 select 查问(查问部分)

MySQL [wakka]> select * from test where id = 3; // 会话 1
+------+--------+--------+
| id   | name   | alias  |
+------+--------+--------+
|    3 | 王五   | wangwu |
+------+--------+--------+
1 row in set (0.00 sec)

MySQL [wakka]> select * from test where id = 3; // 会话 2
+------+--------+--------+
| id   | name   | alias  |
+------+--------+--------+
|    3 | 王五   | wangwu |
+------+--------+--------+
1 row in set (0.00 sec)

由此也能验证结尾的话,无论是获取部分数据还是全局数据,对 select 语句而言,InnoDB 都不会加任何锁。

7. 咱们接着测试一下更新语句,会话 1 将 id= 3 的 name 设置成“王八”,会话 2 将 id= 3 的 name 设置成“王九”。

MySQL [wakka]> update test set name = '王八' where id = 3;  // 会话 1
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

// 那其余会话还能对这行进行批改吗?MySQL [wakka]> 
MySQL [wakka]> 
MySQL [wakka]> update test set name = '王九' where id = 3; // 会话 2 批改 id= 3 会始终卡在这

会话 2 之所以阻塞在那的起因是因为会话 1 的 update 语句会对 id= 3 加排他锁(下面咱们说过,insert, update, delete 会对数据行加排他锁),所以当另外一个会话事务想改 id= 3 的时候,会被阻塞在那,直到会话 1 的 update 事务提交才会开释锁。

// 当会话 1 的事务提交后,会话 2 卡在那的事务才会执行
MySQL [wakka]> update test set name = '王九' where id = 3;
Query OK, 0 rows affected (1 min 47.71 sec)  // 会发现执行工夫超长
Rows matched: 1  Changed: 0  Warnings: 0

咱们下面的例子是 update 的同一行(id=3),如果 update 不同行呢?

MySQL [wakka]> update test set name = '王十' where id = 3; // 会话 1:update id=3
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [wakka]> update test set name = '赵十' where id = 4; // 会话 2: update id=4
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    160714383
Current database: wakka

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

咱们能够看到,当更新不同行的时候,是不会呈现阻塞的,这也能够阐明 InnoDB 应用的行锁,同时也能阐明行锁的并发性能会好过表锁,因为能够给不同的行加锁,不同行之间会互不干涉。

以上就是 InnoDB 行锁的根本解说,说到 InnoDB 行锁,还有一个常见的问题就是 InnoDB 就肯定是行锁吗?InnoDB 存不存在表锁呢? 先说答案:InnoDB 有可能产生表锁,当没有用到索引或者索引生效的时候会产生表锁,具体我用个例子来阐明:

咱们下面给 test 这张表的 id 字段和 name 字段建了索引,然而 alias 并没有。

MySQL [wakka]> show index from test;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    160714511
Current database: wakka

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          1 | idx_id   |            1 | id          | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_name |            1 | name        | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

咱们先在会话 1 里用 alias 条件更新一行数据,留神我用的条件是 alias=xxx

MySQL [wakka]> set autocommit = 0;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    160714522
Current database: wakka

Query OK, 0 rows affected (0.01 sec)

MySQL [wakka]> 
MySQL [wakka]> 
MySQL [wakka]> select * from test;
+------+--------+----------+
| id   | name   | alias    |
+------+--------+----------+
|    1 | 张三   | zhangsan |
|    2 | 李四   | lisi     |
|    3 | 王九   | wangwu   |
|    4 | 赵十   | zhaoliu  |
|    5 | 孙七   | sunqi    |
|    6 | 冯八   | fengba   |
|    7 | 杨九   | yangjiu  |
+------+--------+----------+
7 rows in set (0.00 sec)

MySQL [wakka]> update test set name = '冯十' where alias = 'fengba';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

接着咱们在会话 2 中执行另一个单行 update 变更:

MySQL [wakka]> set autocommit = 0;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    160714527
Current database: wakka

Query OK, 0 rows affected (0.01 sec)

MySQL [wakka]> 
MySQL [wakka]> 
MySQL [wakka]> 
MySQL [wakka]> update test set name = '赵十一' where id = 4;

会话 2 会始终阻塞,咱们同样是更新不同行(操作不同行),为什么会话 2 还会阻塞呢?InnoDB 不是反对的行锁吗?起因是因为以后行锁曾经生效,因为没有用到索引(或者索引生效),所以行锁降级到表锁。索引生效能够有很多种状况,最常见的是一种状况是一个 varchar 字符型类型的字段(存的是数值型的字符),而后 where 条件不带引号。(当前有机会能够开一个专题来讲讲索引生效的状况)。

以上就是 InnoDB 行锁的简略解说,MySQL 锁是 MySQL 当中很简单的一块,越研究会发现外面的内容越深。上述只是浅析,大抵的介绍了上行锁和行锁降级为表锁。


专一 Web 开发,后盾开发,DB 性能和架构
我的公众号:“码农漫谈”,欢送关注与交换

正文完
 0