MySQL锁 、行锁、表锁、间隙锁、临键锁、共享锁、排它锁、意向锁

行锁

使用行锁是，如果匹配条件字段不带有索引时，Innodb会使用表锁；

行锁的 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
行锁的 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

共享锁 可以理解为 读锁，拍他锁 理解为 写锁；
写锁不能和其他写锁 或者 读锁兼容，但是读锁可以和其他读锁兼容；

记录锁（Record Lock）

对主键或者唯一索引进行增删改或显示的加锁，InnoDB会加记录锁

## 显示的加锁，ID是主键 或 唯一索引
select * from  people where id =3 for update;

---

间隙锁(Gap Locks)

间隙锁：锁加在不存在的空闲空间；

https://zhuanlan.zhihu.com/p/48269420

产生间隙锁的条件

产生间隙锁的条件（RR事务隔离级别下）：

1. 使用普通索引锁定；
2. 使用 多列 唯一索引；
3. 使用唯一索引锁定 多行 记录。

如果，搜索条件里有多个查询条件(即使每个列都有唯一索引)，也是会有间隙锁的。

---

间隙锁设置

查看间隙锁是否禁用

show variables like 'innodb_locks_unsafe_for_binlog';

默认值为OFF，即启用间隙锁；

关闭间隙锁

# 在 my.cnf 里面的[mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1

---

间隙锁区间范围

左开 右闭

---

测试

环境：MySQL，InnoDB，默认的隔离级别（RR）

数据表

CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据

INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');

目前该表存在的间隙

(-infinity, 1]
(1, 5]
(5, 7]
(7, 11]
(11, +infinity]

测试

1. 测试行锁

由于id是主键，因此这里加的是 行锁；
如果ID是普通索引，则下面的sql加的是（1，5]和（5，7]的间隙锁；

/* 开启事务1 */
BEGIN;
/*由于id是主键，因此这里加的是 行锁；*/
SELECT * FROM `test` WHERE `id` = 5 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
/* 提交事务1，释放事务1的锁 */
COMMIT;

在其他事物里插入id=4和id=6的数据 均能成功

INSERT INTO `test` (`id`, `name`) VALUES (4, '小张'); # 正常执行

/* 事务3插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 正常执行

---

2. 测试间隙锁
   对主键 或 唯一索引加 范围 也可以开启 间隙锁；

/* 开启事务1 */
BEGIN;
/* 查询 id 在 7 - 11 范围的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
/* 提交事务1，释放事务1的锁 */
COMMIT;

对区间（5，7] 和 （7-11]加间隙锁间隙锁；

/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 正常执行

/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 阻塞

/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 阻塞

/* 事务7插入一条 id = 11， name = '李西' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (11, '李西'); # 阻塞

/* 事务8插入一条 id = 12， name = '张三' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (12, '张三'); # 正常执行

---

临键锁(Next-key Locks)

临键锁 也是一种 间隙锁；

例如表t1中有age作为普通索引；

其中存在的间隙为
(-∞，8]
(8，10]
(10，30]
(30，+∞]

那么
select * from t1 where age=10,则是对区间(8，10]
和(10，30]加锁，因为都是相邻age=10的区间，所以称为临键锁；

---

表锁

表级锁为表级别的锁定，会锁定整张表，可以很好的避免死锁

https://www.cnblogs.com/null-qige/p/8664009.html

用Lock Table来创建，在操作结束后，使用UnLock来释放锁。

表锁 的读锁（共享锁）

加锁 lock 表名 read;
解锁 unlock tables;

测试

创建了1个数据库locktest 和 2个表test_product 和 test_user;同时开启2个mysql client A、B;

A 客户端

mysql> LOCK TABLES test_product READ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_user
    -> ;
ERROR 1100 (HY000): Table 'test_user' was not locked with LOCK TABLES

a线程中对test_product加lock … read,但没有对test_user加锁，此时不允许对其他表进行访问；

在B中输入

mysql> SELECT * FROM test_product limit 0,1;
+----+------+------+--------+----------+
| id | code | name | price  | quantity |
+----+------+------+--------+----------+
|  1 | S001 | 1    | 100.00 |      200 |
+----+------+------+--------+----------+
1 row in set (0.00 sec)

mysql> update test_product set price=250 where id=2;

当更新操作时，一直卡在这里，等待a线程释放锁；

释放a线程的锁
a线程中输入
UNLOCK TABLES

b线程中

mysql> update test_product set price=250 where id=2;
Query OK, 0 rows affected (2 min 11.63 sec)
Rows matched: 1  Changed: 0  Warnings: 0

a线程释放锁后，b线程成功修改了数据；

表锁的写锁（排他锁）

a线程中

mysql> LOCK TABLES test_product WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_user;
ERROR 1100 (HY000): Table 'test_user' was not locked with LOCK TABLES

同理。加锁后不允许对 未加锁的表进行访问；

b线程中

mysql> SELECT * FROM test_product limit 0,1;

查询操作会一直卡在这里；

当在a线程中释放锁后，b线程便查询出了结果；

综上
Lock Tables…READ不会阻塞其他线程对表数据的读取，会阻塞其他线程对数据变更（增、删、改），并且不允许访问未被锁住的表；

Lock Tables…WRITE会阻塞其他线程对数据的 读和写（增删改查），并且不允许访问未被锁住的表；

---

页锁

✌页锁运用在 BDB引擎，颗粒度和性能都位于 表锁和行所之间，会出现死锁；

---

Innodb意向锁

https://blog.csdn.net/dreamvyps/article/details/84500543

https://blog.csdn.net/yabingshi_tech/article/details/30495065

定义

意向锁的含义是如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁；
对任一结点加锁时，必须先对它的上层结点加意向锁。如：对表中的任一行加锁时，必须先对它所在的表加意向锁，然后再对该行加锁。

事务a在T表的r行已经加了锁，事务b想对T表加 表锁，在没有意向锁的情况下，事务b需要遍历整个表才能知道有行锁，然后被阻塞，效率低；

---

意向锁分为 意向共享锁 和 意向排它锁；

意向共享锁（Intent Share Lock，简称 IS 锁）

如果要对一个数据库对象加S锁，首先要对其上级结点加IS 锁，表示它的后裔结点拟（意向）加 S锁；

意向排它锁（Intent Exclusive Lock，简称 IX 锁）

如果要对一个数据库对象加X 锁，首先要对其上级结点加 IX锁，表示它的后裔结点拟（意向）加X 锁。

---

MySQL死锁

表锁不会出现死锁，行锁、页锁可能出现死锁；

所谓死锁: 是指两个或两个以上的进程在执行过程中,
因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.

MySQL有两种死锁处理方式

等待，直到超时

1. 事物1已经获得a表的锁，准备获取b表的锁，事物2已经获得b表的锁，正准备获取a表的锁，此时将发生死锁；

当一方的事物等待一段时间后就会自动放弃并回滚，innodb中通过innodb_lock_wait_timeout参数设置等待时间；

行锁会产生死锁，因为在行锁中，锁是逐步获得的，主要分为两步：锁住主键索引，锁住非主键索引。如：当两个事务同时执行时，一个锁住了主键索引，在等待其他索引；另一个锁住了非主键索引，在等待主键索引。这样便会发生死锁。InnoDB一般都可以检测到这种死锁，并使一个事务释放锁回退，另一个获取锁完成事务。

发起死锁检测

如何预防死锁

1. 当对多个行(页)加锁时，保持加锁顺序一致；
2. 同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；