MySQL锁

Damoncai 11/2/2022 MySQL

# MySQL 锁

# 1. MySQL锁简介

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

在数据库中，除传统计算资源（CPU、RAM、I\O等）的争抢，数据也是一种供多用户共享的资源。如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。锁冲突也是影响数据库并发访问性能的一个重要因素，因此锁对数据库尤其重要。

# 2. 锁的类型

# 2.1 表锁

种类

读锁（read lock），也叫共享锁（shared lock）针对同一份数据，多个读操作可以同时进行而不会互相影响（select）

写锁（write lock），也叫排他锁（exclusive lock）当前操作没完成之前，会阻塞其它读和写操作（update、insert、delete）

存储引擎默认锁

MyISAM，InnoDB也支持表锁

特点

对整张表加锁
开销小
加锁快
无死锁
锁粒度大，发生锁冲突概率大，并发性低

结论

读锁会阻塞写操作，不会阻塞读操作
写锁会阻塞读和写操作

建议

MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎，因为写锁以后，其它线程不能做任何操作，大量的更新使查询很难得到锁，从而造成永远阻塞。

# 2.2 行锁

种类

读锁（read lock），也叫共享锁（shared lock）允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

写锁（write lock），也叫排他锁（exclusive lock）允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁

意向共享锁（IS）一个事务给一个数据行加共享锁时，必须先获得表的IS锁

意向排它锁（IX）一个事务给一个数据行加排他锁时，必须先获得该表的IX锁

存储引擎默认锁

InnoDB

特点

对一行数据加锁
开销大
加锁慢
会出现死锁
锁粒度小，发生锁冲突概率最低，并发性高

事务并发带来的问题

更新丢失解决：让事务变成串行操作，而不是并发的操作，即对每个事务开始---对读取记录加排他锁
脏读解决：隔离级别为Read uncommitted
不可重读解决：使用Next-Key Lock算法来避免
幻读解决：间隙锁（Gap Lock）

# 3. 如何上锁

# 3.1 表锁

隐式上锁（默认，自动加锁自动释放）

select //上读锁

insert、update、delete //上写锁

显式上锁（手动）

lock table tableName read;//读锁
lock table tableName write;//写锁

1
2

解锁（手动）

unlock tables;//所有锁表

表读锁测试

session01	session02
lock table teacher read;// 上读锁
select * from teacher; // 可以正常读取	select * from teacher;// 可以正常读取
update teacher set name = 3 where id =2;// 报错因被上读锁不能写操作	update teacher set name = 3 where id =2;// 被阻塞
unlock tables;// 解锁
	update teacher set name = 3 where id =2;// 更新操作成功

表写锁测试

session01	session02
lock table teacher write;// 上写锁
select * from teacher; // 可以正常读取	select * from teacher;// 被阻塞
update teacher set name = 3 where id =2;// 可以正常更新操作	update teacher set name = 4 where id =2;// 被阻塞
unlock tables;// 解锁
	select * from teacher;// 读取成功
	update teacher set name = 4 where id =2;// 更新操作成功

# 3.2 行锁

隐式上锁（默认，自动加锁自动释放）

select //不会上锁

insert、update、delete //上写锁

显式上锁（手动）

select * from tableName lock in share mode;//读锁
select * from tableName for update;//写锁

1
2

解锁（手动）

提交事务（commit）
回滚事务（rollback）
kill 阻塞进程

行读锁

session01	session02
begin;
select * from teacher where id = 2 lock in share mode;// 上读锁
	select * from teacher where id = 2;// 可以正常读取
update teacher set name = 3 where id =2;// 可以更新操作	update teacher set name = 5 where id =2;// 被阻塞
commit;
	update teacher set name = 5 where id =2;// 更新操作成功

行写锁

session01	session02
begin;
select * from teacher where id = 2 for update;// 上写锁
	select * from teacher where id = 2;// 可以正常读取
update teacher set name = 3 where id =2;// 可以更新操作	update teacher set name = 5 where id =2;// 被阻塞
commit;
	update teacher set name = 5 where id =2;// 更新操作成功

