MySQL（二十）锁（二）表锁、行锁与页级锁

从操作粒度的锁类型划分：表级锁、行级锁和页锁

为了提高数据库的并发度，每次锁定的数据范围越小越好，理论上只锁定当前操作的数据的方案会获得最大的并发度，但是管理锁也是很耗费资源的事情（涉及锁的获取、检查和释放），因此需要在并发度和性能之间做取舍，这样就产生了锁粒度的概念。

锁所影响的数据的范围称作锁的粒度，锁按照操作粒度从小到大可以划分为行锁、表锁、页面。

1 表级锁（Table Lock）

表级锁会锁定整张表，是MySQL最基本的锁策略，能够避免死锁
并不依赖于存储引擎（所有的存储引擎对于表锁的策略都是一样的）
表锁是开销最小的锁（因为粒度大），但同时出现资源竞争的概率也高，并发性大打折扣

1.1 表级别的S和X锁

在对某个表执行INSERT、SELECT 、UPDATE、DELETE操作的时候，并不会对表添加表级别的X锁或者S锁。而当另一个事务执行DROP ALERT 这类的DDL语句的时候，其他事务的INSERT、SELECT 、UPDATE、DELETE操作会发生堵塞，反之DDL语句也会堵塞。这个过程其实是在service层使用的一种元数据锁来实现的。

一般情况下，不会使用InnoDB存储引擎提供的S锁和X锁，只会在一些特殊情况下，比如说崩溃恢复中用到，在autocommit = 0 、innodb_table_locks = 1时，手动获取InnoDB存储引擎提供的表的S锁和X锁可以这么写：

 LOCK TABLES t READ; --对表t添加表级别的S锁
LOCK TABLES t WRITE; --对表t添加表级别的X锁

不过尽量不要使用InnoDB存储引擎的表上使用Lock Tables，因为这样不会提供什么额外的保护，只是会降低并发性能，InnoDB存储引擎还提供了更加细粒度的行级锁。

举例验证：

 mysql> create table mylock(
    -> id int not null primary key auto_increment,
    -> name varchar(20)
    -> )engine=myisam; -- 也可以使用InnoDB，但不建议这么做
Query OK, 0 rows affected (0.00 sec)
mysql> insert into mylock(name) values ('a');
Query OK, 1 row affected (0.01 sec)

show open tables查看加锁的表：

 mysql> show open tables where in_use > 0;
Empty set (0.00 sec)
 
mysql> lock tables mylock read;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show open tables where in_use > 0;
+------------+--------+--------+-------------+
| Database   | Table  | In_use | Name_locked |
+------------+--------+--------+-------------+
| atguigudb1 | mylock |      1 |           0 |
+------------+--------+--------+-------------+
1 row in set (0.00 sec)
 
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
 
mysql> lock tables mylock write;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show open tables where in_use > 0;
+------------+--------+--------+-------------+
| Database   | Table  | In_use | Name_locked |
+------------+--------+--------+-------------+
| atguigudb1 | mylock |      1 |           0 |
+------------+--------+--------+-------------+
1 row in set (0.00 sec)
 
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show open tables where in_use > 0;
Empty set (0.00 sec)

事务一执行：

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> lock tables mylock read;
Query OK, 0 rows affected (0.00 sec)
-- 事务不能更新自己添加行级读锁的表记录
mysql> update mylock set name = 'a1' where id = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
-- 事务能读取自己添加行级读锁的表记录
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

事务二：

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 事务二可以读取事务一添加读锁的表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)
-- 更新则会堵塞
mysql> update table set name = 'a2' where id = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table set name = 'a2' where id = 1' at line 1
mysql> update mylock set name = 'a2' where id = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> update mylock set name = 'a2' where id = 1 nowait;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nowait' at line 1

剩下就不一一验证了，总结如下：

MyISAM在执行select操作前，会为涉及到的表加读锁，在执行增删改前，会给涉及的表加写锁，（因为MyISAM不支持行级锁）,InnoDB是不会为这个表添加表级别的读锁或者写锁的。

1.2 意向锁

InnDB存储引擎支持多粒度锁，它允许行级锁和表级锁共存冲突
意向锁就是一种不与行级锁冲突的表级锁，表示"某个事务在该表的某些行持有锁或者准备持有锁"

意向锁分为两种：

意向共享锁：表示事务有意向对表中的某些行添加S锁

 -- 事务想要获取行级别S锁，必须先获取表级别的IS锁
SELECT ... FROM t LOCK IN SHARE MODE;

意向排它锁：表示事务有意向对表中的某些行添加X锁

 -- 事务想要获取行级别X锁，必须先获取表级别的IX锁
SELECT ... FROM t FOR UPDATE;

可以看到上面意向锁的语句和普通共享、排他锁的语句是相同的，这也就意味着：

用户无法自己手动添加意向锁、意向锁是由InnoDB存储引擎自己维护的。
在数据行添加锁之前，InnoDB会先获取该数据行对应的表时候持有相应的意向锁，然后再判断加锁是否成功。这么做的好处就在于不需要去每一行全表扫描查看是否持有锁了。具体描述如下：

