MySQL之锁机制

表锁和行锁
- - - 1.表级锁
    - 2.行级锁
排它锁和共享锁
间隙锁
意向共享锁和意向排它锁
- - - 1.InnoDB表级锁
    - 2.意向锁
死锁

环境：MySQL8.0，事务提交方式使用手动提交，默认使用可重复读隔离级别。如果不特别声明，则使用默认存储引擎InnoDB。使用的表结构如下：

# uid作为主索引，age作为辅助索引
CREATE TABLE `student` (
  `uid` int unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` varchar(16) DEFAULT NULL COMMENT '学生姓名',
  `age` tinyint unsigned DEFAULT '18' COMMENT '学生年龄',
  `sex` enum('male','female') NOT NULL COMMENT '学生性别',
  PRIMARY KEY (`uid`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

表锁和行锁

按照锁的粒度可以将锁划分为表锁和行锁。

1.表级锁

表级锁：对整张表进行加锁，锁的粒度比行级锁大，发生锁冲突的概率高，并发度低。
MyISAM存储引擎支持表级锁

2.行级锁

行级锁：对某行记录进行加锁，锁的粒度更小。发生锁冲突的概率低，并发度高。
InnoDB存储引擎支持行级锁和表级锁。
InnoDB中的行级锁：通过给索引上的索引项加锁来实现行级锁，而不是给表的行记录加锁。所以只有通过索引条件检索数据，InnoDB才使用行级锁，否则使用表锁。

# 示例1
# 会话1
mysql> begin;       # 1
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where age = 23 for update;         # 3
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   1 | 关华   |   23 | male |
|   8 | 刘炜   |   23 | male |
+-----+--------+------+------+
2 rows in set (0.00 sec)


# 会话2
mysql> begin;   # 2
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where age = 20 for update; # 4
+-----+--------+------+--------+
| uid | name   | age  | sex    |
+-----+--------+------+--------+
|   3 | NrvCer |   20 | female |
|   6 | 小王   |   20 | male   |
+-----+--------+------+--------+
2 rows in set (0.00 sec)

# 如上所示，会话1和会话2分别在不同的记录的索引项加排他锁没有问题
# 因为加的锁是行级锁

# 示例2

# 会话1
mysql> begin;       # 1
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where name ='NrvCer' for update;         # 3
+-----+--------+------+--------+
| uid | name   | age  | sex    |
+-----+--------+------+--------+
|   2 | NrvCer |   22 | male   |
|   3 | NrvCer |   20 | female |
+-----+--------+------+--------+
2 rows in set (0.00 sec)

# 会话2
mysql> begin;       # 2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where name = '小王' for update;             # 4
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# 如上所示：因为name字段并没有建立索引，因此会话1的第三步使用的表级的排他锁
# 第四步虽然对其他记录加排它锁，但是依然阻塞

即使SQL中使用了索引，但是经过MySQL的优化器后，如果认为全表扫描比使用索引效率高，此时会放弃使用索引，因此也不会使用行锁，而是使用表锁。这个涉及到MySQL的优化，MySQL的优化：某个字段建立了索引，而且使用where过滤条使用了该字段，但是进行查询时，符合要求的记录数和整表记录数相差不大，则MySQL不会使用索引进行多路查找，而是进行整表搜索。

排它锁和共享锁

MySQL的事务的隔离级别中，串行化这一隔离级别的实现原理就是排它锁和共享锁，select查询操作默认获取共享锁，insert、update、delete获得的是排它锁。因此这里介绍一个排它锁和共享锁。按照锁的类型可以分为排它锁和共享锁。

1.排它锁

排它锁（Exclusive）：又称为X锁，写锁。
显式加排它锁：select ... for update;

select * from student where uid = 3 for update;

2.共享锁

共享锁（Shared）：又称为S锁，读锁
显式加共享锁：select ... lock in share mode;

select * from student where uid = 3 lock in share mode;

3.X锁和S锁的关系

SS兼容：一个事务对数据对象O加了S锁，则在加锁期间其他事务只能对数据对象O加S锁。
SX互斥：同理
XX互斥：同理
XS互斥：同理

间隙锁

间隙锁（gap lock）：InnoDB会给符合条件的已有数据记录的索引项加锁，可能是排它锁，也可能是共享锁。对于健值在条件范围内但是并不存在的记录，叫做间隙。InnoDB会给这个间隙加锁，这种锁机制就是间隙锁。行锁和间隙锁一起构成next-key lock。

案例：前提使用set global transaction_isolation = 'serializable';命令将事务的隔离级别更改为串行化

范围查询

# 会话1
mysql> begin;   # 1
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where age > 20;    # 3
+-----+-----------+------+--------+
| uid | name      | age  | sex    |
+-----+-----------+------+--------+
|  10 | 小孙      |   21 | male   |
|   2 | NrvCer    |   22 | male   |
|   9 | 小明      |   22 | male   |
|   1 | 关华      |   23 | male   |
|   8 | 刘炜      |   23 | male   |
|   4 | 王丽丽    |   26 | female |
|   7 | 刘小红    |   26 | female |
+-----+-----------+------+--------+
7 rows in set (0.00 sec)

# 会话2
mysql> begin;   # 2
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student(name,age,sex) values('王妍',19,'female' );    # 4 OK
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(name,age,sex) values('王妍',20,'female' ); # 5 不行

mysql> update student set name = '刘丽' where age = 23;       # 6 不行

mysql> insert into student(name,age,sex) values('张莎',21,'female' ); # 7 不行

mysql> insert into student(name,age,sex) values('李燕',20,'female' ); # 8 不行
# 如上所示：在会话1的第三步中，会对age > 20的间隙加间隙锁
# 辅助索引示例：第一排为索引字段age的值，第二排为主键id的值
# 20 | 20 | ...
# 3  | 6  | ...

等值查询

# student表如下所示：
    +-----+-----------+------+--------+
| uid | name      | age  | sex    |
+-----+-----------+------+--------+
|   3 | NrvCer    |   20 | female |
|   6 | 小王      |   20 | male   |
|  21 | 余帅      |   20 | male   |
|  10 | 小孙      |   21 | male   |
|   2 | NrvCer    |   22 | male   |
|   9 | 小明      |   22 | male   |
|   1 | 关华      |   23 | male   |
|   8 | 刘炜      |   23 | male   |
|   4 | 王丽丽    |   26 | female |
|   7 | 刘小红    |   26 | female |
+-----+-----------+------+--------+

# 会话1

mysql> begin; # 1
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where age = 22; # 3
+-----+--------+------+------+
| uid | name   | age  | sex  |
+-----+--------+------+------+
|   2 | NrvCer |   22 | male |
|   9 | 小明   |   22 | male |
+-----+--------+------+------+
2 rows in set (0.00 sec)

# 会话2
mysql> begin; # 2
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student(name,age,sex) values('haha',21,'male'); # 4,不行

mysql> insert into student(name,age,sex) values('haha',22,'male'); # 5，不行

# 如上所示，在会话1的第三步中，会对如下所示的间隙之间加锁。

# 21 | 22 | 22 | 23
# 10 |  2 | 9  | 1

目的：为了防止幻读，满足事务的隔离级别中，串行化隔离级别的要求。

意向共享锁和意向排它锁

1.InnoDB表级锁

现在有一个问题，InnoDB是支持行锁的，行锁的锁粒度比表锁小，发生锁冲突的概率低，并发度高，那么是不是时时刻刻都使用行锁呢？在有些情况下应当使用表级锁：
1. 事务需要更新一张大表的大部分或者全部记录，如果使用默认的行锁，则这个事务的执行效率很低。
2. 事务涉及到多个表，比较复杂，很可能引起死锁造成大量事务回滚。
因此在有些情况下使用表级锁是有必要的。那么如何获取表级锁呢？假设一种情形：一张千万条记录的大表，需要获取这张大表的共享锁，则需要使用某种措施确认这张大表没有被其他事务获取过排它锁以及表中的任何一条记录没有被其他事务获取过排它锁。为了高效获取表级锁，引入了意向锁。

2.意向锁

意向排它锁：简称IX。事务计划给记录行加排它锁。当事务在给一行记录加排它锁之前,InnoDB会先取得该表的IX锁。
意向共享锁：简称IS。事务计划给记录行加共享锁。当事务在给一行记录加共享锁之前,InnoDB会先取得该表的IS锁。
有了意向锁，当一个事务要获取表的X锁时，只需要检查表的IX锁以及表的IS锁是否被其他事务获取；当一个事务要获取表的S锁时，只需要检查表的IX锁是否被其他事务获取

死锁

死锁：两个或者多个事务已经占用资源，并请求锁定对方占用的资源，这时就会发生死锁。
案例：使用默认的隔离级别可重复读，并且使用显式加锁进行死锁的演示。

# 会话1
mysql> begin;  # 1
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where uid= 7 for update;     # 3
+-----+-----------+------+--------+
| uid | name      | age  | sex    |
+-----+-----------+------+--------+
|   7 | 刘小红    |   26 | female |
+-----+-----------+------+--------+
1 row in set (0.00 sec)

mysql> select * from student  where uid = 4 for update;     # 5
+-----+-----------+------+--------+
| uid | name      | age  | sex    |
+-----+-----------+------+--------+
|   4 | 王丽丽    |   26 | female |
+-----+-----------+------+--------+
1 row in set (11.24 sec)


# 会话2
mysql> begin;   # 2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student  where uid = 4 for update;         # 4
+-----+-----------+------+--------+
| uid | name      | age  | sex    |
+-----+-----------+------+--------+
|   4 | 王丽丽    |   26 | female |
+-----+-----------+------+--------+
1 row in set (0.00 sec)

mysql> select * from student where uid= 7 for update;         # 6
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

MySQL实现了死锁检测机制，当发现死锁会让其中一个事务进行回滚，打破死锁。
死锁如何避免呢？获取锁的顺序得当，通常存在多个事务获取多个相同的资源的锁时，应该按照相同的顺序去获取资源的锁，这样就不会有上述案例死锁发生。例如上述案例中，会话2执行第六步再执行第四步。

posted on 2023-10-03 11:15 scrutiny-span 阅读(11) 评论(0) 编辑收藏举报

刷新页面返回顶部

导航