MySQL锁机制详解

1 MySQL 锁简介

1.1 什么是锁

锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能，实现了锁机制，MySQL中的锁是在服务器层或者存储引擎层实现的。

1.2 锁用来解决什么问题

锁是用来解决并发事务的访问问题，我们已经知道事务并发执行时可能带来的各种问题，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另外一方面还要确保每个用户能以一致的方式读取和修改数据，尤其是一个事务进行读取操作，另一个同时进行改动操作的情况下。

一个事务进行读取操作，另一个进行改动操作,我们前边说过，这种情况下可能发生脏读、不可重复读、幻读的问题。

怎么解决脏读、不可重复读、幻读这些问题呢？其实有两种可选的解决方案：

方案一：读操作MVCC，写操作进行加锁

该方案性能较好，但可能会读到旧版本记录

方案二：读写操作都加锁

该方案性能一般，但是每次都可以读取到最新的记录，比如在银行场景中，对安全性要求非常高

2 锁的分类

MySQL 中锁有很多，按照模式、粒度等可以分为如下几种类型

3 乐观悲观锁

3.1 乐观锁

1、概念

乐观锁，顾名思义，就是非常乐观，乐观锁认为数据一般情况下不会造成冲突，所以在数据提交更新的时候才会去检测。

2、实现

乐观锁是基本版本号机制实现的，数据表中增加一个 version 字段，读取数据时将 version 一起读出。数据每更新一次，version 字段值 + 1。当修改需要提交时，将读取时的版本号与数据库当前版本号做比较，如果一致，说明在此期间无人修改这条记录，不一致则说明已经被修改了，提交失败。

3、适用场景

乐观锁适用于读操作多，写操作少的场景

3.2 悲观锁

1、概念

悲观锁是相比较乐观锁而言的，就是比较悲观，悲观锁认为数据每次操作都会被修改，所以在每次操作数据时都会加上锁。

2、实现

悲观锁通过共享锁和排他锁实现（下面会讲到这两种锁）

3、适用场景

适用于并发量不大，写操作多，读操作少的场景

4 共享排他锁

4.1 共享锁

1、概念

共享锁，又称读锁，简称 S 锁。当事务对数据加上读锁后，其他事务只能对该数据加读锁，不能加写锁。

2、实现

共享锁加锁方法：select …lock in share mode

4.2 排他锁

1、概念

排他锁，又称为写锁，简称 X 锁，当事务对数据加上排他锁后，其他事务无法对该数据进行查询或者修改

MySQL InnoDB引擎默认 update，delete，insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型。

2、实现

排他锁加锁方式：select …for update

5 粒度锁

5.1 全局锁

1、概念

全局锁，从名称上可以理解，全局锁就是对整个 MySQL 数据库实例加锁，加锁期间，对数据库的任何增删改操作都无法执行。

2、实现

MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)

3、适用场景

全库数据备份，可以使用全局锁，其他情况不要使用

5.2 表级锁

1、概念

表级锁，给当前操作的这张表加锁， MyISAM 与 InnoDB 引擎都支持表级锁定

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

2、实现

加表锁：lock table read/write

解除表锁：

第一步：找出被锁的表

show processlist

第二步：kill 掉锁表的进程

kill 21;
kill 22;
1
2

再次更新 user 表数据

可以正常更新了

5.3 页级锁

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁

5.4 行级锁

1、概念

行级锁是 MySQL 粒度最细的锁，发生锁冲突概率最低，但是加锁慢，开销大

MySQL 中只有 InnoDB 引擎支持行锁，其他不支持

2、实现

MySQL 中，行级锁并不是之间锁记录，而是锁的索引。MySQL 在执行 update、delete 语句时会自动加上行锁

6 意向锁

1、概念

意向锁是表锁，为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存

2、作用

当有事务A有行锁时，MySQL会自动为该表添加意向锁，事务B如果想申请整个表的写锁，那么不需要遍历每一行判断是否存在行锁，而直接判断是否存在意向锁，增强性能。

3、兼容性

7 间隙临键记录锁

1、概念

记录锁、间隙锁、临键锁都是排它锁，而记录锁的使用方法跟排它锁介绍一致。

7.1 记录锁

记录锁是封锁记录，记录锁也叫行锁，例如：

select * from user where id = 1 for update;
1

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

7.2 间隙锁

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据

select * from user where id < 10 for update;
1

