五、MySQL 锁机制

433 阅读20分钟

1. 概述

锁是计算机协调多个进程或线程并发访问某一种资源的机制。

在数据库中,除去计算机硬件资源(CPU、RAM、I/O等)的争用外,数据也是一种供许多用户共享的资源。如何保证并发访问数据的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素,因此锁对于数据库而言尤其重要,也更加复杂。

比如我们在淘宝抢购商品时,当两个客户同时抢购一个商品时,后台的数据库中必然用到事务和锁,通过对整个下单流程进行统一的事务交和数据库加锁机制,保证商品不会被超卖。

1.1 锁的分类

  • 数据库操作类型分类(读/写)
    • 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响;
    • 写锁(排他锁):当前操作没有完成前,会阻断其他的写锁和读锁;
  • 对数据的操作粒度:
    • 表锁:对数据表上锁
    • 行锁:对操作的数据行上锁

开销、加锁速度、死锁、粒度、并发性能只能根据具体应用的特点来说明哪种锁更合适。

这里推荐一篇关于 MySQL 锁相关的博客 MySQL的锁机制和加锁原理

2. 表锁(偏向读)

2.1 特点

偏向读操作,偏向 MYISAM 存储引擎,开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率最高,并发最低

2.2 示例

2.2.1 建立数据库

use big_data;

create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

查看创建的数据库

mysql> use base_crud;
Database changed
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

数据库锁的基本操作

  • 手动添加表锁:

    lock table 表名1 read(write), 表名2 read(write), 其他;

  • 查看上锁的表:

    show open tabels

  • 手动解锁:

    unlock tables

2.2.2 添加读锁

mylock 表添加读取锁,同时打开两个 session 窗口

session1 窗口

# 为 mylock 表添加读锁
mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)

# 查询 mylock 表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a1   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

# 更新 mylock 表
mysql> update mylock set name = 'a' where id  = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

# 查询 book 表
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

# 更新 book 表
mysql> update book set card = 10 where bookid = 1;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

# 释放锁
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)

session2窗口

# 查询 mylock 表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a1   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

# 查询 book 表
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
|      4 |    2 |
|     25 |    2 |
|     36 |    2 |
|     23 |    3 |
|     38 |    4 |
|     15 |    6 |
|     16 |    6 |
|     24 |    6 |
|     39 |    6 |
|     34 |   20 |
+--------+------+
10 rows in set (0.00 sec)

# 更新 book表
mysql> update book set card = 10 where bookid = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 更新 mylock 表
mysql> update mylock set name = 'a3' where id = 1;
Query OK, 1 row affected (28.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

由于session1 在 mylock 表加上读锁,导致队列阻塞,只有当session1 释放读锁时session2 才能对 mylock 表进行更新操作,因此更新操作等待时间很长。

加读锁后不同 session 对表操作总结

表操作当前 session其他 session
读取加了读锁的表YESYES
读取没有加读锁的表NOYES
更新/插入加了读锁的表NO阻塞等待
更新/插入没有加读锁的表NOYES
删除加了读锁操作的表NO阻塞等待
删除没有加读锁的表NOYES

2.2.3 添加写锁

mylock 表添加写入锁,同时打开两个 session 窗口

session1 窗口

# 1.1 给 mylock 表添加写锁
mysql> lock table mylock write;
Query OK, 0 rows affected (0.00 sec)

# 1.2 查询 mylock 表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a3   |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.01 sec)

# 1.2 查询 book 表
mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

# 2.1 更新 mylock 表
mysql> update mylock set name = 'b1' where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 2.2 更新后查询 mylock 表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a3   |
|  2 | b1   |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

# 3.1 更新 book 表
mysql> update book set card = 22 where bookid = 1;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

# 3.2 释放写锁
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)

session2 窗口

# 1.1 查询 mylock 表
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a3   |
|  2 | b1   |
|  3 | c3   |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (12.18 sec)

# 1.2 查询 book 表
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
|      4 |    2 |
|     25 |    2 |
|     36 |    2 |
|     23 |    3 |
|     38 |    4 |
|     15 |    6 |
|     16 |    6 |
|     24 |    6 |
|     39 |    6 |
|     34 |   20 |
+--------+------+
10 rows in set (0.00 sec)

