MySQL 锁（5） - HelloWorld开发者社区

InnoDB引擎有8类锁

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

类别

锁级别

说明

Shared and Exclusive Locks

行级锁

共享锁、排它锁

Intention Locks

表级锁

意向锁

Record Locks

行级锁

记录锁

Gap Locks

行级锁

间隙锁

Next-Key Locks

行级锁

临键锁

Insert Intention Locks

行级锁

插入意向锁

AUTO-INC Locks

表级锁

自增锁

Predicate Locks for Spatial Indexes

空间索引的谓词锁

锁的颗粒度

锁的颗粒度分行级锁与表级锁，下面来分析一下两种差异。

表锁，顾名思义，是锁一张表；行锁，就是锁住表里的一行数据。锁定粒度，表锁大于行锁。

从效率上看，表锁的效率要比行锁高，表锁直接锁表就可以，行锁还需要找到对应数据才行。所以表锁效率更高。

并发冲突概率，因为表锁是锁定整张表，所以发生冲突的概率更高。而行锁只锁定一行数据，所以冲突概率更小。

锁的冲突概率更大，并发性能更低。

表锁

行锁

锁定粒度

大

小

锁效率

高

低

并发冲突概率

大

小

共享锁（Shared Locks）

我们获取了一行数据的读锁之后，可以用来读取数据，所以它也叫读锁，注意不要在加上了读锁之后去写数据，不然可能会出现死锁的情况。而且多个事务可以共享一把读锁。

那如何给一行数据加上读锁呢？

select * from user_innoDB where id = 1 lock in share mode;

释放锁有两种方式，只要事务结束，锁就会自动释放，包括提交事务和结束事务。

排它锁（Exclusive Locks）

排它锁是用来操作数据的，所以又叫写锁。只要有一个事务获取了一行数据的排它锁，其它事务就不能再获取这一行数据的共享锁和排它锁。

可以使用for update条件数据加上排它锁。

select * from user_innoDB where id = 1 for update;

意向锁

意向锁我们好像从来没有使用过，那意向锁是什么呢？其实它是由数据库自己维护使用的。

当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁。

反过来说：

如果一张表至少有一个意向共享锁，说明有其它事务给其中的某些数据行加上了共享锁。

排它锁也相同。

那么这两个表级别的锁存在的意义是什么？

我们有了表级别的锁，在InnoDB里面就可以支持更多粒度的锁。
提升加锁效率

第二个作用，如果没有意向锁的话，当我们准备给一张表加上表锁的时候，我们首先要做什么？先要去判断有没有其他的事务锁定了其中某些行？如果有的话肯定不能加上表锁。那么这个时候我们就要去扫描整张表才能确定能不能成功加上一个表锁，如果数据量特别大，比如上千万的数据时，加表锁的效率是不是非常低？

但如果我们引入了意向锁之后就不一样了。只要判断这张表上面有没有意向锁，如果有，就直接返回失败。如果没有，就可以加锁成功。所以InnoDB里面的表锁，我们可以把它理解成一个标志。

事务1

begin;
select * from user_innoDB where id = 1 for update;

事务2

begin;
lock tables user_innoDB write; ## blocked
unlock tables; ## 释放表锁方式

以上就是MySQL里的4种基本锁模式，或者叫锁类型。

根据以上了解，锁的作用就是来解决事务对数据的并发访问的问题。

那么，锁倒底锁住了什么呢？

当一个事务锁住了一行数据的时候，其他事务不能操作这一行数据，那它到底是锁住了这一行数据，还是锁住了这一个字段，还是锁住了别的东西呢？

行锁的原理

下边我们使用3张表来说明行锁，一张没有索引<user_lock_no>，一张有主键索引的表<user_lock_pk>，一张有唯一索引的表<user_lock_uq>。

