InnoDB的锁机制浅析
本文是大而全版(五合一):InnoDB的锁机制浅析(All in One)
如果想分开看可以点击下面链接:
文章总共分为五个部分:
- InnoDB的锁机制浅析(一)—基本概念/兼容矩阵
- InnoDB的锁机制浅析(二)—探索InnoDB中的锁(Record锁/Gap锁/Next-key锁/插入意向锁)
- InnoDB的锁机制浅析(三)—幻读
- InnoDB的锁机制浅析(四)—不同SQL的加锁状况
- InnoDB的锁机制浅析(五)—死锁场景(Insert死锁)
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> 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.2 查看锁状态的方式
要看到锁的情况,必须手动开启多个事务,其中一些锁的状态的查看则必须使锁处于waiting
状态,这样才能在mysql的引擎状态日志中看到。
命令:
mysql> show engine innodb status;
这条命令能显示最近几个事务的状态、查询和写入情况等信息。当出现死锁时,命令能给出最近的死锁明细。
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
事务加锁情况
mysql> show engine innodb status\G;
...
------------
TRANSACTIONS
------------
---TRANSACTION 366811, ACTIVE 690 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 785, OS thread handle 123145432457216, query id 729076 localhost 127.0.0.1 root