即所有在 [1，10）区间内的记录行都会被锁住，所有id 为 1、2、3、4、5、6、7、8、9 的数据行的插入会被阻塞

7.3 临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，是一个左开右闭区间。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

---

---

---

1、MySql锁机制

锁机制的作用：

解决因为资源共享，而造成的并发问题。

没有锁机制时：

例如一号用户和二号用户都要去买同一件商品（假如这件商品是一件衣服），一号用户手速稍微快了一些，于是就先买到了这件衣服，但是因为没有“锁机制”，于是就造成了二号用户不知道这件衣服已经被人买了，所以就造成了“并发问题”。

有锁机制时：

但是有了“锁机制”，一号用户在买了衣服之后就会“对衣服进行加锁”，二号用户看到“衣服被加锁了”，于是就去“等待着衣服被解锁”。

在“衣服未被解锁”的过程中，衣服则会被一号用户进行“试穿”、“下单”、“付款”、“打包”、或者“不满意，取消订单”一系列的操作，这一系列操作之后“衣服会被解锁”。

如果一号用户这边觉得衣服还行打算买下来，在买完之后“衣服就会被解锁”，二号用户这边看到“衣服被解锁了”就会去查看衣服是否还存在，很明显已经不存在了，因为一号用户已经把衣服买了，如果一号用户“不满意，取消订单”，那么二号用户这边就可以购买这件衣服。

因此通过锁机制就很好的解决了买衣服造成的并发问题。

2、MySql锁的分类

按照“操作类型”分：

1、读锁（也叫“共享锁”）：

对同一个数据（衣服），多个读操作可以同时进行，互不干扰。

2、写锁（也叫“互斥锁”）

如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作、写操作。

按照“操作范围”分：

1、表锁 ：

一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突、并发度低。

2、行锁（与表锁的特性完全相反）：

一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）。

3、页锁（几乎遇不见）

页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

3、MySql表锁示例

准备工作：

1、创建两张表，表table和表lock

create table tab(
id int primary key auto_increment , 
name varchar(20)
)engine myisam;
insert into tab(name) values('tab1');
insert into tab(name) values('tab2');
insert into tab(name) values('tab3');
insert into tab(name) values('tab4');
insert into tab(name) values('tab5');
1
2
3
4
5
6
7
8
9

create table locktab(
id int primary key auto_increment , 
name varchar(20)
)engine myisam;
insert into locktab(name) values('locktab1');
insert into locktab(name) values('locktab2');
insert into locktab(name) values('locktab3');
insert into locktab(name) values('locktab4');
insert into locktab(name) values('locktab5');
1
2
3
4
5
6
7
8
9

2、再新建两个会话

加锁相关命令：

1、增加锁：

lock table 表1 read/write,表2 read/write,...;
1

2、释放锁：

unlock tables;
1

3、查看加锁的表：

show open tables;
1

加读锁（也叫“共享锁”）：

会话192.168.11.101（1）：

给表locktab加读锁：

lock table locktab read;
select * from locktab; --执行读操作（查），可以
1
2

delete from locktab where id =1; --执行写操作（增删改），不可以
1

操作没有加锁的表tab：

select * from tab; --读操作，不可以
delete from tab where id = 1; --写操作，不可以
1
2

结论：

1、如果某一个会话对A表加了read锁，则该会话可以对A表进行读操作、不能进行写操作；且该会话不能对其他表进行读、写操作。

2、即如果给A表加了读锁，则当前会话只能对A表进行读操作。

会话192.168.11.101（2），也就是其它会话：

操作加锁了的表locktab：

select * from locktab; --读操作（查），可以
1

delete from locktab where id =1; --写操作，会“等待”会话192.168.11.101（1）将锁释放

1
2

操作没有加锁的表tab：

select * from tab;  --读操作（查），可以
delete from tab where id = 1; --写操作（增删改），可以
1
2

再来在“会话192.168.11.101（1）”中把锁释放了，看看“delete from locktab where id =1;”是否会释放阻塞：

总结：

“会话192.168.11.101（1）”给表locktab加了锁，其他会话的操作：

1、可以对其他表（表locktab以外的表）进行读、写操作。

2、对表locktab：读操作可以，写操作需要等待释放锁。

加写锁（也叫“互斥锁”）：

会话192.168.11.101（1）：

给表locktab加读锁：

lock table locktab write;
select * from locktab; --读操作，可以
delete from locktab where id = 5; --写操作，可以
1
2
3

操作没有加锁的表tab：

select * from tab; --读操作，不可以
delete from tab where id = 3; --写操作，不可以
1
2

总结：

当前会话（会话192.168.11.101（1））可以对加了写锁的表进行任何操作（增删改查），但是不能操作（增删改查）其他表。

会话192.168.11.101（2）：

操作加锁了的表locktab：

select * from locktab; --写操作，会阻塞
1

delete from locktab where id = 2; --写操作，也会阻塞
1

对“会话192.168.11.101（2）”中加写锁的表可以进行增删改查的前提是：等待“会话192.168.11.101（1）”释放写锁。

操作没加锁的表tab：

select * from tab; --读操作，可以
delete from tab where id = 2; --写操作，可以
1
2

MySQL表级锁的锁模式：

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁。

所以对MyISAM表进行操作，会有以下情况：

1、对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

2、对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

分析表锁定：

查看哪些表加了锁：

show open tables
1

1代表被加了锁

分析表锁定的严重程度：

show status like 'table%';
1

Table_locks_immediate：即可能获取到的锁数。

Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)。