# 1.3 更新 mylock 表
mysql> update mylock set name = 'c3' where id = 3;
Query OK, 1 row affected (10.42 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 1.4 更新 book 表
mysql> update book set card = 2222 where bookid = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 1.5 更新后查询 book 表
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
|     25 |    2 |
|     36 |    2 |
|     23 |    3 |
|     38 |    4 |
|     15 |    6 |
|     16 |    6 |
|     24 |    6 |
|     39 |    6 |
|     34 |   20 |
|      4 | 2222 |
+--------+------+
10 rows in set (0.00 sec)

由于session1 在 mylock 表加上写锁,导致队列阻塞,只有当session1 释放读锁时session2 才能对 mylock 表进行查询、更新、插入、删除操作,阻塞等待时间很长。

加写锁后不同 session 对表操作总结

表操作当前 session其他 session
读取加了写锁的表YES阻塞等待
读取没有加写锁的表NOYES
更新/插入加了写锁的表YES阻塞等待
更新/插入没有加写锁的表NOYES
删除加了写锁的表YES阻塞等待
删除没有加写锁的表NOYES

2.2.4 示例总结

MYISAM 在执行查询语句 SELECT 前,会自动给涉及到的所有表加读锁;在执行增删改操作前,会自动给涉及到的所有表加写锁

MySQL 的表级锁有两种模式:

  • 表共享读锁(Table Read Lock)
  • 表独占写锁(Table Write Lock)

对使用 MyISAM 文件存储引擎的表进行操作会出现以下情况:

  • MyISAM 表的读操作(加读锁)不会阻塞其他进程对同一表的读请求,但会阻塞同一表的写请求。当读锁释放后才会执行其他进程的写操作;
  • MyISAM 表的写操作(加写锁)会阻塞其他进程对同一表的读和写操作,只有写锁释放后,才能执行其他进程的读写操作;
  • 简而言之,就是读锁会阻塞写操作,但是不会阻塞读操作。而写锁则会把读操作和写操作都阻塞;

2.3 表锁分析

查看数据库中那些表加锁:show open tables;

1:表示加锁

0:表示没有加锁

分析表锁定

使用命令:show status like 'table%'

mysql> show status like 'table%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Table_locks_immediate | 510836 |
| Table_locks_waited    | 5      |
+-----------------------+--------+
2 rows in set (0.00 sec)

参数说明:

  • Table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁查询的次数,每立即获取锁值加 1;
  • Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加 1)数值高说明存在严重的表级锁争用情况;

MyISAM 的读写锁调度是写优先,因此不适合做写为主表的引擎。因为加锁后,其他线程不能做任何操作,大量的更新会使得查询很难获得锁,进而造成查询阻塞。

3. 行锁(偏向写)

3.1 特点

偏向 InnoDB 存储引擎,开销大,加锁慢;会出现死锁问题,锁的粒度小,发生锁冲突的概率最低,并发度是最高的。

InnoDB 引擎和 MyISAM 引擎最大的不同:InnoDB 支持事务(transaction)和行级锁。

3.2 事务相关

3.2.1 事务及其 ACID 属性

事务是由一组 SQL 语句组成的逻辑处理单元,事物具有以下四个属性,通常简称为事务的 ACID 属性

原子性(Atomicity):事务是一个原子操作,对数据的修改,要么全都执行,要么全都不执行;

一致性(Consistent):在事务开始和完成时,数据必须保持一致状态。这也就意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有内部数据结构(如:B 树索引或双向链表)也都必须是正确的;

隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务不受外部并发操作影响的**“独立环境”**执行。这意味着事务处理的中间状态对外部是不可见的,反之亦然;

持久性(Durable):事务完成后,对于数据的修改是永久性的,即使出现系统故障也能够保持;

3.2.2 并发带来的事务问题

更新丢失(Lost Update)

当两个或多个事务选择同一行,并基于最初选定的值更行当前行是,由于每个事务不知道其他事务的存在,最后的更新覆盖了由其他事务所作的更新,产生丢失更新的问题。