# 初始化
CREATE TABLE `user_lock_no` (
  `id` int(5) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


CREATE TABLE `user_lock_pk` (
  `id` int(5) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


CREATE TABLE `user_lock_uq` (
  `id` int(5) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  unique index (`name`) comment '唯一名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

# 初始化数据
insert into user_lock_no(id,name,age) values(1,'张1',12),(2,'张2',13),(3,'张3',14),(4,'张4',15);

insert into user_lock_pk(id,name,age) values(1,'张1',12),(4,'张4',13),(7,'张7',14),(10,'张10',15);

insert into user_lock_uq(id,name,age) values(1,'张1',12),(4,'张4',13),(7,'张7',14),(10,'张10',15);

没有索引的表(user_lock_no)

Transaction 1

Transaction 2

begin;

select * from user_lock_no where name = '张4' for update ;

【阻塞】-->

select * from user_lock_no where name = '张4' for update ;

可能使用字段加锁，换主键尝试【阻塞】-->

select * from user_lock_no where id = 4 for update ;

可能锁的是一行，换另一条记录【阻塞】-->

select * from user_lock_no where id = 3 for update ;

在第一个事务里，我们通过name = '张4'锁住一行数据。

在第二个事务里，我们尝试给id = 4的这一行加锁【阻塞】了。现在说明这一行被锁了。

如果我们在第二个事务里为另外一条加锁会发生什么？

在第二个事务里，我们尝试给id = 3的这一行加锁也被【阻塞】了。实际上这里整张表都被锁住了。所以在一张表中如果没有索引或者没用索引的情况下，会锁住整张表吗？我们继续。

有主键索引（user_lock_pk）

Transaction 1

Transaction 2

begin;

select * from user_lock_pk where id = 1 for update ;

与Transaction 1条件相同加锁【阻塞】-->

select * from user_lock_pk where id = 1 for update ;

换记录【不阻塞】-->

select * from user_lock_pk where id = 4 for update ;

换字段【阻塞】-->

select * from user_lock_pk where name = '张1' for update ;

换数据、字段【阻塞】-->

select * from user_lock_pk where name = '张4' for update ;

第一种情况，使用相同ID加锁【阻塞】。

第二种情况，使用不同ID加锁，可以加锁成功【不阻塞】。

第三种情况，使用不同字段，相同记录加锁【阻塞】。

第四种情况，使用不同字段，不同记录加锁【阻塞】。

1、2、3种情况可以理解都是行锁，但为什么第4种情况也被阻塞了呢？继续向下。

唯一索引（user_lock_uq）

Transaction 1

Transaction 2

begin;

select * from user_lock_uq where name = '张1' for update;

与Transaction 1条件相同加锁【阻塞】-->

select * from user_lock_uq where name = '张1' for update ;

换字段【阻塞】-->

select * from user_lock_uq where id = 1 for update ;

换数据、字段【不阻塞】-->

select * from user_lock_uq where name = '张4' for update ;

换记录【不阻塞】-->

select * from user_lock_uq where id = 7 for update ;

换记录非索引字段【阻塞】-->

select * from user_lock_uq where age = 15 for update ;

在本次例子中使用唯一索引和主键索引测试，不同的记录都可以正常加锁【不阻塞】。而使用非索引字段还是被【阻塞】。

通过以上三个例子，可以判断出，锁住的不是数据记录，也不是字段，那InnoDB到底锁住的是什么呢？我们分析一下三个表的差异在哪，也就是这三张表的结构，是什么导致了加锁行为的差异？

答案就是索引。InnoDB的行锁，就是通过锁住索引来实现的。

疑问：

1. 为什么表里没有索引的时候，锁住一行数据会导致锁表？

答：如果我们定义了主键（PRIMARY KEY），那么InnoDB会选择主键作为聚集索引。

2. 如果锁住的是索引，一张表没有索引怎么办？

答：如果没有显示定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。

3. 一张表有没有可能没有索引？

答：如果也没有这样的唯一索引，则InnoDB会选择内置的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

所以，为什么锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了。

4. 为什么通过唯一索引给数据行加锁，主键索引也会被锁住？

答：在上篇文章中我们说明了辅助索引是存储的辅助索引树与主键ID的。

比如name=张4，存储的是name的索引和主键ID的值4。

而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候，它跟我们检查数据的步骤是一样的。会通过主键值找到主键索引，然后也锁定。

MySQL 锁（5）

现在我们已经搞清楚4个锁的基本类型和锁的远离了，在官网上还有3种锁，我们可以把它理解为锁的算法。

锁的算法

接下来我们用<user_lock_pk>表中的数据来说明三种行锁的区别。

记录锁（Record）

MySQL 锁（5）

我们把刚才<user_lock_pk>表的记录，用上图表示（只用主键表示一行记录）。这张表里有4条Record。

我们把这种有间隙，精准指定某一行的锁算法叫记录锁（Record）。

select * from user_lock_pk where id = 4 for update;

间隙锁（Gap Lock）

MySQL 锁（5）

当我们查询的记录不存在，没有命中任何一行记录，无论用等值查询还是范围查询，它使用的都是间隙锁（Gap Lock）。

Transaction 1

Transaction 2

begin;

select * from user_lock_pk where id = 6 for update;

<--【锁住】(4,7)

【阻塞】-->

insert into user_lock_pk value(5,'张5',15);

【阻塞】-->

insert into user_lock_pk value(6,'张6',16);

【不阻塞】-->

select * from user_lock_pk where id = 6 for update;

select * from user_lock_pk where id > 20 for update;

<--【锁住】(10,+∞)

阻塞】-->

insert intouser_lock_pk value(12,'张12',18);

注意：间隙锁主要是阻塞插入insert。相同的间隙锁之间不冲突。

Gap Lock只在RR中存在。如果要关闭间隙锁，就是把事务隔离级别设置成RC，并且把innodb_locks_unsafe_for_binlog设置为ON。

这种情况下除了外键约束和唯一性检查会加间隙锁，其他情况都不会用间隙锁。

临键锁（Next Key）

MySQL 锁（5）

当我们使用了范围查询，不仅命中了记录，还包含了间隙，在这种情况下我们使用的就是临键锁，它是MYSQL里面默认的行锁算法，相当于记录锁加上间隙锁。

其他两种退化情况：

唯一性索引，等值查询匹配到一条记录的时候，退化成记录锁。

没匹配到任何记录的时候，退化成间隙锁。

Transaction 1

Transaction 2

begin;

select * from user_lock_pk where id > 5 and id < 9 for update;

【不阻塞】-->

select * from user_lock_pk where id = 4 for update ;

【阻塞】-->

insert into user_lock_pk value(6,'张6',16);

【阻塞】-->

insert into user_lock_pk value(8,'张8',18);

【阻塞】-->

select * from user_lock_pk where id = 10 for update ;

临键锁，锁住最后一个Key的下一个左开右闭的区间。

Transaction 1

Transaction 2

begin;

select * from user_lock_pk where id > 5 and id <= 7 for update;

<--锁住( 4 , 7 ]和（ 7 , 10 ]

【阻塞】-->

insert into user_lock_pk value(6,'张6',16);

【阻塞】-->

insert into user_lock_pk value(8,'张8',18);

【阻塞】-->

select * from user_lock_pk where id = 10 for update ;

select * from user_lock_pk where id > 8 and id <= 10 for update;

<--锁住( 7 , 10 ]和 ( 10 , +∞ )

【阻塞】-->

insert into user_lock_pk value(8,'张8',18);

【阻塞】-->

select * from user_lock_pk where id = 10 for update ;

【阻塞】-->

insert into user_lock_pk value(11,'张11',20);

为什么要锁住下一个左开右闭的区间？——就是为了解决幻读的问题。

死锁

在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥的特性。一个事务或者说一个线程持有锁的时候，会阻止其他的线程获取锁，这个时候会造成阻塞等待，如果循环等待，就有可能造成死锁。

锁的释放与阻塞

锁在什么时候会被释放？

事务结束（commit , rollback）
客户端连接断开

如果一个事务一直未释放锁，其他事务会被阻塞多久？会不会永远等待下去？如果是，在并发比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL 有一个参数来控制获取锁的等待时间。

-- 查看innodb锁超时时间（默认50秒）
show variables like 'innodb_lock_wait_timeout';

对于死锁，是无论等多久都不能获取到锁的，这种情况，也需要等待 50 秒钟吗？那不是白白浪费了 50 秒钟的时间吗？

我们先来看一下什么时候会发生死锁。

死锁的发生和检测

死锁演示：

Transaction 1

Transaction 2

begin;

select * from user_lock_pk where id = 1 for update;

begin;

delete from user_lock_pk where id = 4;

update user_lock_pk set name = '张4-new' where id = 4;

【马上退出】[40001][1213] Deadlock found when trying to get lock; try restarting transaction

【获得锁】

delete from user_lock_pk where id = 1;

在第一个事务中，检测到了死锁，马上退出，第二个事务获得了锁，不需要等待50秒。

为什么可以直接检测到呢？是因为死锁的发生需要满足一定的条件，所以发生死锁时，InnoDB一般都能通过算法（wait-for graph）自动检测到。

死锁发生需要满足的条件：

同一时刻只能有一个事务持有这把锁
其他事务要在这个事务释放之后才能获取锁，而不可以强制剥夺
当多个事务形成等待环路的时候，即发生死锁。

举例：

如果一直没有释放，就有可能造成大量阻塞或者发生死锁，造成系统吞吐量下降，这时候就要查看是哪些事务持有了锁。

查看锁信息（日志）

-- 行锁信息
show status like 'innodb_row_lock%';

Variable_name

Value

说明

Innodb_row_lock_current_waits

当前正在等待锁定的数量

Innodb_row_lock_time

108942

从系统启动到现在锁定的总时间长度，单位ms

Innodb_row_lock_time_avg

6052

每次等待所花平均时间

Innodb_row_lock_time_max

33329

从系统启动到现在等待最长的一次所花的时间

Innodb_row_lock_waits

从系统启动到现在总共等待的次数

show命令是一个概要信息。InnoDB还提供了三张表来分析事务与锁的情况：

-- 当前运行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

-- 当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- 当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

找出持有锁的事务之后，如果有一个事务长时间持有锁不释放，可以kill事务对应的线程ID，也就是INNODB_TRX表中的trx_mysql_thread_id。

-- 死锁进程
show processlist ;

-- 删除线程
kill <trx_mysql_thread_id>;

-- 查询是否锁表
show OPEN TABLES where In_use > 0;

-- 查看当前有无行锁等待事件
-- 优化建议：
-- 若当前有行锁等待，则有可能导致锁超时被回滚，事务失败；
-- 有时候，可能是因为某个终端/会话开启事务，对数据加锁后，忘记提交/回滚，导致行锁不能释放。
SELECT
 lw.requesting_trx_id AS request_XID,
 trx.trx_mysql_thread_id as request_mysql_PID,
 trx.trx_query AS request_query,
 lw.blocking_trx_id AS blocking_XID,
 trx1.trx_mysql_thread_id as blocking_mysql_PID,
 trx1.trx_query AS blocking_query,
 lo.lock_index AS lock_index
FROM
 information_schema.innodb_lock_waits lw
 INNER JOIN information_schema.innodb_locks lo ON lw.requesting_trx_id = lo.lock_trx_id
 INNER JOIN information_schema.innodb_locks lo1 ON lw.blocking_trx_id = lo1.lock_trx_id
 INNER JOIN information_schema.innodb_trx trx ON lo.lock_trx_id = trx.trx_id
 INNER JOIN information_schema.innodb_trx trx1 ON lo1.lock_trx_id = trx1.trx_id;

当然，死锁的问题不能每次都靠kill线程来解决，这是治标不治本的行为。我们应该尽量在应用端，也就是在编码的过程中避免。