一般建议：

Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎，否则MyISAM引擎。

4、MySql行锁示例

准备工作：

unlock tables; --需要先释放之前加的表锁
1

create table linelock(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb ;
insert into linelock(name) values('1');
insert into linelock(name) values('2');
insert into linelock(name) values('3');
insert into linelock(name) values('4');
insert into linelock(name) values('5');
1
2
3
4
5
6
7
8
9

为了研究行锁，暂时将自动commit关闭，“set autocommit = 0;”，以后需要通过“commit”关键字提交写操作：

set autocommit = 0;
1

mysql默认自动commit，oracle默认不会自动commit。

行锁，操作同一条数据：

会话192.168.11.101（1）：写操作

insert into linelock values(6,'a6');
1

会话192.168.11.101（2）：写操作，同样的数据

update linelock set name='ax' where id = 6;
1

总结：

1、如果会话x对某条数据a进行DML操作（研究时：关闭了自动commit的情况下），则其他会话必须等待会话x结束事务(commit/rollback)后，才能对数据a进行操作。

2、表锁是通过unlock tables，也可以通过事务解锁；行锁是通过事务解锁。

行锁，操作不同数据：

会话192.168.11.101（1）： 写操作

insert into linelock values(8,'a8');
1

会话192.168.11.101（2）： 写操作，不同的数据

update linelock set name='ax' where id = 5;
1

总结：

行锁，一次锁一行数据；因此如果操作的是不同数据，则不干扰。

行锁的注意事项：

1、如果没有索引，则行锁会转为表锁

先给字段“name”加个索引：

show index from linelock;
1

可以看到有一条索引，这是主键索引，建表创建主键的时候自动加的。

这是mysql的基础，无论是创建主键还是外键都会自动创建主键索引或外键索引。

给name加个索引：

alter table linelock add index idx_linelock_name(name);
1

会话192.168.11.101（1）： 写操作

update linelock set name = 'ai' where name = '3';
1

会话192.168.11.101（2）： 写操作，不同的数据

update linelock set name = 'aiX' where name = '4';
1

可以看到在索引没有失效的情况下所有会话都可以对数据进行写操作。

会话192.168.11.101（1）： 写操作

update linelock set name = 'ai' where name = 3;
1

会话192.168.11.101（2）： 写操作，不同的数据

update linelock set name = 'aiX' where name = 4;
1

可以发现，数据被阻塞了（加锁）。

原因：如果索引列发生了类型转换，则索引失效。 因此此次操作，会从行锁转为表锁。

2、行锁的一种特殊情况：间隙锁：值在范围内，但却不存在。

此时linelock表中 没有id=7的数据：

“update linelock set name =‘x’ where id >1 and id<9;”即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。

间隙：Mysql会自动给间隙加锁（间隙锁）。即会自动给id=7的数据加间隙锁（行锁）。

行锁：如果有where，则实际加锁的范围就是where后面的范围（不是实际的值）。

如果仅仅是查询数据，能否加锁？ 可以使用for update：

select * from linelock where id =2 for update;
1

通过for update对query语句进行加锁。

mysql中，以下三种方式都可以将自动提交关闭：

set autocommit =0;start transaction;begin;
1

行锁：

InnoDB默认采用行锁。

缺点：

比表锁性能损耗大。

优点：

并发能力强，效率高。

因此建议，高并发用InnoDB，否则用MyISAM。

行锁分析：

show status like '%innodb_row_lock%';
1

Innodb_row_lock_current_waits：当前正在等待锁的数量。

Innodb_row_lock_time：等待总时长。从系统启动到现在一共等待的时间。

Innodb_row_lock_time_avg：平均等待时长。从系统启动到现在平均等待的时间。

Innodb_row_lock_time_max：最大等待时长。从系统启动到现在最大一次等待的时间。

Innodb_row_lock_waits：等待次数。从系统启动到现在一共等待的次数。