MySQL中到底有哪些锁

910 阅读18分钟

之前在“分布式锁实现方案到底有哪些”一文中提到了锁这个概念,其实在MySQL中也是有锁的概念的,可能是大家对他不够了解,平日里也很少去使用,所以在谈论MySQL锁的时候,大家有点谈“锁”色变。随着工作经验的增长和业务的复杂程度的升高,我们又随时可能遇到因为MySQL死锁导致的问题,开始的时候大家可能就直接kill掉死锁的线程,但是没有找到具体死锁的原因,死锁的问题还是会出现。所以大家不能再当一个将头插入沙里的鸵鸟了,今天就跟我来学习MySQL锁相关的基础知识,让我们把握住他!(以下数据库皆指的是MySQL)

这里的水很深,你把握不住

什么是锁

这里呢就不在多说,直接把之前的文章的介绍copy过来!为啥你要这样做,因为虽然所处的环境不一样,但是这个锁的概念都是一样的啊,知识都是有关联且相通的,要学会举一反三!

一般来说当代码中会有多个线程任务访问同一共享资源时,就会出现冲突或者错误,目前解决这种问题都是采用序列化访问共享资源的解决方案,即将共享资源放在某一代码块中并加锁,某一时刻只能有一个线程访问该代码块。从而我们可以得出结论,锁是一种方案,一种解决多线程访问共享资源不出问题的解决方案。

有哪些锁

MySQL里的锁和Java中的锁一个德行,就是种类特别多。我这里先随便的说几个,例如共享锁、排它锁、记录锁、gap锁、next key锁、意向锁等等。是不是有很多锁,注意,这里其实没有说完,还有其他的锁。锁虽然很多,但是我们可以进行分个类,这样好介绍他们。

MySQL锁的分类

上图中展示了MySQL中存在的锁的,我们常用的基本都在里面了。第4个分类按照对共享资源加不加锁来区分锁的,这个概念Java中也是存在的,例如java 中AtomicInteger,就是基于乐观锁思想的实现 ------ CAS(CompareAndSwap) 比较并交换。由此可见很多知识就是TM的相同的呀!

为啥有这么多锁

我们对数据库进行操作,操作的过程中可能涉及多个SQL,但是中途我们想撤销操作或者操作被中断,我们想让数据库中的数据回到最开始的状态,让数据前后保持一致。为了解决这个问题,大佬们提出事务这个概念。事务具有以下四个特点:

  • 原子性(Atomicity):事务是一个完整的操作。事务的各步操作是不可分的(原子的);要么都执行,要么都不执行。

  • 一致性(Consistency):当事务完成时,数据必须处于一致状态。

  • 隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的,这表明事务必须是独立的,它不应以任何方式依赖于或影响其他事务。

  • 永久性(Durability):事务完成后,它对数据库的修改被永久保持,事务日志能够保持事务的永久性。

在MySQL中,如果不显示的开启事务,那么每一条语句均为一个独立的事务。

mysql> show global variables like  '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

那在MySQL这里,如果不安全的访问会出现啥问题呢。大家把数据按照一定的规则存放在MySQL中,然后根据业务场景需要,对数据进行增删改查,即江湖中常说的“CURD”。同一时间(并发)可能会有多个人操作,对数据进行“CURD”,如果不处理好,那就会导致数据错误,然后就没有然后了!

那么就来详细的说一下,并发事务会出现什么问题?

  1. 如果事务A修改未提交事务B修改的数据,这就说明发生了脏写
  2. 如果事务A读取到了未提交事务B修改的数据,这就说明发生了脏读
  3. 如果事务A修改了未提交事务B读取的数据,这就说明了不可重复读。更严格的来说,就是事务A先读取了记录a,接着事务B修改了记录a并提交事务,事务A再次读取记录a时,发现记录a的值发生了变化。
  4. 如果事务A按照一定的条件查询到了一些记录,在事务A没有提交事务之前,事务B写入了一些符合事务A搜索条件的记录,事务A再次按照那些条件查询时,发现查询到的记录比之前多了,这就意味着事务A发生了幻读

对于这些问题,SQL标准提出了一个事务隔离级别的概念,具体分为:READ UNCOMMITTED 读未提交、READ COMMITED 读已提交、REPEATABLE READ 可重复读、SERLALIZABLE 串行化;不同的隔离级别特点如下:

  • READ UNCOMMITTED 读未提交,该级别下脏读、不可重复读、幻读都可能发生。
  • READ COMMITED 读已提交,该级别下不可重复读、幻读都可能发生,脏读不允许发生。
  • REPEATABLE READ 可重复读,该级别下幻读都可能发生,脏读、不可重复读不可能发生。
  • SERLALIZABLE 串行化,上述情况都不能发生。

