MySQL锁

MySQL锁

数据库锁

锁的简介

为什么需要锁？

到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？

锁的概念

当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。

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

​ 在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。锁对数据库而言显得尤其重要，也更加复杂。

​ 我们可以用商场的试衣间来做个比喻。商场里得每个试衣间都可供多个消费者使用，因此可能出现多个消费者同时试衣服需要使用试衣间。为了避免冲突，试衣间装了锁，某一个试衣服的人在试衣间里把锁锁住了，其他顾客就不能再从外面打开了，只能等待里面的顾客，试完衣服，从里面把锁打开，外面的人才能进去。

锁的分类

乐观锁

​ 机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。相对悲观锁而言，乐观锁更倾向于开发运用。

悲观锁

​ 具有强烈的独占和排他特性。它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

锁的粒度

这里需要说明的是，悲观锁是一种思想，它的实现是使用了 共享锁与排他锁来实现的。因此悲观锁本身并不是MySQL实现的锁机制，它是我们造出来的一个概念。

另外，我看到很多文章在讲悲观锁时，只说排他锁是悲观锁机制，没有说共享锁是什么机制，而我认为共享锁也属于悲观锁，具体原因往后看。

表级锁

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

​ 是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁分为表共享读锁与表独占写锁。

行级锁

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

​ 是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

页级锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。在这个部分只讲表级锁、行级锁，gap锁放到事务中讲。

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

表锁与行锁的使用场景

• 表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如OLAP系统。
• 行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM锁

MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。随着应用跟对事物完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎。但是MyISAM的表锁依然是使用最为广泛的锁类型。

查询表级锁的争用情况

可以通过检查Table_locks_waited和Table_locks_immediate状态变量来分析系统上的表锁定争夺

1
2
3
4
5
6
7
8
9
10
11

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 644   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 15    |
| Table_open_cache_misses    | 10    |
| Table_open_cache_overflows | 1     |
+----------------------------+-------+
5 rows in set (0.00 sec)

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

表级锁的锁模式

MySQL的表级锁有两种模式：

• 表共享读锁（Table Read Lock）
• 表独占写锁（Table Write Lock）

可见，对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的度和写操作；MyISAM表的读写操作之间，以及写操作之间是串行的。

准备工作

创建一个myisam引擎的testmyisam的表

1

CREATE TABLE testmyisam (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(20) NOT NULL ) ENGINE=MYISAM CHARSET=utf8;

共享读锁

语法：lock table 表名 read

执行时序

事务A操作

对testmysam表进行加锁（只读）

1
2

mysql> lock table testmyisam read;
Query OK, 0 rows affected (0.00 sec)

事务B操作

查询

1
2
3
4
5
6
7

mysql> select * from testmyisam;-- 可以查询
+----+---------------+
| id | name          |
+----+---------------+
|  1 | 联想tinkpad   |
+----+---------------+
1 row in set (0.00 sec)

事务A操作

插入或者修改数据报错

1
2
3
4

mysql> insert into testmyisam(name) values('联想tinkpad');
ERROR 1099 (HY000): Table 'testmyisam' was locked with a READ lock and can't be updated
mysql> update testmyisam set name='xxxxx' where id=1;
ERROR 1099 (HY000): Table 'testmyisam' was locked with a READ lock and can't be updated

事务B操作

插入数据进入等待状态

1

insert into testmyisam(name) values('联想tinkpadxxx'); //等待...

事务A操作

不同表的操作插入数据

1
2
3
4
5

mysql>  INSERT INTO account (NAME,age) VALUE('aa',123);
ERROR 1100 (HY000): Table 'account' was not locked with LOCK TABLES
-- 查询数据
mysql> select * from account;
ERROR 1100 (HY000): Table 'account' was not locked with LOCK TABLES

事务B操作

插入数据成功

1
2

mysql> INSERT INTO account (NAME,age) VALUE('aa',123);
Query OK, 1 row affected (0.00 sec)

事务A操作

进行加锁操作

1
2

mysql> select s.* from testmyisam s;
ERROR 1100 (HY000): Table 's' was not locked with LOCK TABLES