为什么上了写锁，别的事务还可以读操作？因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。

# 4. 行锁的实现算法

# 4.1 Record Lock 锁

单个行记录上的锁 Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定

# 4.2 Gap Lock 锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引加锁，对于键值在条件范围内但并不存在的记录。

优点：解决了事务并发的幻读问题不足：因为query执行过程中通过范围查找的话，他会锁定争个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成锁定的时候无法插入锁定键值范围内任何数据。在某些场景下这可能会对性能造成很大的危害。

# 4.3 Next-key Lock 锁

同时锁住数据+间隙锁在Repeatable Read隔离级别下，Next-key Lock 算法是默认的行记录锁定算法。

# 4.4 行锁的注意点

只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁(索引失效，行锁变表锁)
即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突
如果数据表建有多个索引时，可以通过不同的索引锁定不同的行

# 5. 如何排查锁

# 5.1 表锁

查看表锁情况

show open tables;

表锁分析

show status like 'table%';

1. table_locks_waited
出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值加1），此值高说明存在着较严重的表级锁争用情况
2. table_locks_immediate
产生表级锁定次数，不是可以立即获取锁的查询次数，每立即获取锁加1

1
2
3
4

# 5.2 行锁

行锁分析

show status like 'innodb_row_lock%';

innodb_row_lock_current_waits //当前正在等待锁定的数量
innodb_row_lock_time //从系统启动到现在锁定总时间长度
innodb_row_lock_time_avg //每次等待所花平均时间
innodb_row_lock_time_max //从系统启动到现在等待最长的一次所花时间
innodb_row_lock_waits //系统启动后到现在总共等待的次数

information_schema 库

innodb_lock_waits表
innodb_locks表
innodb_trx表

优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离

# 6. 死锁

# 6.1 解释

指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象

# 6.2 产生的条件

互斥条件：一个资源每次只能被一个进程使用
请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺
循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系

# 6.1 解决

查看死锁：show engine innodb status \G
自动检测机制，超时自动回滚代价较小的事务（innodb_lock_wait_timeout 默认50s）
人为解决，kill阻塞进程（show processlist）
wait for graph 等待图（主动检测）

# 6.2 如何避免

加锁顺序一致，尽可能一次性锁定所需的数据行
尽量基于primary（主键）或unique key更新数据
单次操作数据量不宜过多，涉及表尽量少
减少表上索引，减少锁定资源
尽量使用较低的隔离级别
尽量使用相同条件访问数据，这样可以避免间隙锁对并发的插入影响
精心设计索引，尽量使用索引访问数据
借助相关工具：pt-deadlock-logger

# 6.3 悲观锁和乐观锁

悲观锁

当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。

这种借助数据库锁机制在修改数据之前先锁定，再修改的方式被称之为悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）。

之所以叫做悲观锁，是因为这是一种对数据的修改抱有悲观态度的并发控制方式。我们一般认为数据被并发修改的概率比较大，所以需要在修改之前先加锁。

悲观并发控制实际上是**“先取锁再访问”的保守策略**，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；

另外，还会降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

乐观锁

乐观锁（ Optimistic Locking ）是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。

悲观锁实现方式

悲观锁的实现，往往依靠数据库提供的锁机制。在数据库中，悲观锁的流程如下：

在对记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。
如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
其间如果有其他事务对该记录做加锁的操作，都要等待当前事务解锁或直接抛出异常。

MySql Innodb引擎举例，来说明一下在SQL中如何使用悲观锁

注意：要使用悲观锁，我们必须关闭mysql数据库中自动提交的属性，命令set autocommit=0;即可关闭，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

//0.开始事务
begin; 
//1.查询出商品库存信息
select quantity from items where id=1 for update;
//2.修改商品库存为2
update items set quantity=2 where id = 1;
//3.提交事务
commit;

1
2
3
4
5
6
7
8

以上，在对id = 1的记录修改前，先通过for update的方式进行加锁，然后再进行修改。这就是比较典型的悲观锁策略。

