大话MySQL锁

一、锁介绍

不同存储引擎支持的锁是不同的，比如MyISAM只有表锁，而InnoDB既支持表锁又支持行锁。

下图展示了InnoDB不同锁类型之间的关系：

图中的概念比较多不好理解，下面依次进行说明。

1.1乐观锁

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所在在数据进行提交更新时，才会对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误信息，让用户决定如何处理，其核心是基于CAS算法。乐观锁适用于读多写少的场景，可以提高程序吞吐量。

Mysql自带的是没有乐观锁的，但是可以通过表上加个version字段来实现自己乐观锁。

假如要更新一个用户的年龄，可以这样做：

查出用户id等于3的用户信息，select id,name,age,version from user where id = 3,得到如下的数据。

id	Name	Age	Version
3	张三	26	1

更新张三的年龄为27，注意where条件带上版本号。update user set age = 27,version = 2 where id = 3 and version = 1;
如果更新的结果是1则表示更新成功了，如果是0则表示更新失败需要重新尝试。

1.2悲观锁

悲伤锁就是在每次操作数据时，都悲观地认为会出现数据冲突，所以必须先获取到数据的锁再对其修改。传统的关系型数据库用的就是悲观锁，还有JDK中的synchronized关键字等。悲观锁主要分为共享锁和排他锁。

1.3共享锁

共享锁【shared locks】，又叫读锁，顾名思义，共享锁就是多个事务对同一个数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

如何获取共享锁？

select * from user where id = 3 lock in share mode;

注意：在有事务获取到了共享锁之后，其他事务是不能做insert/update/delete操作的，因为insert/update/delete语句会自动加上排他锁。

1.4排他锁

排他锁【exclusive locks】，又叫写锁，顾名思义，排他锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据进行读取和修改。

如何获取排他锁？

在sql语句后加上for update即可。

select * from user where id = 3 for update

1.5表锁

表锁，顾名思义就是对整张表加锁，是Mysql各存储引擎中最大粒度的锁定机制。

优点：实现逻辑简单，获取锁和释放锁的速度很快，由于每次都是将整张表锁定所以可以很好的避免死锁问题。

缺点：锁定颗粒度大导致出现锁定资源争用的概率高，并发度低。

1.6行锁

行锁，顾名思义就是对表中的某行数据加锁，锁定颗粒度最小。

优点：发生锁冲突的概率低，并发处理能力强。

缺点：由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

如何判断使用的是行锁还是表锁？

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，所以只有在通过索引条件检索数据时才会用行锁，否则使用表锁。并且该索引不能失效，否则都会从行锁升级为表锁。所以在使用select for update时，where 子句一定要带上索引，否则极容易造成性能问题。

行锁又细分三种实现算法：

record lock：专门对索引项加锁；
gap lock：间隙锁，是对索引之间的间隙加锁；
Next-key lock：是前面两种的组合，对索引及其之间的间隙加锁；

1.7页面锁

页面锁出现比较少，它的特点是开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发度一般。

二、死锁

2.1死锁原理

死锁（Deadlock）所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

死锁的四个必要条件：

互斥条件：一个资源每次只能被一个进程使用。
占有且等待：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
不可强行占有:进程已获得的资源，在末使用完之前，不能强行剥夺。
循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

2.2死锁案例

案例一

首先创建一张订单记录表，用于做订单的幂等性校验防止重复生成订单。

CREATE TABLE `order_record`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` int(11) DEFAULT NULL,
  `status` int(4) DEFAULT NULL,
  `create_date` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_order_status`(`order_no`,`status`) USING BTREE
) ENGINE = InnoDB

事务A	事务B
关闭自动提交事务，set autocommit = 0;	set autocommit = 0;
select id from order_record where order_no = 4 for update;//检查是否存在订单号为4的订单
	select id from order_record where order_no = 5 for update;//检查是否存在订单号为5的订单
//如果没有则插入信息 insert into order_record(order_no,status,create_date) values(4,1,'2020-10-04 10:56:00'); 此时锁等待中...
	//如果没有则插入信息 insert into order_record(order_no,status,create_date) values(5,1,'2020-10-04 10:56:00');
	返回结果表明发生死锁，ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
COMMIT;(未完成)	COMMIT;(未完成)

分析：

由于order_no列为非唯一索引，而且此时是RR事务隔离级别，所以SELECT 的加锁类型是gap lock,而且gap范围是(4,+∞)。

当我们执行插入 SQL 时，会在插入间隙上再次获取插入意向锁。插入意向锁其实也是一种 gap 锁，它与 gap lock 是冲突的，事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁。

案例二

InnoDB 存储引擎的主键索引为聚簇索引，其它索引为辅助索引。如果两个更新事务使用了不同的辅助索引，或者一个使用辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待，造成死锁。

步骤：

首先，order_record表存在以下数据。

mysql锁

然后打开两个窗口

事务A	事务B
BEGIN;	BEGIN;
update order_record set status = 1 where order_no = 4;
	mysql> update order_record set status = 1 where id = 4; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction//发生了死锁

分析：

事务A	事务B
首先获取idx_order_status辅助索引
	获取主键索引的行锁
根据辅助索引获取主键索引，再获取主键索引的行锁
	更新status列时，需要idx_order_status辅助索引

所以再更新数据时，要尽量根据主键来更新，可以有效避免死锁发生。

二、如何避免死锁

通常有以下手段可以预防死锁的发生：

在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁。
在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导致的死锁问题。
更新表时，尽量使用主键更新。
避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
设置锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。

如果真的发生了数据库死锁，也有以下方式处理：

查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

杀死进程 kill pid

而且MySQL默认开启了死锁检测机制，当检测到死锁后会选择一个最小（锁定资源最少的）的事务进行回滚。

三、总结

平常很少写MySQL相关的文章，其实MySQL中的门道还是挺多的，本文关于间隙锁等概念讲的比较简单，推荐博客《mysql间隙锁》。
以后可能会再写一篇关于索引的，也有可能不会（主要是懒😄），如果本文哪里有错误，请多指教。

posted @ 2020-10-04 21:44 烟味i 阅读(159) 评论(0) 编辑收藏举报

刷新页面返回顶部

十年磨一剑