意思就是各个数据库,你们按照我这个标准去解决这些问题就行。Oracle数据库呢比较横,他其实就搞定了两个标准:READ COMMITED 读已提交、SERLALIZABLE 串行化,他可能想搞那么多玩意干啥?这两个不就足够了么,瘪犊子玩意!然后自己的事务隔离级别就默认读已提交。MySQL可能就比较老实,他把上面SQL定的是个事务隔离级别都搞定了,在这里给MySQL点个赞!MySQL默认事务隔离级别选择的是可重复读。有人会想,为啥没有提出解决脏读问题,不是没有解决脏读,而是脏读的问题必须解决,无论在什么级别脏读的问题都必须解决,所以上面就没有提。

这里大家可能还有个疑问,为啥两个数据库的默认隔离级别不一样?其实,MySQL是有自己的苦衷的,当然具体是啥原因,如果要解释的话,牵涉的东西还蛮多的!这里就先按下不表,有机会的下次再说!

避免脏读、不可重复读、幻读其实有两种可选的解决办法的:

  1. 我们知道读-读可以并行,不用加锁。那么读操作就用MySQL的多版本并发控制(MVVC),写操作加锁来解决。
  2. 读写操作都采用加锁的方式。

很明显采用MVCC方式,读写操作彼此不重复,性能更高。采用加锁的方式的话,读写要排队,效率有时肯定比不上前者。但是在一些特殊的场景,如解决幻读,就必须采用加锁的方式解决。总而言之,为了摆平不同场景,保证他们不出问题,于是乎就出现了各种各样的锁。

各种各样的锁

前文图中的MySQL锁分的很细,其实很多锁可以同时属于不同的分类,下面介绍的时候,也就不按照图中分类的方式介绍了。

1. 乐观锁

乐观锁其实是一种概念,不止MySQL有,这个前文也有提到。他其实是一种对共享资源的一种概念,认为在操作资源的过程中,不会有其他人操作了资源,所以不加锁,只在最后更新数据的时候去判断数据有没有被更新,如果没有更新则提交自己的操作,如果有更新则进行其他操作,例如报错或者重试。

乐观锁的简单实现很简单,就像前文说到的CAS一样。那具体使用场景我来举个例子,假设现在有更新商品库存stock的场景:订单支付完成,id=3000的商品库存stock减去2。

大家一般的写法可能是这样的:

UPDATE goods SET stock = stock - 2 WHERE id = 3000;

这种写法的SQL一般情况下是没有啥问题的,但是我们写代码,**部分代码是为了完成功能,另外一部分代码都是在保障异常情况下程序不出错。**在高并发的异常情况下或者在弱网环境下客户短时间多次点击了下单按钮,上面的SQL就会出现问题:会导致商品的库存出现负数,即商品出现超卖。借鉴CAS的原理稍微改动一下就可以避免大部分的问题,新写法如下:

SELECT stock as oldStock FROM goods WHERE id = 3000;
UPDATE goods SET stock = stock - 2 WHERE id = 3000 AND stock = oldStock AND stock >= 2 
  1. 更新的时候把之前的库存写到where条件上,当库存是oldStock,我们再去更新,防止在更新之前有人已经操作了库存。
  2. 再加上一个库存大小的判断,防止库存被扣成负数。

其实,上面的在WHERE条件中,在某些场景下还是会有问题,具体来说就是ABA问题,这个问题要解决,最好的方式就是直接加个版本号version字段,进行版本控制,更新的时候条件中添加版本的比较。

2. 悲观锁

悲观锁相对于乐观锁,就是不那么乐观,认为自己在操作数据的时候一定有别人来修改数据,因此在获取数据的时候会先加锁,确保数据不会被别的线程修改。

上面那个扣除商品库存的问题,其实也可以用悲观锁来实现,读取数据的时候,直接将数据加锁,操作完成才是否锁。

从上面的概念我们可以得出一些结论,乐观锁适合在读多写少的场景下使用,不加锁的特点可以能够使得系统的性能大幅度提升。悲观锁适合在写多的场景下使用,操作数据前先加锁保证写操作时数据的正确性。


3. 共享锁

共享锁(Shared Lock):简称S锁。因为读读是可以并行的,所以事务T1获得了记录R1的S锁后,其他事务仍然可以获得记录R1的S锁,即S锁相互兼容。

那么如何读取数据的时候,给记录加上S锁呢?

SELECT …… LOCK IN SHARE MODE
4. 排它锁

排它锁(eXclusive Lock),可以称之为独占锁或者是写锁,简称X锁。在事务中要修改一条记录时,需要首先获取这条记录的X锁。既然是叫做排他锁了,X锁与X锁、X锁与S锁相互不兼容。

那么如何读取数据的时候,给记录加上X锁呢?

SELECT …… FOR UPDATE

所以,上面的库存扣除用FOR UPDATE实现如下:

BEGIN;
SELECT * FROM goods WHERE id = 3000 FOR UPDATE;
UPDATE goods SET stock = stock - 2 WHERE id = 3000;
COMMIT;

不过,这种做法比较霸道,数据准确性肯定是没有问题,但是效率肯定就不好了。


MySQL中把在读取记录之前就给记录加锁的读取方式称为锁定读(LOCK READ)。在MySQL中S锁和X锁均属于行锁

假设事务T1获取记录R1的S锁,之后事务T2也想访问记录R1:

  • 如果事务T2也想获得记录R1的S锁,那么事务T2也可以获得该锁。
  • 如果事务T2也想获得记录R1的X锁,那么操作会被阻塞,直到事务T1结束释放了S锁为止。
兼容性X锁S锁
X锁不兼容不兼容
S锁不兼容兼容
5. 表级锁、页级锁、行锁

前面说到按照加锁的粒度来分,MySQL中有表级锁、页级锁、行锁。页级锁比较少见,表级锁也还行,最重要的就是行锁,前面提到的S锁和X锁就是行数;这个地方我先介绍一下表级锁。 表级锁也分成共享锁(S锁)和独占锁(X锁), 一个事务给表加了S锁:

  1. 其他的事务仍然可以给该表加S锁。
  2. 其他的事务仍然可以给表中的某些记录加S锁。
  3. 其他的事务不能给该表加X锁。
  4. 其他的事务不能继续再给表中的某些记录加X锁。

一个事务给表加了X锁:

  1. 其他的事务不能给该表加S锁。
  2. 其他的事务不能给表中的某些记录加S锁。
  3. 其他的事务不能给该表加X锁。
  4. 其他的事务不能继续再给表中的某些记录加X锁。
6. 记录锁

记录锁顾名思义就是加在记录上的锁(Record Lock),前面提到的S锁和X锁可以称之为记录锁。

7. 间隙锁

间隙锁(Gap Lock),他的提出主要是为了防止插入幻影记录提出来的。在这里要强调一点,MySQL中的锁都是基于索引实现的。我们加锁可以解决脏读、脏写的问题,但是发生不可重复读的时候,是因为数据在事务开启之后才插入的,我们根本没有法子给这样的数据加锁,但是天才般的MySQL开发者们想出了一个给索引间隙加锁的方法,这样就解决了不可重复读。 间隙锁虽然也分为共享间隙锁和独占间隙锁,但是他们的作用都是一样的。一个事务对记录R1加了间隙锁,无论是共享还是独占型的,并不限制其他事务继续对记录R1加记录锁或者加Gap锁。

有人可能会问了,给一条记录加Gap锁只是不允许其他事务在这记录的前面的空隙插入记录,但是对于表中的最开始的那条记录和最后一条记录的空隙怎么办呢?这个难不倒MySQL的大神开发者,在MySQL的数据页中有两条伪记录:

  • Infimum记录:表示该页面中最小的记录。
  • Supernum记录:表示该页面中最大的记录。

假设我们给记录255是最后一条记录,加他了Gap锁,就相当于在(255,++\infty)上加了锁,其他的事务休想在这个范围内插入数据。看看问题是不是完美的解决了。

间隙锁在一般情况下,只有在不小于可重复读的事务隔离级别下出现,但是特殊情况下,读已提交的隔离级别下也会有。

8. next key 锁

MySQL在可重复度的事务隔离级别下,在一定程度下解决了幻读的问题就是靠next key 锁。next key锁本质上是记录锁和Gap锁的集合。他在锁定记录的同时,还锁定了该记录之前的空隙。

9. 意向锁

意向锁(Intention Lock)这玩意种类也是特别多。前面不是说到了共享、排他表级锁嘛,如果一个事务要给表加上锁,首先是不是要排除表没有被别的事务加锁,然后排除表中的记录没有被其他事务加锁。看样子这是要准备遍历的节奏呀!一条两条或者成百上千条都没有啥问题,但是这样的数据库的效率能行么?MySQL的大神开发者提出了一个意向锁的概念,这个锁能也有共享和独占的概念:

  1. 意向共享锁(Intention Shared Lock):简称IS锁,当事务准备在记录上加S锁时,先在表上加一个IS锁。
  2. 意向排它锁 ( Intention eXclusive Lock):简称IX锁,当事务准备在记录上加X锁时,先在表上加一个IX锁。 前文提到X锁和S锁之间是互斥的,X锁和X锁之间也是互斥的,S锁和S锁之间是兼容的。这里的IS锁和IX锁和前文提到的X锁和S锁不太一样,IS锁和IX锁是表级锁,但是他们的提出只是为了解决快速判断表中有没有记录被加锁,仅此而已。所以他们之间是相互兼容的,没有任何不兼容。