时间片事务 A事务 B
T1开始事务
T2开始事务
T3查询账户余额 300 元
T4查询账户余额 300 元
T5账户充值 200 元,余额 500 元
T6提交事务
T7消费 100 元,余额 200 元
T8撤销事务
T9余额 300 元

事务 A 最后提交事务,导致事务 B 的事务提交被覆盖,造成事务 B 更新的数据丢失

脏读(Dirty Reads)

事务 A 正在对一条记录做修改,这个事务完成提交前,这条记录的数据就处于待定状态(可能提交也可能回滚);此时,事务 B 也来读取这条待定状态的数据,并对数据做进一步的处理,就会产生数据依赖关系,这种现象叫做**“脏读”**。

简而言之,就是事务 B 读取到了事务 A 已修改但没有提交的数据,还在这个数据的基础上做了操作。此时事务 A 回滚,事务 B 读取到的数据无效,不符合一致性要求。

不可重复读(Non-Repeatable Reads)

一个事务先后读取同一条记录,但在事务的两次读取之间该数据被其他事务所修改或删除,两次读取到的数据不一致或不能读取,这种现象叫做**“不可重复读”**。

简而言之,就是事务 A 读取到了事务 B 已经提交的修改数据,不符合隔离性。

幻读(Phantom Reads)

一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象称为**“幻读”**

简而言之,事务A读取到了事务B提交的新增数据,不符合隔离性。

幻读和脏读比较:

  • 脏读是事务B里面修改了数据;
  • 幻读是事务B里面新增了数据;

幻读和不可重复读比较:

  • 不可重复读的重点是修改:同样的条件,两次读发现值不一样;
  • 幻读的重点在于新增或者删除:同样的条件,两次读发现得到的记录数不一样;

3.3.3 事务的隔离级别

DEFAULT

默认级别,由 DBA 默认的设置来决定隔离级别,归属下列某一种:

READ_UNCOMMITTED

就是一个事务可以读取另一个未提交事务的数据。

会出现脏读、不可重复读、幻读(隔离级别最低,但并发性高)

最低级别,只能保证不读取物理上损坏的数据;

READ_COMMITTED

就是一个事务要等另一个事务提交后才能读取数据,解决脏读问题。

会出现不可重复读、幻读问题(锁定正在读取的行,适用于大多数系统,Oracle默认级别)

语句级别;

REPEATABLE_READ

就是在开始读取数据(事务开启)时,不再允许修改操作,解决不可重复读问题。

会出现幻读问题(锁定所读的所有行,MYSQL默认级别)

事务级别;

SERALZABLE

是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。

但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。(锁整表)

最高级别,事务级别;

事务隔离级别由上到下依次提升,隔离级别越高,越能保证数据的完整性和一致性。但对数据库性能的消耗依次增加,并发执行效率依次下降。

事务隔离实质上就是使得事务在一定程度上**“串行”,实际上是与并发相矛盾的。同时,不同的应用对读一致性和事务的隔离级别是不同的,有些应用可能对“不可重复读”“幻读”**不敏感,更关心数据的并发处理能力。

查看当前数据库的事务隔离级别:show variables like 'tx_isolation'

大多数的数据库默认隔离级别为 Read Commited,比如 SqlServerOracle

少数数据库默认隔离级别为:Repeatable Read 比如:MySQL InnoDB

事务的隔离级别和并发出现的问题之间的关联

Dirty readsnon-repeatable readsphantom reads
READ_UNCOMMITTEDYYY
READ_COMMITTEDNYY
REPEATABLE_READNNY
SERALZABLENNN

3.3 示例

3.3.1 建表语句

-- 创建数据库
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
-- 创建索引
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
-- 关闭自动提交
SET autocommit=0;

查看创建的数据库

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

3.3.2 行锁示例

session 1 窗口

# 1.1更新数据
mysql> update test_innodb_lock set b='b3' where a = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 1.2查询数据
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | b3   |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)
# 1.3提交查询
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

# 2.1 更新行数据
mysql> update test_innodb_lock set b='3000' where a=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 2.2 更新后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | b3   |
|    4 | 3000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)
# 2.3 更新后提交
mysql> commit;
# 2.4提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | b3   |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)
# 2.5 session 2提交后提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 2.6 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | b3   |
|    4 | b4   |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