语法：lock table 表名 as 别名 read;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> lock table testmyisam as t READ; -- 加锁
Query OK, 0 rows affected (0.00 sec)

mysql> select t.* from testmyisam t;  -- 通过锁别名进行访问
+----+------------------+
| id | name             |
+----+------------------+
|  1 | 联想tinkpad      |
|  2 | 联想tinkpad      |
|  3 | 联想tinkpadxxx   |
+----+------------------+
3 rows in set (0.00 sec)

mysql> select * from  testmyisam;  -- 普通的访问方式报错
ERROR 1100 (HY000): Table 'testmyisam' was not locked with LOCK TABLES

解读锁：语法 ： unlock tables;

1
2

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

事务B操作

然后我们发现我们另一个阻塞的插入执行成功了

1
2

mysql> insert into testmyisam(name) values('联想tinkpadxxx'); -- 没有解锁前一直阻塞
Query OK, 1 row affected (9.62 sec)

独占写锁

语法：lock table 表名WRITE

执行时序

事务A操作

对testmysam表进行加写锁

1
2

mysql> lock table testmyisam WRITE;  -- 加写锁
Query OK, 0 rows affected (0.00 sec)

在同一个session中操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

mysql> select * from testmyisam;  -- 查询
+----+------------------+
| id | name             |
+----+------------------+
|  1 | 联想tinkpad      |
|  2 | 联想tinkpad      |
|  3 | 联想tinkpadxxx   |
|  4 | 联想tinkpadxxx   |
+----+------------------+
4 rows in set (0.00 sec)

mysql> insert into testmyisam(name) values('联想tinkpadwwww');  -- 插入数据
Query OK, 1 row affected (0.00 sec)

mysql> delete from  testmyisam where id = 2;  -- 删除数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from testmyisam;  -- 查询
+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | 联想tinkpad       |
|  3 | 联想tinkpadxxx    |
|  4 | 联想tinkpadxxx    |
|  5 | 联想tinkpadwwww   |
+----+-------------------+
4 rows in set (0.00 sec)