兼容性表级X表级SIXIS
表级X不兼容不兼容不兼容不兼容
表级S不兼容兼容不兼容兼容
IX不兼容不兼容兼容兼容
IS不兼容不兼容兼容兼容
  1. 插入意向锁

    一个事务在插入一条记录时,需要判断插入位置是否已被被的事务加了Gap锁。如果有Gap锁,插入操作需要等待,直到拥有Gap锁的那个事务提交为止。为了表示该事务想在间隙中插入新记录,但处于等待的状态,MySQL官方把这种锁命名为插入意向锁。

    这里再说一个问题,假设事务T1插入了一条记录,但是未提交事务。这个时候其他事务,使用SELECT …… LOCK IN SHARE MODE来获取这条记录的S锁,或者使用SELECT …… FOR UPDATE获取这条记录的X锁,这个时候MySQL是怎么处理的?

    这个情况想都不要想,其他事务肯定是不能获取到这条记录的任何形式的锁,为什么这么说?

  • 假设其他事务T2能够获取这条记录的S锁,那不就是一个事务读取了另一个事务未提交的数据,这种情况不就是脏读。
  • 假设其他事务T2能够获取这条记录的X锁进行修改,那不就是一个事务修改了另一个未提交事务修改的数据,这不就是发生了脏写。

这又是脏读又是脏写的,这个是可用事务不可容忍的。所以MySQL开发者为了解决这个问题做了以下的动作:让事务T1给记录加一个X锁,则事务T2为获得对应的锁进行等待。事情又完美解决了,不会发生脏读或者幻读了。

这个我可以给大家证明一下,证明之前先说几个MySQL自带的表。

  • INFORMATION_SCHEMA.INNODB_TRX: 该表存放着Innodb存储引擎当前正在执行的事务信息。
  • INFORMATION_SCHEMA.INNODB_LOCKS:该表存放锁的信息,具体存放哪些锁的信息呢
  1. 如果一个事务想要获取某个锁但是没有获取到则记录该锁的信息。
  2. 如果一个事务获取到了某个锁,但是这个锁阻塞了其他的事务,则记录改锁信息。

通过这两个表的数据,就能证明上面说**让事务T1给记录加一个X锁,则事务T2为获得对应的锁进行等待。**到底是不是这样的。

表结构如下:

CREATE TABLE `single_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int(11) DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8;

  1. 查询id=10005
mysql> SELECT * from single_table where id =10005;
Empty set (0.00 sec)

执行上述SQL,发现这个数据没有。 2. 我们执行下面的SQL直接插入数据。

begin;
INSERT INTO `xiaohaizi`.`single_table` (`id`, `key1`, `key2`, `key3`, `key_part1`, `key_part2`, `key_part3`, `common_field`) VALUES ( '10005','51', '32', 'f4qub4nmo7bco0d', '12iv6210y3uns15nt1y032xwa', 'ijmp1d0iof7nnr5aucaug3tsi', '5', 'uwodyzdbkl22zc36cwmjkbh1yoyxalbc');
  1. 在执行SQL查询锁信息:
mysql> SELECT trx.trx_id,trx.trx_state,trx.trx_query,trx.trx_isolation_level,lock_mode,lock_index,lock_data
    -> FROM INFORMATION_SCHEMA.INNODB_TRX trx
    -> JOIN INFORMATION_SCHEMA.INNODB_LOCKS locks ON trx.trx_id = locks.lock_trx_id;
Empty set, 1 warning (0.00 sec)

mysql>
  1. 重新开启一个终端,在终端里开启事务,获取id=10005这个记录的S锁。
begin;
SELECT * from single_table where id =10005 lock in share mode;

这个时候这个SQL没有执行,一直阻塞在那。 阻塞的事务

  1. 查看锁信息
SELECT trx.trx_id,trx.trx_state,trx.trx_query,trx.trx_isolation_level,lock_mode,lock_index,lock_data
FROM INFORMATION_SCHEMA.INNODB_TRX trx 
JOIN INFORMATION_SCHEMA.INNODB_LOCKS locks ON trx.trx_id = locks.lock_trx_id ;

加S锁时具体的锁信息

加X锁时具体的锁信息

加S或者X锁不存在时的加锁信息

那个事务trx_id 为283509355956016、31253的就是我们加S锁和X锁的SQL,但是那个insert的语句一直获得都是那个记录的X锁。加锁的事务等待超时退出时,具体的锁信息就没有了。

总结

文章到这里基本上把开发中可能遇到的锁都介绍了一遍,由于MySQL中的锁种类丰富,不同情况加什么锁也有很多的规则,但是复杂的生产总会出现各种奇葩的死锁问题,如果不能深入了解各种锁的加锁原理,想排查解决这些死锁问题,无疑是难上加难!所以后面的文章会介绍一些特殊的SQL到底会加什么锁!