如果以上修改库存的代码发生并发，同一时间只有一个线程可以开启事务并获得id=1的锁，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

上面我们提到，使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

乐观锁实现方式

使用乐观锁就不需要借助数据库的锁机制了。

乐观锁的概念中其实已经阐述了他的具体实现细节：主要就是两个步骤：冲突检测和数据更新。其实现方式有一种比较典型的就是Compare and Swap(CAS)技术。

CAS是项乐观锁技术，当多个线程尝试使用CAS同时更新同一个变量时，只有其中一个线程能更新变量的值，而其它线程都失败，失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次尝试。

比如前面的扣减库存问题，通过乐观锁可以实现如下：

//查询出商品库存信息，quantity = 3
select quantity from items where id=1
//修改商品库存为2
update items set quantity=2 where id=1 and quantity = 3;

1
2
3
4

以上，我们在更新之前，先查询一下库存表中当前库存数（quantity），然后在做update的时候，以库存数作为一个修改条件。当我们提交更新的时候，判断数据库表对应记录的当前库存数与第一次取出来的库存数进行比对，如果数据库表当前库存数与第一次取出来的库存数相等，则予以更新，否则认为是过期数据。

但是以上更新语句存在一个比较重要的问题，即ABA问题。

比如说一个线程1从数据库中取出库存数3，这时候另一个线程2也从数据库中库存数3，并且线程2进行了一些操作将库存数变成了2，紧接着又将库存数变成3，这时候线程1进行CAS操作发现数据库中仍然是3，然后线程1操作成功。尽管线程1的CAS操作成功，但是不代表这个过程就是没有问题的。

有一个比较好的办法可以解决ABA问题，那就是通过一个单独的可以顺序递增的version字段。改为以下方式即可：

//查询出商品信息，version = 1
select version from items where id=1
//修改商品库存为2
update items set quantity=2,version = 3 where id=1 and version = 2;

1
2
3
4

乐观锁每次在执行数据的修改操作时，都会带上一个版本号，一旦版本号和数据的版本号一致就可以执行修改操作并对版本号执行+1操作，否则就执行失败。因为每次操作的版本号都会随之增加，所以不会出现ABA问题，因为版本号只会增加不会减少。

除了version以外，还可以使用时间戳，因为时间戳天然具有顺序递增性。

以上SQL其实还是有一定的问题的，就是一旦高并发的时候，就只有一个线程可以修改成功，那么就会存在大量的失败。

对于像淘宝这样的电商网站，高并发是常有的事，总让用户感知到失败显然是不合理的。所以，还是要想办法减小乐观锁的粒度的。

有一条比较好的建议，可以减小乐观锁力度，最大程度的提升吞吐率，提高并发能力！如下：

//修改商品库存
update item 
set quantity=quantity - 1 
where id = 1 and quantity - 1 > 0

1
2
3
4

以上SQL语句中，如果用户下单数为1，则通过quantity - 1 > 0的方式进行乐观锁控制。

以上update语句，在执行过程中，会在一次原子操作中自己查询一遍quantity的值，并将其扣减掉1。

高并发环境下锁粒度把控是一门重要的学问，选择一个好的锁，在保证数据安全的情况下，可以大大提升吞吐率，进而提升性能。

MySQL事务 MySQL体系架构

西门小狼狗

Choose mode

MySQL锁

MySQL锁

# MySQL 锁

# 1. MySQL锁简介

# 2. 锁的类型

# 2.1 表锁

# 2.2 行锁

# 3. 如何上锁

# 3.1 表锁

# 3.2 行锁

# 4. 行锁的实现算法

# 4.1 Record Lock 锁

# 4.2 Gap Lock 锁

# 4.3 Next-key Lock 锁

# 4.4 行锁的注意点

# 5. 如何排查锁

# 5.1 表锁

# 5.2 行锁

# 6. 死锁

# 6.1 解释

# 6.2 产生的条件

# 6.1 解决

# 6.2 如何避免

# 6.3 悲观锁和乐观锁