MYSQL知识点回顾：InnoDB的锁机制（一）记录锁

imperfectsam

已于 2022-10-28 14:16:46 修改

阅读量643

点赞数

文章标签： mysql

于 2022-10-28 09:26:29 首次发布

本文链接：https://blog.csdn.net/weixin_36531037/article/details/127515279

版权

1. 前言

数据事务设计遵循ACID的原则。

MySQL数据库提供了四种默认的隔离级别，读未提交(read-uncommitted)、读已提交(或不可重复读)(read-committed)、可重复读(repeatable-read)、串行化(serializable)。

MySQL的默认隔离级别是RR。

2. 锁基本概念

2.1 共享锁和排它锁

InnoDB实现了两种标准行级锁，一种是共享锁(shared locks，S锁)，另一种是独占锁，或者叫排它锁(exclusive locks，X锁)。

S锁允许当前持有该锁的事务读取行。
X锁允许当前持有该锁的事务更新或删除行。

S锁
如果事务T1持有了行r上的S锁，则其他事务可以同时持有行r的S锁，但是不能对行r加X锁。

X锁
如果事务T1持有了行r上的X锁，则其他任何事务不能持有行r的X锁，必须等待T1在行r上的X锁释放。

如果事务T1在行r上保持S锁，则另一个事务T2对行r的锁的请求按如下方式处理：

T2可以同时持有S锁
T2如果想在行r上获取X锁，必须等待其他事务对该行添加的S锁或X锁的释放。

2.2 意向锁-Intention Locks

InnoDB支持多种粒度的锁，允许行级锁和表级锁的共存。例如LOCK TABLES … WRITE等语句可以在指定的表上加上独占锁。
InnoBD使用意向锁来实现多个粒度级别的锁定。意向锁是表级锁，表示table中的row所需要的锁(S锁或X锁)的类型。

意向锁分为意向共享锁(IS锁)和意向排它锁(IX锁)。
IS锁表示当前事务意图在表中的行上设置共享锁，下面语句执行时会首先获取IS锁，因为这个操作在获取S锁：

SELECT ... LOCK IN SHARE MODE

IX锁表示当前事务意图在表中的行上设置排它锁。下面语句执行时会首先获取IX锁，因为这个操作在获取X锁：

SELECT ... FOR UPDATE

事务要获取某个表上的S锁和X锁之前，必须先分别获取对应的IS锁和IX锁。

2.3 锁的兼容性

锁的兼容矩阵如下：

—	排它锁(X)	意向排它锁(IX)	共享锁(S)	意向共享锁(IS)
排它锁(X)	N	N	N	N
意向排它锁(IX)	N	OK	N	OK
共享锁(S)	N	N	OK	OK
意向共享锁(IS)	N	OK	OK	OK

按照上面的兼容性，如果不同事务之间的锁兼容，则当前加锁事务可以持有锁，如果有冲突则会等待其他事务的锁释放。

如果一个事务请求锁时，请求的锁与已经持有的锁冲突而无法获取时，互相等待就可能会产生死锁。

意向锁不会阻止除了全表锁定请求之外的任何锁请求。
意向锁的主要目的是显示事务正在锁定某行或者正意图锁定某行。

3. InnoDB中的锁

常见的锁有Record锁、gap锁、next-key锁、插入意向锁、自增锁等。
下面会对每一种锁给出一个查看锁的示例。

3.1 准备工作

3.1.1 首先开启三个mysql的会话

在这里插入图片描述
这里我是通过xshell打开三个客户端，然后分别对mysql进行会话。

3.1.2 测试用表结构

示例的基础是一个只有两列的数据库表。

mysql> CREATE TABLE test (
id int(11) NOT NULL,
code int(11) NOT NULL, 
PRIMARY KEY(id), 
KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

mysql> INSERT INTO test(id,code) values(1,1),(10,10);

数据表test只有两列，id是主键索引，code是普通的索引(注意，一定不要是唯一索引)，并初始化了两条记录，分别是(1,1),(10,10)。
这样，我们验证唯一键索引就可以使用id列，验证普通索引(非唯一键二级索引)时就使用code列。

3.1.3 查看锁状态的方式

要看到锁的情况，必须手动开启多个事务，其中一些锁的状态的查看则必须使锁处于waiting状态，这样才能在mysql的引擎状态日志中看到。

命令：

mysql> show engine innodb status;

这条命令能显示最近几个事务的状态、查询和写入情况等信息。当出现死锁时，命令能给出最近的死锁明细。

3.1.4 查看当前正在运行的事务的状态

1.查询正在执行的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

这里查出了当前正在执行的事务之后，可以通过trx_mysql_thread_id来杀死进程。

2.杀死进程id

kill 线程ID（trx_mysql_thread_id）

3.2 记录锁 Record Locks

Record锁
Record Lock是对索引记录的锁定。记录锁有两种模式，S模式和X模式。
例如SELECT id FROM test WHERE id = 10 FOR UPDATE;表示防止任何其他事务插入、更新或者删除id =10的行。

记录锁始终只锁定索引。即使表没有建立索引，InnoDB也会创建一个隐藏的聚簇索引(隐藏的递增主键索引)，并使用此索引进行记录锁定。

开启第一个事务，不提交，测试完之后回滚。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

会话一：开始第一个事务，这里我们首先在会话一中执行以上sql
在这里插入图片描述
这里可以看得到因为当前记录没有被锁住，这里马上就能够显示出
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

会话三然后我们在会话三中查看事务的加锁情况

mysql> show engine innodb status\G;

在这里插入图片描述
可以看到有一行被加了锁。由之前对锁的描述可以推测出，update语句给id=1这一行上加了一个X锁。
注意：X锁广义上是一种抽象意义的排它锁，即锁一般分为X模式和S模式，狭义上指row或者index上的锁，而Record锁是索引上的锁。

第一个事务保持原状，不要提交或者回滚，现在开启第二个事务。

会话二：开始第二个事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set id=3 where id=1;

在这里插入图片描述
这里我们可以看到sql语句的执行被阻塞了。

会话三：查看所有事务的状态

mysql> show engine innodb status\G;

在这里插入图片描述
我们看到了这个锁的状态。状态标题是’事务正在等待获取锁’，直译一下’X锁模式锁住了记录’。后面还有一句but not gap意思是只对record本身加锁，并不对间隙加锁。

参考文献：https://www.cnblogs.com/AaronCui/p/10490663.html