# 3.1 更新a=5
mysql> update test_innodb_lock set b='5005' where a=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 3.2 提交事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# 3.3 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | b3   |
|    4 | b4   |
|    5 | 5005 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9009 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

session 2 窗口

# 1.1 session 1 未提交 session 2 查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)
# 1.2 session 1提交后session 2提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 1.3 session 1和 session 2都提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | b3   |
|    4 | 4000 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

# 2.1 更新行数据,session 1尚未提交,此时会出现阻塞当session 1提交后session 2才能更新
mysql> update test_innodb_lock set b='b4' where a=4;
Query OK, 1 row affected (15.21 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 2.2 session 1提交事务后提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# 2.3 查询数据
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | b3   |
|    4 | b4   |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

# 3.1 更新a=9
mysql> update test_innodb_lock set b='9009' where a=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 3.2 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 3.3 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | b3   |
|    4 | b4   |
|    5 | 5005 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9009 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

3.3.3 索引失效行锁升级为表锁

session 1 窗口

# 1.1 查询当前数据
mysql> select * from test_innodb_lock;
+------+-------+
| a    | b     |
+------+-------+
|    1 | 10010 |
|    3 | b3    |
|    4 | b4    |
|    5 | 5005  |
|    6 | 6000  |
|    7 | 7007  |
|    8 | 8008  |
|    9 | 10010 |
|    1 | 10010 |
+------+-------+
9 rows in set (0.00 sec)
# 1.2 更新b=10010 所在行数据
mysql> update test_innodb_lock set a=20 where b=10010;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
# 1.3 提交事务
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
# 1.4 提交后查询
mysql> select * from test_innodb_lock;
+------+-------+
| a    | b     |
+------+-------+
|   20 | 10010 |
|    3 | 3000  |
|    4 | b4    |
|    5 | 5005  |
|    6 | 6000  |
|    7 | 7007  |
|    8 | 8008  |
|   20 | 10010 |
|   20 | 10010 |
+------+-------+
9 rows in set (0.00 sec)

session 2 窗口

# 1.1 查询当前数据
mysql> select * from test_innodb_lock;
+------+-------+
| a    | b     |
+------+-------+
|    1 | 10010 |
|    3 | b3    |
|    4 | b4    |
|    5 | 5005  |
|    6 | 6000  |
|    7 | 7007  |
|    8 | 8008  |
|    9 | 10010 |
|    1 | 10010 |
+------+-------+
9 rows in set (0.00 sec)
# 1.2 session 1更新后未提交,由于session 1索引失效导致行锁失效变成表锁,session 2更新不同行时被阻塞
mysql> update test_innodb_lock set b='3000' where a=3;
Query OK, 1 row affected (9.93 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# session 1提交后提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# 提交后查询
mysql> select * from test_innodb_lock;
+------+-------+
| a    | b     |
+------+-------+
|   20 | 10010 |
|    3 | 3000  |
|    4 | b4    |
|    5 | 5005  |
|    6 | 6000  |
|    7 | 7007  |
|    8 | 8008  |
|   20 | 10010 |
|   20 | 10010 |
+------+-------+
9 rows in set (0.00 sec)

行锁示例总结:

  • 当前session 更新数据后不提交,其他session 不能看到更新后的数据;
  • 当前session 更新一行数据未提交,其他 session 更新同一行数据时会阻塞;
  • 当前 session 和其他 session 更新不同的数据行时,不会发生阻塞;
  • 索引失效会导致行锁变成表锁;

3.3.4 间隙锁

当我们用范围查询而不是相等条件检索数据,并请求共享锁或排他锁时,InnoDB 会给符合条件的已有数据的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做**“间隙(GAP)”,InnoDB 也会对“间隙”加锁,这种锁机制就叫做“间隙锁(Next-Key 锁)”**。

间隙锁的危害:

Query 执行过程中通过范围查找的话,会锁住整个范围内所有的索引键值,即使这个值不存在。

间隙锁的一个致命弱点,就是当锁定一个范围值后,即使某些不存在的键值也会被锁定,会造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下可能会对性能造成很大影响。

session 1窗口

# 1.1 更新 1 < a < 6 的行 b 为 test
mysql>  update test_innodb_lock set b='test' where a>1 and a<6;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
# 1.2 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 1.3 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | test |
|    4 | test |
|    5 | test |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
+------+------+
9 rows in set (0.00 sec)

# 2.1 session 2提交后提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 2.2 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | test |
|    4 | test |
|    5 | test |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
|    2 | 2000 |
+------+------+
10 rows in set (0.00 sec)

# 3.锁定一行
# 3.1 打上起始点
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 3.2 查询锁定
mysql> select * from test_innodb_lock where a=8 for update;
+------+------+
| a    | b    |
+------+------+
|    8 | 8000 |
+------+------+
1 row in set (0.00 sec)
# 3.3 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 3.4 session 2提交后提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 3.5 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | test |
|    4 | test |
|    5 | test |
|    6 | 6000 |
|    7 | 7000 |
|    8 | xxxx |
|    9 | 9000 |
|    1 | b1   |
|    2 | 2000 |
+------+------+
10 rows in set (0.00 sec)

session 2窗口

# 1.1 session 1更新范围数据,产生间隙锁造成阻塞,session 1提交后执行
mysql> insert into test_innodb_lock values(2,'2000');
Query OK, 1 row affected (7.61 sec)
# 1.2 session 1提交后提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
# 1.3 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | test |
|    4 | test |
|    5 | test |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b1   |
|    2 | 2000 |
+------+------+
10 rows in set (0.00 sec)

# 3.锁定一行
# 3.1 更新一行,由于session 1查询时锁定行,导致执行阻塞。session 1提交后才能执行
mysql> update test_innodb_lock set b='xxxx' where a=8;
Query OK, 1 row affected (7.90 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# 3.2 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
# 3.3 提交后查询
mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | test |
|    4 | test |
|    5 | test |
|    6 | 6000 |
|    7 | 7000 |
|    8 | xxxx |
|    9 | 9000 |
|    1 | b1   |
|    2 | 2000 |
+------+------+
10 rows in set (0.00 sec)

面试问题:如何锁定行?

select xxxx... for update 锁定某一行后,其他的操作会被阻塞,直到锁定行的会话提交。

3.3.5 总结

InnoDB 存储引擎由于实现了行级锁定,虽然在锁定机制实现方面所带来的性能损耗比表级锁更多,但是在整体并发处理能力方面要远优于 MyISAM 的表级锁定的。当系统并发量比较高的时候,InnoDB 的整体性能和 MyISAM 相比会有比较明显的优势。

InnoDB 的行级锁同样也存在问题,当我们不当使用时,可能会使 InnoDB 的整体性能表现比 MyISAM 更差。

3.4 行锁分析

通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁争夺情况

查看命令 show status like '%innodb_row_lock%'

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 91944 |
| Innodb_row_lock_time_avg      | 18388 |
| Innodb_row_lock_time_max      | 51296 |
| Innodb_row_lock_waits         | 5     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

状态变量说明:

Innodb_row_lock_current_waits:当前正在等待锁定的数量;

Innodb_row_lock_time:从系统启动到现在锁定总时间;

Innodb_row_lock_time_avg:每次平均等待时间;

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

Innodb_row_lock_waits:系统启动后到现在总共等待的次数;

五个状态变量中比较重要的:

Innodb_row_lock_time_avg:每次平均等待时间;

Innodb_row_lock_waits:系统启动后到现在总共等待的次数;

Innodb_row_lock_time:从系统启动到现在锁定总时间;

尤其 是当前等待次数很高,而且每次等待时长也很长的时候,需要分析系统中出现多次等待的原因,并根据分析结果指定优化计划。

3.5 优化建议

① 尽可能让所有数据检索都通过索引完成,避免无索引行锁升级为表锁;

② 合理设计索引,尽量缩小锁的范围;

③ 尽可能减少检索条件,避免间隙锁;

④ 尽量控制事务大小,减少锁定资源量和时间长度;

⑤ 尽可能使用低级别的事务隔离;

4. 页锁

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