小结：

InnoDB支持多粒度锁，在特定场景下，行级锁和表级锁可以并存
意向锁之间互不排斥，但除了IS和S锁兼容外，意向锁会和 表级别的共享锁、排它锁互斥
IS和IX是表级别的锁，不会和行级别的S、X锁冲突，只会和上面的表级别的锁冲突
意向锁在保证并发性的前提下，实现了表锁和行锁共存，并且满足事务隔离性的要求

🌟 1.3 自增锁（AUTO-INC锁）

我们常常对表的某个列添加auto_increment属性，意味着在书写sql语句的时候不需要为其赋值，而mysql会自动为其赋值为递增的值，但是多个事务执行插入操作的时候，肯定会有一些并发性问题，自增锁就解决了这一问题。

首先，所有的数据插入方式都分为三类：

Simple Inserts：简单插入。可以预先知道插入行数的语句。
Bulk Inserts：批量插入。预先不知道插入行数的语句（比如包含嵌套子查询），InnoDB每处理一行，就为AUTO_INCREMENT列分配一个新值。
Mixed-mode inserts：指定了部分新行的自增id。

自增锁的工作原理：

AUTO-INSERT锁是向包含AUTO_INCREMENT的列的表添加数据时需要获取的一种特殊的表级锁
当事务在执行插入语句的时候，就需要获取AUTO-INSERT锁，然后为每一条待添加的记录分配一个递增的值，添加完成后释放AUTO-INSERT锁。
一个事务在持有AUTO-INSERT锁的过程中，其他事务的插入语句都要抢占这个表级锁而被堵塞，因此并发性不高
可以通过innodb_autoinc_lock_mode来修改锁定机制
- innodb_autoinc_lock_mode=0：传统模式，即上面一个事务在持有AUTO-INSERT锁的过程中，其他事务的插入语句都要抢占这个表级锁而被堵塞
- innodb_autoinc_lock_mode=1：Bulk Inserts仍然使用AUTO-INC表级锁，而Simple Inserts由于预先知道插入行数，所以使用一种mutex（轻量锁）来控制自动递增，这种轻量锁不会等到语句结束再释放，而是只在分配过程的持续时间内保持，并且如果一个事务持有AUTO-INSERT锁则Simple Inserts想要持有mutex锁也需要等待。
- innodb_autoinc_lock_mode=2：交错模式为默认模式。我是看不懂这下面的话，了解一下就行

1.4 元数据锁（Meta-Data-Lock锁）P176

MySQL5.5引入了meta data lock，简称MDL锁，属于表锁的范畴
当事务对一个表进行增删改查操作的时候，需要添加MDL读锁；进行表结构变更的时候，加MDL写锁
读锁之间不互斥，读写锁之间、写锁之间是互斥的，用来保证表结构的一致性问题
不需要显示使用，会在访问一个表的时候自动加上

2 行级锁

行锁也称记录锁，就是锁住某一行的记录
MySQL服务器层面没有实现行级锁，只在存储引擎层实现
行锁的优点是粒度小，发生冲突的概率低，并发度高
缺点是粒度小造成的锁数量多，因此开销比较大，加锁比较慢，存在死锁问题

InnoDB与MyISAM的主要不同：

InnoDB支持事务
InnoDB支持行级锁

2.1 记录锁（Record Locks）

添加记录所的X锁和S锁语句如下：

 -- S锁
select * from t where id = x in share mode;
select * from t where id = x for share;
-- X锁
select * from t where id = x for update;

2.2 间隙锁

MySQL在Repeatable Read隔离级别下是可以解决幻读问题的，解决的方案有两种：

通过MVCC

读操作利用多版本并发控制（MVCC），写操作加锁

MVCC就是生成一个ReadView，通过ReadView能够找到符合条件的记录版本（历史版本由undo log提供查询），查询语句执行查询已经提交的事务做出的更改，对于没由提交的事务和ReadView创建之后的事务做出的更改是看不到的。而写操作肯定是针对的最新版本的记录，因此读记录的历史版本和写操作的最新记录版本并不会冲突，也就是采用MVCC时，读写操作并不会冲突。
普通的SELECT语句在READ COMMITTED 和 REPEATABLE READ隔离级别下的读操作就是利用MVCC进行的读
- READ COMMITTED：由于不会读取没有提交的事务修改的数据版本，因此避免了脏读问题
- REPEATABLE READ：由于不会读取Read View创建之后的事务更改的数据(一个事务只有在第一次执行SELECT语句才会生成一个Read View，之后的SELECT语句都在复用)，因此避免了可重复读和幻读问题
通过加锁的方式

读、写操作都采用加锁的方式

在一些业务场景中，不允许读取数据的历史版本，即每次都需要去读取磁盘中最新的数据，这样也就意味着读操作也需要和写操作一样排队执行。

如此一来，脏读和不可重复读问题都得到了解决，因为读操作和写操作的串行执行，不会出现一个事务读取另一个未提交事务的数据以及一个事务读取过程中另一个事务修改数据提交导致前一个事务前后读取数据不一致的情况（第二个事务根本无法开始）。

