03【锁机制】
一、MySQL锁机制
锁是计算机协调多个进程或线程并发访问某一资源的机制。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要。
MySQL用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。这些锁统称为悲观锁(Pessimistic Lock)。
MySQL中的锁其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用表级锁。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
1.1 锁的分类
1.1.1 按操作分
- 读锁(共享锁)：Shared Locks（S）
- 写锁(排它锁)：Exclusive Locks（X）
- 意向共享锁：Intention Shared Locks（IS）
- 意向排他锁：Intention Exclusive Locks（IX）
- 自增锁：（AUTO-INC Locks）
1.1.2 按粒度分
- 表锁：Table Locks
- 行锁：Row Locks
- 页锁：Page Locks
1.1.3 按算法划分
- 记录锁：Record Locks
- 间隙锁：Gap Locks
- 临键锁：Next-key Locks
1.2 MyISAM引擎锁
MyIsam引擎默认使用的是表锁。
对MyISAM表的读操作，触发的是共享锁。即：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
对MyISAM表的写操作，触发的是排它锁。即：则会阻塞其他用户对同一表的读和写操作；
Tips：MyISAM表的读操作与写操作之间，以及写操作之间是串行的！
MySQL的表级锁有两种模式：
- 表共享读锁（读锁）（Table Read Lock）：允许本线程/进程读、不允许本线程写，其他线程可读，不可写
- 表独占写锁（写锁）（Table Write Lock）：允许本线程读、写操作，其他线程不可读、不可写
建立一张使用MyISAM引擎的数据库表：
drop table if exists test1;
CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) ,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM ;
INSERT INTO test1 VALUES(1,'aaa');
INSERT INTO test1 VALUES(2,'bbb');
查看表是否被锁过
show open tables;
对表加锁（读锁/写锁）
lock table test1 read;
lock table test1 write;
对表解锁
unlock tables;
1.2.1 MySIAM-读锁
在MySIAM引擎中，当某个线程获取到表的读锁时，当前线程除了读取数据之外，不可进行写操作（insert、update、delete）， 其他线程可以进行读的操作，不可进行写的操作。
- 示例
给test1表加上读锁
lock table test1 read;
当前线程只能执行query操作，但是只能查询本表，其他表不能查询，不能执行insert、update、delete操作
其他线程可以读取该表数据，但是insert、update、delete会出现阻塞状态。
| session-01 | session-02 |
|---|---|
| lock table test1 read; | |
| select * from test1; -- 本线程可读 | |
| select * from test1; -- 其他本线程可读 | |
| update test1 set name='aaa' where id=1; -- 本线程不可写 | |
| update test1 set name='aaa' where id=1; -- 其他线程不可写 |
Tips：本线程可读、不可写，其他线程也是可读、不可写
- 关于MyISAM引擎锁的注意事项：
在MySIAM引擎中，对表进行select、insert、update、delete等操作都会自动加上表锁，其中select操作加上的是读锁，insert等操作加上的是写锁，整个过程不需要用户干预，因此，用户一般不需要直接用lock table命令给MyISAM表显式加锁。
当使用LOCK TABLE时，不仅需要一次锁定用到的所有表，而且，同一个表取过多少别名，也要对那些别名进行锁定，否则也会出错！
-- 给test1表加读锁
lock table test1 read;
-- Table 'a' was not locked with LOCK TABLES 提示表别名"a"没有被锁住
select * from test1 a where a.id=1;
-- 释放锁
unlock tables;
-- 取别名
lock table test1 as a read;
select * from test1 a where a.id=10; -- 一切正常
unlock tables;
-- 取多个别名
lock table test1 as a read,test1 b read;
select * from test1 a; -- 正常
select * from test1 b; -- 正常
select * from test1 c; -- Table 'c' was not locked with LOCK TABLES
unlock tables;
1.2.2 MySIAM-写锁
在MySIAM引擎中，当某个线程获取到表的写锁时，只有持有该锁的线程才可以对表进行进行操作，其他线程对表进行读、写操作时都会被等待，直到锁被释放。
- 示例
给student表加写锁
lock table test1 write;
当前线程可以执行update、delete、insert、select等操作，其他线程以上操作均不能执行，需要等到线程释放锁
| session-01 | session-02 |
|---|---|
| lock table test1 write; -- 写锁 | |
| select * from test1; -- 本线程可读 | |
| select * from test1; -- 其他线程不可读 | |
| update test1 set name='aaa' where id=1; -- 本线程可写 | |
| update test1 set name='aaa' where id=1; -- 其他线程 不可写 |
Tips：本线程可读、可写，其他线程不可读、不可写；
1.2.3 MyISAM表锁应用场景
给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。
- SQL语句：
CREATE TABLE `t_orders` (
`id` int(11) NOT NULL COMMENT '订单id',
`order_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单名称',
`total` double(255, 0) NULL DEFAULT NULL COMMENT '订单总金额',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `t_order_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单详情id',
`order_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单详情名称',
`subtotal` double(255, 0) NULL DEFAULT NULL COMMENT '订单详情金额',
`order_id` int(11) NULL DEFAULT NULL COMMENT '所属订单',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 插入订单数据
insert into t_orders values(1,'双十一购物',0);
insert into t_orders values(2,'618购物',0);
-- 在订单详情插入一条数据
insert into t_order_detail values(1,'神舟笔记本',4999,1);
-- 同时更改订单表的订单金额
update t_orders set total=total+4999 where id=1;
-- 又插入一条记录
insert into t_order_detail values(2,'华为手机',2888,1);
-- 更改订单表中的订单金额
update t_orders set total=total+2888 where id=1;
有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条
- SQL语句：
-- 统计订单表金额
select sum(total) from t_orders;
-- 统计订单详情表金额
select sum(subtotal) from t_order_detail;
这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail表可能已经发生了改变。因此，正确的方法应该是：
-- 给两张表都加上读锁
Lock tables t_orders read, t_order_detail read;
select sum(total) from t_orders;
select sum(subtotal) from t_order_detail;
Unlock tables;
1.2.4 MyISAM的并发插入
MyISAM表的读写操作之间是串行的，在一定的条件下，MyISAM表也支持查询和插入操作的并发进行，在MyISAM引擎中有一个系统变量concurrent_insert，专门用于控制其并发插入的行为
- 查询当前
concurrent_insert的值：
show variables like 'concurrent_insert';
- concurrent_insert为0时，为NEVER状态，不允许并发插入
- concurrent_insert为1时，为AUTO状态，如果MyISAM表中没有空洞（即表的中没有被标记删除的行），MyISAM允许在一个进程读表的同时，另一个进程从数据文件尾部插入。这也是MySQL的默认设置。
- concurrent_insert为2时，为ALWAYS状态，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
Tips：在MyISAM并发插入时，推荐值为2（ALWAYS）
修改会话级别：
set global concurrent_insert=2; -- 重启服务后失效
修改配置文件：
[mysqld]
concurrent_insert=2
- 案例测试：
drop table if exists test2;
CREATE TABLE `test2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) ,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM ;
INSERT INTO test2 VALUES(10,'aa');
【并发插入测试】
| session-01 | session-02 |
|---|---|
| lock table test2 read local; | |
| insert into test2 values(5,'bb'); | |
| insert into test2 values(15,'bb'); | |
| update test2 set name='a' where id=1; -- 阻塞 | |
Tips：
- 1）
read local选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾插入记录- 2）并发插入
AUTO状态指的是允许在数据文件末尾进行插入，不是指表末尾；- 3）只有MyISAM表才有并发插入，InnoDB表不支持；
1.2.5 MyISAM的锁调度
MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？
答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。
我们可以通过指定UPDATE、DELETE语句的low_priority_updates属性，降低该语句的优先级。也可以通过指定SELECT语句的high_priority属性来提高该语句的优先级；
Tips：SELECT语句只能提高优先级，UPDATE、DELETE语句只能降低优先级，INSERT语句既可以降低也可以提高优先级；
查看MyISAM的读/写优先级：
show variables like 'low_priority_updates';
更改low_priority_updates
set low_priority_updates=1; -- 降低全局写操作的优先级
update low_priority userinfo set username='1' where id=1; -- 降低本次修改操作的优先级
delete low_priority from userinfo where id=1; -- 降低本次删除操作的优先级
select high_priority * from userinfo; -- 提升本次查询操作的优先级
虽然上面的方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。
另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的写锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
show variables like 'max_write_lock_count';
看到上面的值就知道了，在读写竞争时，读锁毫无优势。
1.2.6 lock table语句
lock table等语句是MySQL服务器提供的API，这意味着任何存储引擎都可以使用这些API来锁表；Innodb也不例外；
测试：
drop table if exists test3;
CREATE TABLE `test3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) ,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = innodb ; -- InnoDB存储引擎
INSERT INTO test3 VALUES(1,'aa');
INSERT INTO test3 VALUES(2,'bb');
【案例测试】
| session-01 | session-02 |
|---|---|
| lock table test1 read; -- 读锁 | |
| update test1 set name='aaa' where id=1; -- 阻塞 |
Tips：我们知道InnoDB是支持行锁与表锁的，使用
lock table等语句给InnoDB表上锁，无疑是增大了锁的粒度（直接提升为表锁）；因此InnoDB表很少使用lock table等语句来给表上锁；
1.3 InnoDB 引擎锁
InnoDB与MyISAM的最大不同有两点：
一是支持事务、外键；
二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
- 共享锁（S）：又称读锁。允许一个事务去读一行，阻止其他事务获取该行的排它锁
- 排他锁（Ｘ）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务获取排它锁、共享锁
查看InnoDB行锁的使用信息：
show status like 'innodb_row_lock%';
**Innodb_row_lock_current_waits**：当前有多少线程正在等待行锁**Innodb_row_lock_time**：行锁等待时间**Innodb_row_lock_time_avg**：行锁平均等待时间**Innodb_row_lock_time_max**：行锁最大等待时间**Innodb_row_lock_waits**：总的行锁等待数
【创建测试表】
-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
money DOUBLE
);
-- 添加数据
INSERT INTO account (name, money) VALUES ('a', 1000), ('b', 1000);
1.3.1 InnoDB-读锁
在InnoDB引擎中，某个事务获取共享锁之后，会阻止其他事务获取排它锁，但是其他事务可以获取共享锁。值得注意的是，在InnoDB引擎中，进行普通的查询操作是不会触发共享锁的，必须显示的加上**lock in share mode**，才会加上共享锁。
Tips：本线程可读、可写，其他线程可读、不可写；
- 共享锁（S）：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
【测试案例-01】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; |
| select * from account where id=1;
-- 不会加上共享锁，必须要使用local in share mode | |
| | update account set money=10 where id=1; -- 不阻塞 |
| rollback; | |
| | rollback; |
【测试案例-02】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; | |
| select * from account where id=1 lock in share mode; | |
| update account set money=10 where id=1; -- 阻塞 | |
| select * from account where id=1 lock in share mode; -- 不阻塞 | |
| rollback; | |
| rollback; |
【测试案例-03】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; | |
| select * from account where id=1 lock in share mode; |
| update account set money=10 where id=1;
-- 不阻塞，但是锁升级为了排它锁 | |
| | update account set money=10 where id=1; -- 阻塞 |
| | select * from account where id=1 lock in share mode; -- 阻塞 |
| rollback; | |
| | rollback; |
1.3.2 InnoDB-写锁
在InnoDB中，排它锁允许当前排它锁事务更新数据，阻止其他事务获取排它锁、共享锁，获取排它锁可以在查询语句后面显示的加上for update，来获取排它锁，触发任何修改（update/delete/insert）操作也会获取该行的排它锁
Tips：本线程可读、可写，其他线程不可读不可写；
在InnoDB中，执行任何的增删改语句都会自动的触发排它锁，不需要与查询语句一样显示的加上其他参数。另外，在InnoDB中允许给任意一条select语句加上排它锁，只需要在select语句后加上for update即可。
- 排他锁（X）：
SELECT * FROM table_name WHERE ... FOR UPDATE
在InnoDB引擎中，获取到排它锁的事务将会阻止其他事务获取排它锁、共享锁；
【测试案例-01】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; | |
| update account set money=10 where id=1; -- 获取排它锁 | |
| update account set money=10 where id=1; -- 阻塞 | |
| select * from account where id=1 lock in share mode; -- 阻塞 | |
| select * from account where id=1 for update; -- 阻塞 | |
| rollback; | |
| rollback; |
【测试案例-02】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; | |
| select * from account where id=1 for update; -- 获取排它锁 | |
| update account set money=10 where id=1; -- 阻塞 | |
| select * from account where id=1 for update; -- 阻塞 | |
| select * from account where id=1 lock in share mode; -- 阻塞 | |
| rollback; | |
| rollback; |
1.3.3 InnoDB行锁的实现
InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
Tips：在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。
【测试案例-01】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; |
| -- 表级共享锁
select * from account where name='a' lock in share mode; | |
| | select * from account where name='b' for update; -- 表级排它锁,阻塞 |
| rollback; | |
| | rollback; |
Tips：InnoDB如果没有用到索引则默认使用表锁
给name列创建索引：
-- 添加索引
create index idx_name on account(name);
【测试案例-02】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; |
| -- 行级共享锁
select * from account where name='a' lock in share mode; | |
| | -- 行级排它锁,非阻塞
select * from account where name='b' for update; |
| rollback; | |
| | rollback; |
1.3.4 InnoDB死锁
如果一个事务中请求了某表的读锁，另一个事务请求了某表的写锁，势必会被阻塞，于此同时在第一个事务中（请求读锁的事务）再次请求写锁，那么这样一来两个客户端都在等待对方的锁释放，造成死锁；
| Client-01 | Client-02 |
|---|---|
| begin; -- 开启事务 | |
| begin -- 开启事务 | |
| select * from account where id=1 lock in share mode; | |
| select * from account where id=1 lock in share mode; |
| -- 阻塞，锁升级为排它锁
select * from account where id=1 for update; | |
| | select * from account where id=1 for update; --触发死锁 |
Tips：在InnoDB中，死锁的一方将所有的锁都释放；
1.4 锁小结
1.4.1 MySIAM引擎
1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用对同一表查询和插入的锁争用问题。
3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。
1.4.2 InnoDB 引擎
（1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会默认使用表锁。
（2）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。
在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：
- 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
- 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
二、MySQL高级锁
2.1 意向锁
2.1.1 意向锁的概念
意向锁的存在是为了协调行锁和表锁的关系，用于优化InnoDB加锁的策略。意向锁的主要功能就是：避免为了判断表是否存在行锁而去全表扫描。
意向锁是由InnoDB在操作数据之前自动加的，不需要用户干预；
- 意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
- 意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁
场景举例（假设此时没有意向锁）：假设事务A锁住了表中的一行记录，之后，事务B申请整个表的写锁。数据库需要避免这种冲突，需要让B的申请被阻塞，直到A释放了行锁。数据库要怎么判断这个冲突呢？
- 方式1）：判断表中的每一行是否已被行锁锁住（效率非常低）
- 方式2）：直接判断整表是否已被其他事务用表锁锁表
意向锁就是在这个时候发挥作用的，有了意向锁。在意向锁存在的情况下，事务A必须先申请表的意向共享锁（表级锁），成功后再申请一行的行锁。下次事务B去申请表的排它锁时，发现有意向共享锁，说明表中肯定有某些行被锁住了，事务B将会阻塞；
当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定；
（1）如果意向锁是行锁，则需要遍历每一行数据去确认；
（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。
因此，意向锁是表级别的；
2.1.2 意向锁的应用
测试IS和IX之间是共享的，意向锁（共享和排他）和表级别的X锁是冲突的；
- 测试数据：
drop table if exists test4;
CREATE TABLE `test4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) ,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = innodb ;
INSERT INTO test4 VALUES(1,'aa');
INSERT INTO test4 VALUES(2,'bb');
INSERT INTO test4 VALUES(3,'cc');
【案例测试】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; | |
| select * from test4 where id=1 lock in share mode; -- 申请这行的共享锁 | |
| select * from test4 for update; -- 申请整表的排它锁（阻塞） |
示意图：
①事务A申请整个表的IS锁，成功。
②事务A申请id=2这一行的S锁，成功。
③事务B申请整个表的IX锁，成功。因为IS和IX锁是兼容的，并且IX锁和行级别的S锁也是兼容的。
④事务B申请整个表的X锁，失败。因为表级别的X锁和IS锁是冲突的。
Tips：有了意向锁，在事务B申请整表的排它锁时，直接判断该表有没有意向锁即可（判断一次）。MySQL就可以很轻松判断这个表中是否记录被锁住了；
2.1.3 意向锁与X/S锁的关系
我们之前说过，事务A在锁定一行记录时，会先加上意向锁（表级别），之后事务B申请整个表的排它锁时，先加上意向排它锁，发现该表已经被加上意向锁了，但是意向锁之间是兼容的，可以申请成功，之后事务B尝试申请表级别排它锁，申请锁失败，被阻塞；因为表级别的排它锁和意向锁是冲突的；
需要注意的是，虽然表级别的排它锁和意向锁之间的冲突的，但是行级别的排它锁和意向锁之间确实兼容的；我们观察下面案例：
【测试案例】
测试意向锁和行级S/X锁是兼容的，并且所有的意向锁直接都是兼容的
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; |
| -- 申请这行的共享锁
select * from test3 where id=1 lock in share mode; | |
| | select * from test3 where id=2 for update; -- 申请这行的排它锁 |
示意图：
①事务A申请整个表的IS锁，成功。
②事务A申请id=2这一行的S锁，成功。
③事务B申请整个表的IX锁，成功。因为IS和IX锁是兼容的，并且IX锁和行级别的S锁也是兼容的。
④事务B申请整个id=3这一行的X锁，成功。因为行级别的X锁和IS锁是兼容的。
Tips：意向锁与行级的S/X锁之间的兼容的
关系如下：
X |
IX |
S |
IS |
|
|---|---|---|---|---|
X |
||||
| （表级） | Conflict | Conflict | Conflict | Conflict |
S |
||||
| （表级） | Conflict | Conflict | Compatible | Compatible |
X |
||||
| （行级） | Conflict/Compatible | Compatible | Conflict/Compatible | Compatible |
S |
||||
| （行级） | Conflict/Compatible | Compatible | Compatible | Compatible |
注意：意向锁不会与行级的S / X锁互斥，只会与表级别的S / X锁互斥；而S/X锁之间的互斥要看两个事务是否是操作同一行。
获取行级X锁时，行级X锁不会因为有别的事务上了IX而阻塞，mysql是允许多个行级X锁同时存在的，只要他们不是针对相同的数据行。
2.2 记录锁
2.2.1 记录锁的条件
Record Lock：记录锁，在使用主键或唯一索引精确匹配行时触发的行级锁；
需要注意的是：记录锁锁的触发查询条件必须为精确匹配且命中数据，不能为 > 、 <、 like 、 between...end 等，否则将会触发间隙锁或临键锁；
Tips：记录锁与后面学习的间隙锁和临键锁不同，记录锁会与所有符合条件的锁互斥；而间隙锁和临键锁只会与符合条件的insert互斥；
记录锁的条件：
- 1）必须是主键索引或者唯一索引
- 2）查询语句条件必须为精确匹配且命中数据
- 3）触发间隙锁和临键锁时可能会触发记录锁
2.2.2 记录锁测试
【创建测试表】
drop table if exists t1;
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT=1;
INSERT INTO `t1`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t1`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t1`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t1`(`id`, `num`) VALUES (20, 20);
【测试案例-01】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; |
| -- 触发记录锁（使用了索引）
select * from t1 where id=5 for update; | |
| | select * from t1 where id=2 for update; -- 不阻塞 |
| | select * from t1 where id=7 for update; -- 不阻塞 |
| | update t1 set num=1 where id=7; -- 不阻塞 |
| | delete from t1 where id=7; -- 不阻塞 |
| | select * from t1 where id=5 for update; -- 阻塞 |
| | update t1 set num=1 where id=5; -- 阻塞 |
| | delete from t1 where id=5; -- 阻塞 |
| rollback; | |
| | rollback; |
上述案例中触发的是记录锁，锁住的记录只有id=5的这一条记录；
2.3 间隙锁
2.2.1 间隙锁概述
Gap Lock：间隙锁，当我们用范围条件而不是等值条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。
间隙锁（Gap Lock）是Innodb在可重复读提交下为了解决幻读问题时引入的锁机制，因此，间隙锁只会阻塞insert类型的排它锁；
- 创建测试表，添加数据
drop table if exists t1;
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT=1;
INSERT INTO `t1`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t1`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t1`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t1`(`id`, `num`) VALUES (20, 20);
2.3.2 间隙锁测试
【测试案例-01】
当发生范围加锁时，InnoDB将符合范围的间隙全部加上间隙锁，这些被间隙锁锁住的间隙将不能被insert（被阻塞）
但可以执行update/delete/for update/lock in share mode等操作；
Tips：间隙锁的主要目的就是为了防止幻读，因此只会阻塞insert语句；
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; | |
| select * from t1 where id>10 for update; | |
| insert into t1 values(11,0); -- 处于间隙中,阻塞 | |
| insert into t1 values(18,0); -- 处于间隙中,阻塞 | |
| insert into t1 values(8,0); -- 不处于间隙,不阻塞 | |
| select * from t1 where id=11 for update; -- 不是insert语句,不阻塞 | |
| update t1 set num=1 where id=11; -- 不是insert语句,不阻塞 | |
| delete from t1 where id=11; -- 不是insert语句,不阻塞 | |
| rollback; | |
| rollback; |
【测试案例-02】
间隙锁不一定要使用范围加锁，有时候等值查询一样可以触发间隙锁；
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; | |
| select * from t1 where id=100 for update; -- 触发间隙锁 | |
| insert into t1 values(100,0); -- 处于间隙中,阻塞 | |
| select * from t1 where id=100 for update; -- 不是insert语句,不阻塞 | |
| update t1 set num=1 where id=100; -- 不是insert语句,不阻塞 | |
| delete from t1 where id=100; -- 不是insert语句,不阻塞 | |
| rollback; | |
| rollback; |
2.2.3 间隙锁中的记录锁
当发生范围加锁时，如果范围内有符合数据的记录，那么这些记录加的不是间隙锁，而是记录锁；
只有那些间隙才会被加上间隙锁，间隙锁只会阻塞insert，但记录锁不是；
【测试案例】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; | |
| select * from t1 where id>10 for update; | |
| insert into t1 values(11,0); -- 处于间隙中,阻塞 | |
| insert into t1 values(18,0); -- 处于间隙中,阻塞 | |
| insert into t1 values(8,0); -- 不处于间隙,不阻塞 | |
| select * from t1 where id=11 for update; -- 不是insert,不阻塞 | |
| select * from t1 where id=15 for update; -- 不是间隙锁,而是行锁,阻塞 | |
| rollback; | |
| rollback; |
InnoDB使用间隙锁的目的，目的是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了id大于10的任何记录，那么本事务如果再次执行上述语句，就可能会发生幻读（某些情况通过MVCC快照已经解决）；
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
2.4 临键锁
2.4.1 临键锁的区间测试
临键锁（Next-Key Lock）：临键锁是查询时InnoDB根据查询的条件而锁定的一个范围，这个范围中包含有间隙锁和记录锁；临键锁=间隙锁+记录锁。
其设计的目的是为了解决Phantom Problem(幻读)；主要是阻塞insert，但由于临键锁中包含有记录锁，因此临键锁所锁定的范围内如果包含有记录，那么也会给这些记录添加记录锁，从而造成阻塞除insert之外的操作；
Tips：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。
临键锁锁住的区间为：记录+区间（左开右闭）
左开右闭：不锁住左边，锁右边
测试表：
drop table if exists t2;
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(11) ,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;
INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);
-- 创建普通索引
create index idx_num on t2(num);
-- 创建唯一索引
create unique index idx_num on t2(num);
-- 删除索引
drop index idx_num on t2;
- 区间示意图：
Tips：间隙锁只会阻塞insert，记录锁会阻塞任意的锁（单要注意排他锁和共享锁的关系）；
【测试案例-01-间隙锁】
临键锁的触发不仅把条件区间（11-16）的数据行锁住了，还把临键的数据行统统锁住了；锁住的区间为：(10,15]、(15,20]
锁住的id范围：10（不含）~20（含）
| session1 | session2 |
|---|---|
| begin; | |
| begin; | |
| select * from t2 where id>11 and id<16 for update; | |
| insert into t2 values(10,0); -- 不阻塞 | |
| insert into t2 values(11,0); -- 阻塞 | |
| insert into t2 values(15,0); -- 阻塞 | |
| insert into t2 values(16,0); -- 阻塞 | |
| insert into t2 values(18,10); -- 阻塞 | |
| insert into t2 values(20,0); -- 阻塞 | |
| insert into t2 values(21,0); -- 不阻塞 | |
| rollback; | |
| rollback; |
【案例测试-02-记录锁】
临键锁是间隙锁+记录锁的；上述案例中测试了临键锁中的间隙锁，这次我们来测试一下临键锁中的记录锁；
| session1 | session2 |
|---|---|
| begin; | |
| begin; | |
| select * from t2 where id>11 and id<16 for update; | |
| select * from t2 where id=12 for update; -- 间隙锁,不阻塞 | |
| select * from t2 where id=15 for update; -- 记录锁,阻塞 | |
| select * from t2 where id=17 for update; -- 间隙锁,不阻塞 | |
| select * from t2 where id=20 for update; -- 记录锁,阻塞 | |
| rollback; | |
| rollback; |
2.4.2 临键锁-普通列
我们刚刚测试的是以主键索引进行测试，如果采用不同的列（普通列、普通索引、唯一索引/主键索引等），则临键锁中的间隙锁和记录锁住的内容大不相同；
如果查询的是普通列，那么触发的临键锁为：表级别的间隙锁+表级别的记录锁
- 测试表：
drop table if exists t2;
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(11) ,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;
INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);
1）间隙锁
【案例测试-01-表级别间隙锁】
| session1 | session2 |
|---|---|
| begin; | |
| begin; | |
| select * from t2 where num=11 for update; | |
| insert into t2 values(null,3); -- 阻塞 | |
| insert into t2 values(null,5); -- 阻塞 | |
| insert into t2 values(null,8); -- 阻塞 | |
| insert into t2 values(null,10); -- 阻塞 | |
| insert into t2 values(null,18); -- 阻塞 | |
| insert into t2 values(null,21); -- 阻塞 | |
| rollback; | |
| rollback; |
Tips：innoDB查询如果没有使用到索引默认触发表级临键锁，把所有的间隙都锁住了
2）记录锁
以普通列查询除了会触发表级别的临键锁外，同时还会触发表级别的记录锁；
【案例测试-02-表级别记录锁】
| session1 | session2 |
|---|---|
| begin; | |
| begin; | |
| select * from t2 where num=11 for update; | |
| select * from t2 where id=3 for update; -- 间隙锁,不阻塞 | |
| select * from t2 where id=5 for update; -- 记录锁,阻塞 | |
| select * from t2 where id=8 for update; -- 间隙锁,不阻塞 | |
| select * from t2 where id=15 for update; -- 记录锁,阻塞 | |
| select * from t2 where id=18 for update; -- 间隙锁,不阻塞 | |
| select * from t2 where id=20 for update; -- 记录锁,阻塞 | |
| rollback; | |
| rollback; |
2.4.3 临键锁-普通索引
如果查询的列为普通索引列，要看被查询的记录是否在临界值，以及是否是范围查询，才能判断临建锁的范围；
- 被查询的记录是否在临界值情况：
- 非临界值：那么间隙锁为当前记录所在的区间，记录锁则不会生效（记录锁不存在）；
- 临界值：那么间隙锁为相邻的两个区间，记录锁退化成行锁（即只会锁住被查询的那条记录）；
- 范围查询情况：间隙锁为范围所涉及到的所有区间，记录锁也会升级为范围锁涉及到的区间
drop table if exists t2;
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(11) ,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;
INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);
-- 创建普通索引
create index idx_num on t2(num);
1）非临界值
当使用普通索引列查询，查询的记录不处于临界值时，那么间隙锁为被查询记录所在的区间，记录锁则不会生效；
【测试案例-01-间隙锁】
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 触发间隙锁，锁住(15,20]区间
select * from t2 where num=17 for update; | |
| | insert into t2 values(null,15); -- 阻塞 |
| | insert into t2 values(null,18); -- 阻塞 |
| | insert into t2 values(null,20); -- 不阻塞 |
| rollback; | |
| | rollback; |
num=17这条记录不是会锁定(15,20]区间吗？为什么15被阻塞了，20反而没被阻塞呢？
这里需要牵扯到另一个问题了，在InnoDB中，相同的普通索引的叶子节点是以主键的顺序进行排列的，我们来模拟一下刚刚插入的数据在B+Tree上的变化：
只考虑叶子节点的变化，可以看到上图在演变的过程中产生了分裂情况（假设每个叶子节点都只存储两个元素），如果普通索引的重复值太多势必会造成大量的分裂情况，减低插入效率，因此索引列不宜选择重复率太大的列；
再看下图数据库表中实际存储的列的样子我们就会明白为什么num=20不阻塞，num=15阻塞了
- num索引列排列情况：
查询示意图：
【测试案例-02-间隙锁】
当我们把id列的影响也计算进来时，数据就符合我们正常分析的情况了：
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 触发间隙锁，锁住(15,20]区间
select * from t2 where num=17 for update; | |
| | insert into t2 values(14,15); -- 不阻塞 |
| | insert into t2 values(18,18); -- 阻塞 |
| | insert into t2 values(19,20); -- 阻塞 |
| rollback; | |
| | rollback; |
【测试案例-03-记录锁】
当使用普通索引列查询，查询的记录不处于临界值时，那么间隙锁为被查询记录所在的区间，记录锁则不会生效（不存在记录锁）
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 没有num=17的这条记录，记录锁不会存在
select * from t2 where num=17 for update; | |
| | select * from t2 where num=15 for update; -- 不阻塞 |
| | select * from t2 where num=16 for update; -- 不阻塞 |
| | select * from t2 where num=17 for update; -- 不阻塞 |
| | select * from t2 where num=20 for update; -- 不阻塞 |
| rollback; | |
| | rollback; |
2）临界值
【测试案例-01-间隙锁】
当使用普通索引列来查询，并且查询的记录处于临界值时，那么间隙锁为相邻的两个区间，记录锁退化成行锁；
下面案例将会锁住(10,15]、(15,20]两个区间
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 触发的间隙锁的区间为(10,15]、(15,20]
select * from t2 where num=15 for update; | |
| | insert into t2 values(null,8); -- 不阻塞 |
| | insert into t2 values(null,10); -- 阻塞 |
| | insert into t2 values(null,11); -- 阻塞 |
| | insert into t2 values(null,15); -- 阻塞 |
| | insert into t2 values(null,18); -- 阻塞 |
| | insert into t2 values(null,20); -- 不阻塞 |
| rollback; | |
| | rollback; |
发现实际插入的数据跟我们分析的情况不一致，这个时候我们依然也要观察B+Tree的实现：
- 索引底层构建过程：
- 临键锁区间：
15处于
**(10,15]**和**(15,20]**两个临键区间，因此在两个区间内的数据行都被锁住了
【测试案例-02-记录锁】
当使用普通索引列来查询，并且查询的记录处于临界值时，那么间隙锁为相邻的两个区间，记录锁退化成行锁；
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 记录锁只锁住num=15这行记录
select * from t2 where num=15 for update; | |
| | select * from t2 where num=10 for update; -- 不阻塞 |
| | select * from t2 where num=12 for update; -- 不阻塞 |
| | select * from t2 where num=15 for update; -- 阻塞 |
| | select * from t2 where num=18 for update; -- 不阻塞 |
| | select * from t2 where num=20 for update; -- 不阻塞 |
| | select * from t2 where num=22 for update; -- 不阻塞 |
| rollback; | |
| | rollback; |
3）范围值
【测试案例-01-间隙锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 间隙锁为(10,20]区间
select * from t2 where num>11 and num <16 for update; | |
| | insert into t2 values(9,10); -- 不阻塞 |
| | insert into t2 values(11,10); -- 阻塞（参考B+Tree的构建） |
| | insert into t2 values(11,11); -- 阻塞 |
| | insert into t2 values(12,12); -- 阻塞 |
| | insert into t2 values(15,15); -- 阻塞（被记录锁阻塞） |
| | insert into t2 values(18,18); -- 阻塞 |
| | insert into t2 values(19,20); -- 阻塞 |
| | insert into t2 values(21,20); -- 不阻塞（参考B+Tree的构建） |
| rollback; | |
| | rollback; |
- 分析底层B+Tree构建情况：
【测试案例-02-记录锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 记录锁的区间为(10,20]区间
select * from t2 where num>11 and num <16 for update; | |
| | select * from t2 where num=10 for update; -- 不阻塞（左开右闭） |
| | select * from t2 where num=12 for update; -- 不阻塞（属于间隙） |
| | select * from t2 where num=15 for update; -- 阻塞（触发记录锁） |
| | select * from t2 where num=16 for update; -- 不阻塞（属于间隙） |
| | select * from t2 where num=18 for update; -- 不阻塞（属于间隙） |
| | select * from t2 where num=20 for update; -- 阻塞（左开右闭，触发记录锁） |
| | select * from t2 where num=21 for update; -- 不阻塞（即是间隙，也不在区间） |
| rollback; | |
| | rollback; |
2.4.4 临键锁-主键和唯一索引
如果查询的是唯一索引或主键索引，也要看被查询的记录是否在临界值；是否是范围查询等
- 被查询的记录是否在临界值情况：
- 不在临界值：间隙锁为当前被查询的记录所在的区间，记录锁会消失；
- 在临界值：间隙锁会消失，记录锁退化成行锁
- 范围查询情况：间隙锁为范围查询所涉及到的所有区间，记录数也会升级为范围所涉及到的区间（和普通索引的效果一致）；
创建唯一索引：
-- 删除索引
drop index idx_num on t2;
-- 创建唯一索引
create unique index idx_num on t2(num);
1）非临界值
唯一索引在查询非临界值的记录时和普通索引的特点一样，即间隙锁为当前记录所在的区间，记录锁不生效；
【测试案例-01-间隙锁】
| session-01 | session-02 |
|---|---|
| begin; | |
| begin; |
| -- 间隙锁锁住的区间为(15,20]
select * from t2 where num=17 for update; | |
| | insert into t2 values(null,11); -- 不阻塞 |
| | insert into t2 values(null,15); -- 不阻塞（这一列表中已经存在了，立即响应） |
| | insert into t2 values(null,16); -- 阻塞 |
| | insert into t2 values(null,18); -- 阻塞 |
| | insert into t2 values(null,20); -- 不阻塞（这一列表中已经存在了，立即响应） |
| | insert into t2 values(null,21); -- 不阻塞 |
- 分析num列索引的B+Tree底层构建情况：
Tips：唯一索引冲突时MySQL会立即响应，不会触发临键锁
【测试案例-02-记录锁】
唯一索引在查询非临界值的记录时，记录锁不生效；
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 没有num=17的这条记录，记录锁不会存在
select * from t2 where num=17 for update; | |
| | select * from t2 where num=15 for update; -- 不阻塞 |
| | select * from t2 where num=16 for update; -- 不阻塞 |
| | select * from t2 where num=17 for update; -- 不阻塞 |
| | select * from t2 where num=20 for update; -- 不阻塞 |
| rollback; | |
| | rollback; |
2）临界值
在使用唯一索引查询临界值时，间隙锁会消失，记录锁会退化成行锁；
【测试案例-01-间隙锁】
| session1 | session2 |
|---|---|
| begin; | |
| begin; | |
| select * from t2 where num=15 for update; | |
| insert into t2 values(null,4); -- 不阻塞 | |
| insert into t2 values(null,8); -- 不阻塞 | |
| insert into t2 values(null,11); -- 不阻塞 | |
| insert into t2 values(null,15); -- 阻塞(阻塞的原因是记录锁，而不是间隙锁) | |
| insert into t2 values(null,28); -- 不阻塞 | |
| rollback; | insert into t2 values(null,20); -- 不阻塞 |
| rollback; |
【测试案例-02-记录锁】
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 记录锁只锁住num=15这行记录
select * from t2 where num=15 for update; | |
| | select * from t2 where num=10 for update; -- 不阻塞 |
| | select * from t2 where num=12 for update; -- 不阻塞 |
| | select * from t2 where num=15 for update; -- 阻塞 |
| | select * from t2 where num=18 for update; -- 不阻塞 |
| | select * from t2 where num=20 for update; -- 不阻塞 |
| | select * from t2 where num=22 for update; -- 不阻塞 |
| rollback; | |
| | rollback; |
3）范围值
【测试案例-01-间隙锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 间隙锁为(10,20]区间
select * from t2 where num>11 and num <16 for update; | |
| | insert into t2 values(9,10); -- 不阻塞 |
| | insert into t2 values(11,10); -- 阻塞（参考B+Tree的构建） |
| | insert into t2 values(11,11); -- 阻塞 |
| | insert into t2 values(12,12); -- 阻塞 |
| | insert into t2 values(15,15); -- 阻塞（被记录锁阻塞） |
| | insert into t2 values(18,18); -- 阻塞 |
| | insert into t2 values(19,20); -- 阻塞 |
| | insert into t2 values(21,20); -- 不阻塞（参考B+Tree的构建） |
| rollback; | |
| | rollback; |
- 分析底层B+Tree构建情况：
【测试案例-02-记录锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；
| session1 | session2 |
|---|---|
| begin; | |
| begin; |
| -- 记录锁的区间为(10,20]区间
select * from t2 where num>11 and num <16 for update; | |
| | select * from t2 where num=10 for update; -- 不阻塞（左开右闭） |
| | select * from t2 where num=12 for update; -- 不阻塞（属于间隙） |
| | select * from t2 where num=15 for update; -- 阻塞（触发记录锁） |
| | select * from t2 where num=16 for update; -- 不阻塞（属于间隙） |
| | select * from t2 where num=18 for update; -- 不阻塞（属于间隙） |
| | select * from t2 where num=20 for update; -- 阻塞（左开右闭，触发记录锁） |
| | select * from t2 where num=21 for update; -- 不阻塞（即是间隙，也不在区间） |
| rollback; | |
| | rollback; |
2.4.5 临键锁总结
临键锁是InnoDB在查询数据时锁定的一个范围，这个范围包含有间隙锁和记录锁；根据查询的条件不同（是否临界值等）、列的类型不同（是否是索引等）触发的临键锁范围也不同；
- 普通列：临键锁中的间隙锁和记录数均为表级别；
- 普通索引列：
- 非临界值：间隙锁为被查询的记录所在的区间，记录锁不生效
- 临界值：间隙锁为被查询记录所在的相邻两个区间，记录数退化为行锁
- 范围值：间隙锁和记录数均为查询条件所涉及到的区间
- 唯一索引或主键索引列：
- 非临界值：间隙锁为被查询的记录所在的区间，记录锁不生效
- 临界值：间隙锁失效，记录锁退化为行锁
- 范围值：间隙锁和记录数均为查询条件所涉及到的区间
Tips：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。
2.5 自增锁
MySQL的自增锁是指在使用自增主键（Auto Increment）时，为了保证唯一性和正确性，系统会对自增字段进行加锁。这样可以确保同时插入多条记录时，每条记录都能够获得唯一的自增值。
2.5.1 表的插入数据方式
我们之前在表中插入数据都是用最基本的insert，但insert语句的用法用很多，另外MySQL还提供replace语句，允许对表中的数据进行替换；
- insert用法：
drop table if exists t3;
CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT=1;
insert into t3 values(1,20);
insert into t3 values(2,25);
drop table if exists t4;
CREATE TABLE `t4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT=1;
-- 插入记录,如果存在这条记录就报错(主键唯一)
insert into t4 values(10,20);
insert into t4 values(11,20),(12,21),(13,22);
insert into t4 set id=14,age=25;
insert into t4 select * from t3;
- replace用法：
delete from t4;
-- 如果没有这条记录就新增,有这条记录就修改
replace into t4 values(1,20);
replace into t4 set id=10,age=100 ;
replace into t4 select * from t3;
2.5.1 insert的不同类型
1）Simple inserts
简单插入模式
- 示例：
insert into table_name values(xxx);
- 特点：可以提前确定要插入的行数
2）Bulk inserts
批量插入模式，包含insert...select、replace select、load data等语句；
- 示例：
insert into t4 select * from t3;
replace into t4 select * from t3;
- 特点：事先不知道要插入的行数，以及所需的自动增量值的数量
3）Mixed-mode
该模式也属于Simple Inserts
- 示例：
insert into table_name values(xxxx),(xxxx),(xxxx);
- 特点：为一些(但不是全部)新行指定自动增量值
2.5.2 自增锁原理
1）插入原理
MySQL自增锁的实现机制是使用了一个名为"auto-increment lock"的互斥锁。当使用INSERT语句插入一条新记录时，MySQL会自动为自增字段加锁，防止其他并发的插入操作同时获取相同的自增值。这个锁是在内部实现的，不需要用户手动创建或管理。
自增锁确保了插入记录的唯一性和正确性，避免了并发插入产生冲突。但同时也会带来一些性能上的影响，因为并发插入操作需要等待锁的释放。因此，在高并发的场景下，可能需要考虑使用其他方案来避免自增锁成为瓶颈。
Tips：自增锁跟事务无关，即使多个insert语句存在同一个事务中，每次insert都会申请最新的自增锁来获取最新的AUTO_INCREMENT值；获取到自增值后释放，而不是事务结束释放；
2）自增锁表锁
需要注意的是，自增锁是基于表级别的，而不是行级别的。这意味着在同一时刻针对于同一张表只能有一个线程在插入记录（前提是需要increment来分配id），并且每个表都有一个自己独立的自增锁。
2.5.3 自增锁的模式
和自增锁相关的一个参数为（5.1.22版本之后加入）innodb_autoinc_lock_mode：可以设定3个值，0，1，2
show variables like 'innodb_autoinc_lock_mode';
- 0：traditional（传统模式）：每次insert都会产生表级别的自增锁，能够绝对保证insert的插入顺序，但并发能力较弱；
- 1：consecutive（连续模式）：对于Simple Inserts能够产生一个轻量级的页面锁来保证insert的连续插入；对于Bulk Inserts无法确定插入的行数时采用表级别自增锁来保证insert的连续插入；
- 2：interleaved（交叉模式）：不采用表锁，来一个insert处理一个，并发能力最高，但可能会造成insert分配的id顺序不一致；
Tips：参数只控制InnoDB引擎的设置，所有MyISAM均为traditional ，每次均会进行表锁。只有Innodb会视参数不同而产生不通的锁。
1）traditional（传统模式）
在传统模式下，不管是在执行Simple inserts还是Bulk inserts时每个insert获取自增锁时都会触发表锁，在某个insert没有释放表锁之前其他线程/进程均不可获取自增锁；虽然传统模式保证了多个insert插入的连续性但实际上并发插入属于串行化，性能较低；
Tips：再次说明，自增锁是执行insert时获取auto_increment值时才会申请，获取到auto_increment值时就会立即释放，跟事务无关；
2）consecutive（连续模式）
在连续模式下，InnoDB会根据当前执行的insert语句来判断是否使用表级别自增锁。这也是InnoDB的默认值；
- Simple inserts：InnoDB能够预先知道要插入的行数，因此产生的自增锁只会锁住对应的那些id（页锁），避免表级别的自增锁
- Bulk Inserts：InnoDB无法预知要插入的行，触发表级别自增锁
【Simple Inserts】
【Bulk Inserts】
3）interleaved（交叉模式）
在交叉模式下，所有的insert语句都不会使用自增锁（悲观锁），而是采用一个轻量级的mutex（乐观锁），来一个insert立即处理，在生成insert语句完毕后检查id是否被其他线程/进程使用，如果已经被使用则重新获取id；这样一来，多条 INSERT 语句可以并发的执行，因此交叉模式并发量最高，但对于同一个语句来说它所得到的auto_increment值可能不是连续的。
- 交叉模式示意图：
【模拟交叉模式并发插入情况】
步骤①：Thread-01线程执行insert获取到auto_increment值为10
步骤②：与此同时Thread-02线程也执行insert操作获取到10
步骤③：Thread-01对获取对auto_increment值+1，此时auto_increment为11
步骤④：Thread-02对获取对auto_increment值+1，此时auto_increment为11
步骤⑤：Thread-01线程校验id值是否被其他线程获取过，校验结果：未被其他线程获取过，将之前获取到的auto_increment值分配给此次的insert语句，执行插入。
步骤⑥：Thread-02线程校验id值是否被其他线程获取过，校验结果：已经被其他线程获取过，重新回到自增锁步骤①；
【交叉模式的注意事项】
由于交叉模式中，所有的客户端线程都可以同时获取自增锁，因此该模式可能会出现id不连续问题。在搭建有MySQL主从复制的架构并且binlog日志格式为SBR时会出现主从数据不一致问题；
原因：当Master接收高并发量的insert语句时会将insert语句记录到binlog日志中，这些binlog日志被发送到Slave时Slave将会并发执行这些SQL语句，很有可能导致Slave执行这些语句的顺序和当初Master执行的顺序一致，导致主从分配的id不一致，因此在MySQL主从复制时从服务器应禁止使用交叉模式；
2.5.4 自增步长控制
一般我们在创建表的时候id起始值为1，通过AUTO_INCREMENT可以设置其值；
drop table if exists t3;
CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT=1;
-- 在创建表后也可以通过SQL语句修改auto_increment
alter table t3 auto_increment=20;
2.6 悲观锁，乐观锁
2.6.1 悲观锁乐观锁概念
- 悲观锁
- 就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改。
- 整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制
- 事实上关系数据库中的行锁，表锁不论是读写锁都是悲观锁
- 乐观锁
- 顾名思义，就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。
- 但是在更新的时候会去判断在此期间数据有没有被修改
- 需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性
- 悲观锁，乐观锁使用前提
- 对于读操作远多于写操作的时候，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁
- 如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。
2.6.2 乐观锁的实现方式
1）版本号
就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段，每次更新把这个字段加1，读取数据的时候把version读出来，更新的时候比较version如果还是开始读取的version就可以更新了如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。
-- 要修改数据之前，先查该数据上一次修改的时间戳
select version from t_goods where id=1;
-- 修改数据时，更新时间戳
update t_goods set goods_name='小苹果', version=version+1 where version=${version};
2）时间戳
和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间（timestamp）和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比如果一致则OK，否则就是版本冲突。
-- 要修改数据之前，先查该数据上一次修改的时间戳
select lock_time from t_goods where id=1;
-- 修改数据时，更新时间戳
update t_goods set goods_name='小苹果', lock_time=unix_timestamp(CURRENT_TIMESTAMP) where lock_time=${lock_time};
浙公网安备 33010602011771号