锁机制

在 Mysql 中，如果也有多个线程、多个事务并发访问某些资源（比如写同一行记录）时，也需要锁来保护，以确保数据的正确性。

1、分类

从对数据的操作类型来分：

  • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
  • 写锁（排它锁）：当前写操作没有完成前，会阻塞其它写和读操作

从对数据操作的粒度分：

  • 表锁：偏向 MyISam 引擎，开销小，加锁快；无死锁；锁定粒度大，锁冲突概率最高、并发度最低
  • 行锁：偏向 InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，锁冲突概率最低、并发度最高
2、基础命令

2.1 查看当前系统中，每个表被锁与否：

SHOW OPEN TABLES;

mysql表只读状态查询 mysql读数据会锁表吗_读锁

目前所有的 In_use 都是0，说明没有表被锁。

2.2 给 Employee 表加上读锁：

LOCK TABLE employee READ;

2.3 给 Department 表加上写锁：

LOCK TABLE department WRITE;

或者，3.2 和 3.3 的命令写在一起，像这样：

LOCK TABLE employee READ, department WRITE;

2.4 解锁：

UNLOCK TABLES;

现在，所有表都没有被加锁。

2.5查看系统上的表锁定信息

SHOW STATUS LIKE 'table%';

mysql表只读状态查询 mysql读数据会锁表吗_读锁_02

这个【Table_locks_waited】值比较重要，值越大说明等待锁的次数越多、竞争越大。

2.6InnoDB 引擎查看锁状态

SHOW STATUS LIKE '%innodb_row_lock%';

mysql表只读状态查询 mysql读数据会锁表吗_mysql表只读状态查询_03

等待平均时长、等待总时长、等待总次数，这三项比较重要。

3、读锁与写锁

（1）读锁

--Employee 表加上读锁
LOCK TABLE employee READ;

读取当前表

读锁对于读操作共享，加了读锁后，当前会话和其它会话都可以对加了读锁的表进行读操作。

读取其他表

  • 当前会话不能读取（其他表没有被锁定，不能读取）
  • 其它会话可以正常读取

更新当前表

  • 当前会话不能更新
  • 其它会话必须等到当前会话解除锁定后才能进行更新，否则一直挂起。

（2）写锁

读取当前表

  • 当前会话可以查询
  • 其他会话不能查询，解除锁定后查询

读取其它表

  • 当前会话不能读取其它表
  • 其它会话可以正常读取其它表

更新当前表

  • 当前会话可以更新（写操作）
  • 其它会话的更新操作会被阻塞
4、行锁与表锁

（1）行锁

只有InnoDB且where使用索引才支持行锁。

  • 更新不同的行，可以同时操作、不会相互影响。
  • 当更新同一行时，在前一个会话未提交之前，后一个会话的更新操作会被阻塞（挂起）。

a.记录锁

记录锁就是为某行记录加锁，封锁该行的索引记录。

--查询，id 为 1 的记录行会被锁住。
SELECT * FROM table WHERE id = 1 FOR UPDATE;

--在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作
UPDATE SET age = 50 WHERE id = 1;

id列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。

查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁

b.间隙锁

间隙锁基于非唯一索引，它锁定一段范围内的索引记录，基于Next-Key Locking 算法，

使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

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

在执行完某些 SQL 后，InnoDB 也会自动加间隙锁，如临键锁操作。

c.临键锁

Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

实例

id

age

name

1

10

Lee

3

24

Soraka

5

32

Zed

7

45

Talon

该表中 age 列潜在的临键锁有：
(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],

在事务 A 中执行如下命令：

-- 根据非唯一索引列 UPDATE 某条记录
UPDATE table SET name = Vladimir WHERE age = 24;

-- 或根据非唯一索引列 锁住某条记录
SELECT * FROM table WHERE age = 24 FOR UPDATE;

事务 B 中执行以下命令，则该命令会被阻塞：

--事务 A在对 age 为 24 的列进行 UPDATE 操作的同时，也获取了 (24, 32]这个区间内的临键锁。
INSERT INTO table VALUES(100, 26, 'Ezreal');

--InnoDB 会获取该记录行的临键锁，并同时获取该记录行下一个区间的间隙锁(10,24)。
INSERT INTO table VALUES(100, 20, 'Ezreal');

即事务 A在执行了上述的 SQL 后，最终被锁住的记录区间为 (10, 32)。

总结

  1. InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
  2. 记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
  3. 间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
  4. 临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。

（2）表锁

发生在非InnoDB引擎或InnoDB但where无索引或索引失效情景。

更新操作时，表会被锁住。

5、悲观锁与乐观锁

悲观锁

乐观锁

概念

查询时直接锁住记录使得其它事务不能查询，更不能更新

提交更新时检查版本或者时间戳是否符合

语法

select … for update

使用 version 或者 timestamp 进行比较

实现者

数据库本身

开发者

适用场景

并发量大

并发量小

类比Java

Synchronized关键字

CAS 算法

（1）悲观锁

悲观锁：对数据被外界（包括当前系统的其它事务，以及来自外部系统的事务处理）**修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。**悲观锁的实现，往往依靠数据库提供的锁机制。

类似Java中的 Synchronized 关键字。只要对代码加了 Synchronized 关键字，JVM 底层就能保证其线程安全性。

--语法
select * from employee where id = 1 for update;

事务A使用了select ... for update 的悲观锁后，事务B再想使用将被阻塞，同时，阻塞被解除后事务B能看到事务A对数据的修改（事务A修改生效），这就可以很好地解决并发事务的更新丢失问题。

（2）乐观锁

乐观锁：提交更新时检查版本或者时间戳是否符合。

乐观锁在数据库上的实现完全是逻辑的，数据库本身不提供支持，而是需要开发者自己来实现。

常见做法

a.版本号控制

  • 为表中加一个 version 字段；
  • 当读取数据时，连同这个 version 字段一起读出；
  • 数据每更新一次就将此值加一；
  • 当提交更新时，判断数据库表中对应记录的当前版本号是否与之前取出来的版本号一致，如果一致则可以直接更新，如果不一致则表示是过期数据需要重试或者做其它操作（PS：这完完全全就是 CAS 的实现逻辑呀~）

b.时间戳控制

其原理和版本号控制差不多，也是在表中添加一个 timestamp 的时间戳字段，然后提交更新时判断数据库中对应记录的当前时间戳是否与之前取出来的时间戳一致，一致就更新，不一致就重试。