🌟 但是，幻读问题有些尴尬，试想一个事务在进行读操作，因此给表中的一定范围内的数据加锁，但是另一个事务要写的这个幻影数据可不在这个范围里面，也就是两个读写操作并不会冲突，仍然会出现幻读问题
正如上面所说，由于幻影记录并不存在，所以无法给这些幻影记录添加记录锁，因此针对幻影记录的插入提出了间隙锁(Lock_gap)，如下图id为8的记录添加了Lock_gap锁，就意味着不允许别的记录在id值为8的记录前面添加记录，其实就是(3, 8)这个开区间内不允许插入，直到拥有这个间隙锁的事务提交释放掉锁之后。

共享gap锁和独占gap锁的作用是相同的，如果对一条记录添加了间隙锁，并不会影响其他事务对这条记录添加记录锁或者间隙锁。
间隙锁存在的条件是必须在RR隔离级别下并且检索条件必须有索引否则就是表锁了
可以通过对数据页的两个伪纪录Infimun(指向页面最小记录)和Supremum(指向页面最大记录)实现阻止其他事务在左右无限区间内插入数据

sql语句为：
```
 select * from student1 where id > 20 for share;
```
间隙锁测试：

 insert into student1 values 
(1, 'zhangsan', 'yiban'), 
(3, 'lisi', 'erban'), 
(8, 'wangwu', 'erban'), 
(15, 'zhaoliu', 'erban'), 
(20, 'zhaoqi', 'sanban');

事务一执行：

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from student1 where id = 5 for share;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> select * from student1 where stu_no = 5 for share;
Empty set (0.00 sec)
 
mysql>

事务二执行，发现被堵塞：

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into student1 values(4, 'liu', 'yiban');
...

间隙锁导致的死锁问题

事务一获取一个间隙锁

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from student1 where stu_no = 5 for share;
Empty set (0.00 sec)
 
mysql> insert into student1 values(6, 'liu', 'liuban');
Query OK, 1 row affected (6.73 sec)
 
mysql>

事务二执行相同区间的间隙锁，可以看到发生了死锁

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from student1 where id = 5 fro share;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fro share' at line 1
mysql> select * from student1 where stu_no = 5 fro share;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fro share' at line 1
mysql> select * from student1 where stu_no = 5 for share;
Empty set (0.00 sec)
 
mysql> insert into student1 values(6, 'liu', 'liuban');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

然后再回来看事务一执行成功了，因为发生死锁之后，事务二释放了持有的间隙锁

 mysql> insert into student1 values(6, 'liu', 'liuban');
Query OK, 1 row affected (6.73 sec)

2.3 临键锁(Next-key Locks)

临键锁(next-key locks)，又称LOCK_ORIDINARY，
临键锁是在InnoDB存储引擎、隔离级别在可重复读情况下默认使用的数据库锁
临键锁的本质是记录锁和间隙锁的组合，既能对行记录添加记录锁，又能在记录插入前面的间隙添加间隙锁

sql语句如下，会针对stu_no = 8的记录添加写锁，针对区间(8, 15)添加间隙锁

 mysql> select * from student1 where stu_no>= 8 and stu_no < 15 for update;

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from student1 where stu_no>= 8 and stu_no < 15 for update;
+--------+--------+-------+
| stu_no | name   | class |
+--------+--------+-------+
|      8 | wangwu | erban |
+--------+--------+-------+
1 row in set (0.00 sec)
 
mysql>

 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from student where stu_no = 8 for share;
Empty set (0.37 sec)
-- 获取记录锁堵塞
mysql> select * from student1 where stu_no = 8 for share;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
-- 间隙锁堵塞
mysql> insert into student1 values(12 , '12', '12');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

2.4 插入意向锁（Insert Intention Lock）

插入意向锁指的是一个事务在插入的过程中，检测到别的事务在插入位置添加了gap锁而进入堵塞，因此由于InnoDB存储引擎规定而在内存中生成的一个锁结构
插入意向锁是一种特殊的间隙锁，可以锁定开区间内的部分数据
插入意向锁互不排斥，只要记录本身（主键、唯一索引等）不冲突，那么事务就不会出现冲突等待

3 页级锁

页级锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要高
开销介于表锁和行锁之间，并发度一般

每个层级的锁数量是有限的，因为锁会占用内存空间，锁空间大小是有限的。当某个层级的锁数量超过了这个层级的阈值之后，就会进行锁的升级，即采用更高粒度的锁，比如行锁升级为表锁，好处是空间占有变小，但并发度也降低了。

上一篇MySQL（十九）MySQL事务日志（二）UndoLog

下一篇MySQL（二十一）MVCC多版本并发控制

posted @ 2023-05-09 13:56 Tod4 阅读(157) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

历史上的今天：
2022-05-09 【MyBatis】分页插件

Tod4の代码零碎

MySQL（二十）锁（二）表锁、行锁与页级锁