对不同表操作(报错）

1
2
3
4
5

mysql> select s.* from testmyisam s;  -- 别名查询
ERROR 1100 (HY000): Table 's' was not locked with LOCK TABLES

mysql> INSERT INTO account (NAME,age) VALUE('aa',123);  -- 查询account
ERROR 1100 (HY000): Table 'account' was not locked with LOCK TABLES

事务B操作

在其他session中操作(等待)

1
2

select * from  testmyisam;  -- 等待
insert into testmyisam(name) values('联想tinkpadxxx');  -- 等待

其他表的操作

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> INSERT INTO account (NAME,age) VALUE('aa',123);  -- 插入
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | aa   |  123 |
|  2 | aa   |  123 |
|  3 | aa   |  123 |
|  4 | aa   |  123 |
+----+------+------+
4 rows in set (0.00 sec)

事务A操作

解写锁,语法 ： unlock tables;

1
2

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

总结

• 读锁：对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求。

• 读锁：对MyISAM表的读操作，不会阻塞当前session对表读，当对表进行修改会报错。

• 读锁：一个session使用LOCK TABLE命令给表f加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误。

• 写锁：对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。

• 写锁：对 MyISAM表的写操作，当前session可以对本表做CRUD,但对其他表进行操作会报错。

InnoDB锁

InnoDB默认使用行锁，实现了两种标准的行锁——共享锁与排他锁；

共享锁

又称读锁：当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

​ 一般来讲，共享锁主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作（包括加锁的事物也只能读）。简单说就是大家都可以读数据，但是无法修改（更新或者删除），因此我认为::共享锁也是悲观锁::的一种实现。

排它锁

又称写锁：当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

​ 排他锁是与共享锁相对应，自身加排他锁的事物能够自己发起修改操作，其它事物无法再对该数据加共享或者排他锁。

​ 这里需要注意上面说到的一点，由于InnoDB引擎是行锁，不管我们在这条数据上加了共享锁还是排他锁，简单的select语句依然可以使用的，因为默认在InnoDB中select是不加锁的。

​ 这里还有一点，上图中我们写到一个 间隙锁，这是什么东西？比如：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。它存在的主要目的有一个是为了解决幻读问题，因为RR作为InnoDB的默认事物隔离级别，是存在幻读问题的，而我们在实际操作中确没有出现，就是因为这里做了处理。

​ 关于乐观锁是如何加锁的，这个不同系统有不同的实现，简单来说，对每一个数据维护一个版本号，每次读取时把版本号读取出来，更新时版本号+1。然后更新时将读取的版本号作为条件，如果有其它事物更新了，那么必然会导致版本号变化，因为本次更新不会成功。这种机制最大程度的保证了并发。

语法

共享锁

1

lock in share mode

例如：select * from 表 where 条件 lock in share mode;

排它锁

1

for update

例如：select * from 表 where 条件 for update;

注意

• 两个事务不能锁同一个索引。
• insert ，delete ， update在事务中都会自动默认加上排它锁。
• 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

查看锁情况

下面的语句能够展示当前系统锁的情况，当系统锁争用比较严重的时候，Innodb_row_lock_waits 和 Innodb_row_lock_time_avg 的值会比较高。上面的数据是由于我做实验导致的。大家可以检查下自己的系统。

1
2
3
4
5
6
7
8
9
10
11

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

准备工作

1
2
3
4
5
6
7
8
9
10

use innodatabase;
CREATE TABLE testdemo (
`id`  int(255) NOT NULL ,
`c1`  varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`c2`  int(50) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX `idx_c2` (`c2`) USING BTREE
)
ENGINE=InnoDB;
insert into testdemo VALUES(1,'1',1),(2,'2',2);

示例

排他锁

我们发现使用排他锁 对同一条数据修改 等待，对其他数据修改是正常的

行级锁

我们对条件是id的数据进行了事务内的修改，另一个事务的修改：

• 根据id修改相同的数据 成功
• 根据id修改不同的数据 等待
• 根据非索引列修改不同的数据 等待

我们总结发现 mysql的InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！

表级锁

我们对条件是c1的数据进行了事务内的修改，另一个事务的修改：

• 根据c1修改不同的数据 等待
• 根据id修改不同的数据 等待

总结 ： 根据非索引列进行事务修改使用的是表级锁，对其他事务都是全表锁定

共享锁

我们开启了事务，在另一个事务中操作：

• 获取共享锁 等待
• UNLOCK TABLES 继续等待
• ROLLBACK 退出等待状态

总结：回到第一个session UNLOCK TABLES 并不会解锁，使用commit 或者 begin或者ROLLBACK 才会解锁

表锁

我们在一个事务中开启表锁，另一个事务中

• 修改 等待
• 查询 等待
• commit 提交 继续等待
• UNLOCK TABLES 退出等待状态

总结：使用commit，ROLLBACK 并不会解锁，使用UNLOCK TABLES或者begin会解锁。

什么时候会锁表

我们常常说InnoDB是行锁，但是这里介绍一下它锁表的情况。

​ InnoDB行锁是通过索引上的索引项来实现的，这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！
​ 在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

​ 当where条件不是索引时，如果加了排他锁，对这个表其它行记录也不能再加排他锁了，这明显就是锁住了整个表。而如果条件是索引字段，则它只会对where条件指定的行数据加锁，另一个事务可以对其它行数据加锁。

间隙锁

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

间隙锁的产生

上面的文字很抽象，现在举个栗子，介绍间隙锁是怎么产生的：

假设有以下表account：（其中id为PK，name为非唯一索引）

这个时候我们发出一条这样的加锁sql语句：

select id,name from accountwhere id > 0 and id < 5 for update;

这时候，我们命中的数据为以下加粗部分：

select … for update这条语句，是会对数据记录加锁的，这里因为命中了索引，加的是行锁。从数据记录来看，这里排它锁锁住数据是id为1、3和4的这3条数据。

但是，看看前面我们的介绍——对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁。

好了，我们这里，键值在条件范围但是不存在的记录，就是id为2的记录，这里会对id为2数据加上间隙锁。假设这时候如果有id=2的记录insert进来了，是要等到这个事务结束以后才会执行的

间隙锁的效果

时间序列

​ 我们发现对有些条件加锁的话会对这些条件范围内的数据加锁而不仅仅只是在这几条数据上加锁。

间隙锁的作用

总的来说，有2个作用：防止幻读和防止数据误删/改

防止幻读

如果没有间隙锁，事务A在2和5读到的结果是不一样的，有了间隙锁，读的就是一样的了.

防止数据误删/改

这种情况下，如果没有间隙锁，会出现的问题是：id为2的记录，刚加进去，就被删除了，这种情况有时候对业务，是致命性的打击。加了间隙锁之后，由于insert语句要等待事务A执行完之后释放锁，避免了这种情况

使用间隙锁的隐患

最大的隐患就是性能问题

前面提到，假设这时候如果有id=2的记录insert进来了，是要等到这个事务结束以后才会执行的，假设是这种场景

这种情况，对插入的性能就有很大影响了，必须等到事务结束才能进行插入，性能大打折扣

更有甚者，如果间隙锁出现死锁的情况下，会更隐晦，更难定位

锁的等待问题

​ 在工作中经常一个数据被锁住，导致另外的操作完全进行不下去。你肯定碰到过这问题，有些程序员在debug程序的时候，经常会锁住一部分数据库的数据，而这个时候你也要调试这部分功能，却发现代码总是运行超时，你是否碰到过这问题了，其实这问题的根源我相信你也知道了。

举例来说，有两个会话。

程序员甲，正直调试代码：

1
2

BEGIN;
SELECT * FROM testdemo WHERE id = 1 FOR UPDATE;

你正直完成的功能也要经过那部分的代码，你得上个读锁

另外一个session执行下面代码：

1
2

BEGIN;
SELECT * FROM testdemo WHERE id = 1 lock in share mode;

这个时候很不幸，你并不知道发生了什么问题，在你调试得过程中永远就是一个超时得异常，而这种问题不管在开发中还是在实际项目运行中都可能会碰到，那么怎么排查这个问题呢？

这其实也是有小技巧的。

1

select * from information_schema.INNODB_LOCKS;

我通过这个sql语句起码发现在同一张表里面得同一个数据有了2个锁其中一个是X（写锁），另外一个是S（读锁），我可以跳过这一条数据，使用其他数据做调试。

可能如果我就是绕不过，一定就是要用这条数据呢？吼一嗓子吧（哪个缺德的在debug这个表，请不要锁着不动），其实还有更好的方式来看。

1
2
3
4
5
6
7
8

SELECT 
  waiting_query,
  blocking_query,
  blocking_lock_mode,
  sql_kill_blocking_query,
  sql_kill_blocking_connection
FROM
  sys.innodb_lock_waits ;

1
2

mysql> KILL 84;
Query OK, 0 rows affected (0.00 sec)

​ 我现在执行的这个sql语句有了，另外看下最下面，kill命令，你在工作中完全可以通过kill把阻塞了的sql语句给干掉，你就可以继续运行了，不过这命令也要注意使用，如果某同事正在做比较重要的调试，你kill，被他发现可能会被暴打一顿。

​ 上面的解决方案不错，但如果你的MySQL不是5.7的版本呢？是5.6呢，你根本就没有sys库，这个时候就难办了，不过也是有办法的。

1. 同理在本地MySQL5.6里面执行下面查询：

1
2

BEGIN;
SELECT * FROM testdemo WHERE id = 1 FOR UPDATE;

2. 然后在另外一个session里面执行语句：

1
2

BEGIN;
SELECT * FROM testdemo WHERE id = 1 lock in share mode;

3. 最后在执行下面的锁查询语句：

1
2
3
4
5
6
7
8
9
10
11
12

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread
FROM
  information_schema.innodb_lock_waits w
INNER JOIN
  information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN
  information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

看到没有，接下来你是否也可以执行kill 62 这样的大招了。

1
2
3

kill 3
> 1317 - Query execution was interrupted
> 时间: 0s

再次查询发现锁已经不存在了。