简单聊一下MySQL的锁机制

一.什么是锁？

锁是计算机协调多个进程或线程并发访问某⼀资源的机制。锁总体上可以分为悲观锁和乐观锁

从粒度上可以分为：

• 行锁：锁住一整行；
• 表锁：锁住整个表；
• 页锁

从算法上分：

• 间隙锁；
• 记录锁；
• 临键锁；

二.MyISAM引擎锁

MyISAM使用的是表锁，因为MyISAM没有事务。
MyISAM的读和写是串行的，可读不可写，可写不可读
表锁分为两种：
读锁（共享锁）：线程可读不可写。
写锁（排它锁）：本线程可读可写，其他线程不可读不可写，排它排它。

注意事项:MyISAM是隐式加锁，一瞬间的事情，比如我执行一条sql语句，它会在执行之前加上锁，执行完后释放锁，这个过程是很快的，0.00…秒的事。

但是它提供了一些SQL语句让我们人为的加锁和释放锁。任何搜索引擎都能用，不仅仅是MyISAM,InnoDB也可以。

lock table 表 read;
lock table 表 write;

unlock tables;

首先，准备数据

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; 

1.读锁（共享锁）

简单一句话：只可读不可写。

如果给表取别名的话，在执行语句时也要把别名带上：lock table test1 as t read;

2.写锁（排它锁）

这里我们恢复一下数据再做测试

那现在去释放一下锁资源

简单一句话：本线程可读可写，其他线程不可读不可写。

3.并发插入

MyISAM也可以支持并发插入，也就是可读可写，但是update和delete这些语句还是不能，写的话只有insert。

4.读与写的优先级

在MyISAM中，，写比读的优先级要高很多，因为它认为写入数据比读取数据更重要，所以它提供了一些SQL语句来提高第的优秀级和降低写的优先级。

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; -- 提升本次查询操作的优先级

但是我个人感觉，读写频繁的话最好使用InnoDB引擎，开启事务时，一旦数据损坏或者奔溃，它有回滚的功能保证数据的完整。

三.InnoDB引擎锁

InnoDB的锁分为行锁和表锁。

InnoDB默认是表锁（表冲突大），但是如果用到索引时，则触发行锁。
InnoDB也存在两种锁：
读锁（共享锁）：本线程可读可写，其他线程可读不可写。
写锁（排它锁）：本线程可读可写，其他线程不可读不可写，排它排它。

1.读锁（共享锁）

特点：在给其中某一行加上共享锁时，本线程可读可写，其他线程可读，因为这是一个共享锁，但是其他线程写入时，会获取一个排它锁，此时这一行已经有了一个共享锁，不可能再添加锁，所以其他线程可读不可写。
在InnoDB中进⾏普通的查询操作是不会触发共享锁的，必须显示的加上 lock in share mode ，才会加上共享锁。

1.准备数据
CREATE TABLE account ( 
	 id INT PRIMARY KEY AUTO_INCREMENT, 
	 name VARCHAR(10), 
	 money DOUBLE 
); 
-- 添加数据 
INSERT INTO account (name, money) VALUES ('a', 1000), ('b', 1000);

2.写锁（排它锁）

获取排它锁可以在查询语句后⾯显示的加上 for update ，来获取排它锁，触发任何修改（update/delete/insert）操作也会获取该⾏的排它锁。

本线程可读可写，其他线程不可读不可写
当然我们一直强调，是给这一行加上XX锁，所以如果查询不同行的数据时，是可以查得到的。

3.索引实现行锁

InnoDB默认使用表锁，查询则是共享锁，增删改则是排它锁。

现在我们给name列创建一个索引：

create index idx_name on account(name);

4.死锁

（InnoDB）当两个排它锁都在等对方释放锁时，造成死锁的那一方将会主动释放锁。

四.意向锁

意向锁的存在是为了协调⾏锁和表锁的关系，⽤于优化InnoDB加锁的策略。
意向锁的主要功能就是：避免为了判断表是否存在⾏锁⽽去全表扫描。
● 意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
● 意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁
意向排它锁（IX）与IS、IX、X、S都是冲突的。

意向锁之间是兼容的，它与行级别的锁是兼容的，与表级别的X/S是互斥的。而且意向锁是表级别的。

五、记录锁

简单来讲：数据库有这条记录而且上了共享锁或者排它锁，那这一条记录也有一个记录锁。
其实行锁就是记录锁。

记录锁的条件：

• 必须是主键索引或者唯⼀索引
• 查询语句条件必须为精确匹配且命中数据
• 触发间隙锁和临键锁时可能会触发记录锁

触发了记录锁会阻塞这条记录的其他SQL操作(如果查询的sql两条都是共享锁则不会阻塞)

这个图中，左边查询语句是排它锁，右边查询语句是共享锁，所以阻塞

六、间隙锁

间隙锁，当我们⽤范围条件⽽不是等值条件检索数据，并请求共享或排他锁时，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);

七、临键锁

为了解决幻读，阻塞insert。
但由于临键锁中包含有记录锁。临键锁=间隙锁+记录锁
临键锁的区间是：左开右闭，左边不阻塞，右边阻塞

1，普通列

普通列触发的是：表级别的间隙锁+表级别的记录锁

建表数据
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);

这里查询到的是间隙锁，也是表的排它锁，会阻塞insert
这里查询到的是记录锁，也是表的排它锁，所以SQL全部阻塞

2.普通索引

它也是分间隙锁和记录锁的，这里成为临界值和非临界值。

• 非临界值：间隙区间为当前所在区间，没有记录锁（本身这条SQL语句所操作的数据不存在，也就没有记录锁）。
• 临界值：间隙区间为当前涉及到的区间，记录锁变为行锁。
  两者都有间隙区间

create index idx_num on t2(num);

非临界值的情况

我们可以试着分析一下：现在是以num为索引，在插入20的时候，id自增为21，也就是插在（15,20】的后面，但是阻塞只在该区间阻塞，这条记录已经溢出，所以不会造成阻塞。
相反，插入15的时候，id为22，但此时num才是索引，排序的话这条新插入的应该在这个区间，所以这条插入15的SQL1语句会阻塞。

临界值的情况：

范围值

当使⽤普通索引进⾏条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；

3.主键和位移索引

临界值：间隙锁为当前被查询的记录所在的区间，记录锁会消失；
非临界值：间隙锁会消失，记录锁退化成⾏锁

非临界值

-- 删除索引
drop index idx_num on t2;
-- 创建唯⼀索引
create unique index idx_num on t2(num);

临界值

记录锁依旧会阻塞，但是只对这条数据。

4.小结

● 普通列：临键锁中的间隙锁和记录数均为表级别；
● 普通索引列：
○ ⾮临界值：间隙锁为被查询的记录所在的区间，记录锁不⽣效
○ 临界值：间隙锁为被查询记录所在的相邻两个区间，记录数退化为⾏锁
○ 范围值：间隙锁和记录数均为查询条件所涉及到的区间
● 唯⼀索引或主键索引列：
○ ⾮临界值：间隙锁为被查询的记录所在的区间，记录锁不⽣效
○ 临界值：间隙锁失效，记录锁退化为⾏锁

○ 范围值：间隙锁和记录数均为查询条件所涉及到的区间

临键锁的主要⽬的，也是为了避免幻读，如果把事务的隔离级别降级为RC，临
键锁则也会失效

8.自增锁

自增自增，其实我们会第一时间想到那个主键自增，为了保证唯⼀性和正确性，系统会对
⾃增字段进⾏加锁。这样可以确保同时插⼊多条记录时，每条记录都能够获得唯⼀的⾃增值。

⾃增锁是基于表级别的，⽽不是⾏级别的。同⼀时刻针对于同⼀张表只能有⼀个线程在插⼊记录（前提是需要increment来分配id），并且每个表都有⼀个⾃⼰独⽴的⾃增锁。
获取id值前加申请锁，拿到id之后释放锁，与事务无关

● 0：traditional（传统模式）：每次insert都会产⽣表级别的⾃增锁，（由于只知道插入多少条数据，所以在插入操作之前，mysql会把这张表锁了，必须等上一个表的插入操作执行完后释放锁了才会轮到下一次插入操作），能够绝对保证insert的插⼊顺序，但并发能⼒较弱；
● 1：consecutive（连续模式）：对于Simple Inserts能够产⽣⼀个轻量级的⻚⾯锁来保证insert的连续
插⼊；对于Bulk Inserts⽆法确定插⼊的⾏数时采⽤表级别⾃增锁来保证insert的连续插⼊；
● 2：interleaved（交叉模式）：不采⽤表锁，来⼀个insert处理⼀个，并发能⼒最⾼，但可能会造成
insert分配的id顺序不⼀致；

9.悲观锁和乐观锁

1.悲观锁

• 就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改。
• 整个数据处理中需要将数据加锁。悲观锁⼀般都是依靠关系数据库提供的锁机制
• 事实上关系数据库中的⾏锁，表锁不论是读写锁都是悲观锁

2.乐观锁

• 顾名思义，就是很乐观，每次⾃⼰操作数据的时候认为没有⼈会来修改它，所以不去加锁。但是在更新的时候会去判断在此期间数据有没有被修改
• 需要⽤户⾃⼰去实现，不会发⽣并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。