Mysql锁

chenchenchen

发布于 2022-05-07 15:41:38

1.5K0

发布于 2022-05-07 15:41:38

文章被收录于专栏：chenchenchenchenchenchen

全局锁&表锁&行锁&页锁
悲观锁&乐观锁
共享锁&排他锁&意向锁
记录锁&间隙锁&临键锁

全局锁&表锁&行锁&页锁

表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

	行锁	表锁	页锁
MyISAM		√
BDB		√	√
InnoDB	√	√

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞:数据更新语句 (数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。

为什么不使用set global readonly=true的方式呢?

一是，在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。
二是，在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持 readonly状态，这样会导致整个库⻓时间处于不可写状态，⻛险较高。

表级锁

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

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

MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

隐式上锁（默认）：

select（共享锁）
insert、update、delete（排他锁）

显式上锁/解锁（手动）：

lock table table_name read;（共享锁）
lock table table_name write;（排他锁）
unlock table table_name;（解锁单表）
unlock tables;（解锁所有表）

排查：

show open tables;（查看）

分析
mysql> show status like 'table%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Table_locks_immediate      | 100     |
| Table_locks_waited         | 11      |
+----------------------------+---------+
table_locks_waited：因表级锁争用而等待的次数
table_locks_immediate：产生表级锁定的次数

行级锁

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

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

行级锁分为共享锁和排他锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。

意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

注意事项：

在不通过索引条件查询的时候，InnoDB 使用的是表锁，而不是行锁。
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行。另外，无论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的。如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引。这种情况下 InnoDB 将使用表锁，而不是行锁。因此,在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。

锁冲突

窗口A先修改了id为3的用户信息后，还没有提交事务，此时窗口B再更新同一条记录，然后就提示Lock wait timeout exceeded; try restarting transaction ，由于窗口A迟迟没有提交事务，导致锁一直没有释放，就出现了锁冲突，而窗口B一直在等待锁，所以出现了超过锁定超时的警告了。

行锁锁冲突概率低，并发性高，但是会有死锁的情况出现。
表锁的锁冲突几率特别高，但不会出现死锁的情况。

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

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |
+-------------------------------+-------+

InnoDB 的行级锁定状态变量不仅记录了锁定等待次数，还记录了锁定总时长，每次平均时长，以及最大时长，此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下： InnoDB_row_lock_current_waits：当前正在等待锁定的数量； InnoDB_row_lock_time：从系统启动到现在锁定总时间长度； InnoDB_row_lock_time_avg：每次等待所花平均时间； InnoDB_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间； InnoDB_row_lock_waits：系统启动后到现在总共等待的次数；

还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行，并分析锁争用的原因。具体方法如下：

mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

然后就可以用下面的语句来进行查看：

mysql> show engine InnoDB status;

监视器可以通过发出下列语句来停止查看：

mysql> drop table InnoDB_monitor;

设置监视器后，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等，便于进行进一步的分析和问题的确定。默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用“–console”选项来启动服务器以关闭写日志文件。

死锁

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

事务A	事务B
begin;update t set k=k+1 where id=1	begin;
	update t set k=k+1 where id=2
update t set k=k+1 where id=2
	update t set k=k+1 where id=1

事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

在MySQL中，行级锁并不是直接锁记录，而是锁索引。当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

当出现死锁以后，有两种策略:

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect设置为on，表示开启这个逻辑。

产生条件：

互斥
请求与保持
不可剥夺
循环等待

解除死锁：

查看：show engine innodb status;
查看是否锁表（show OPEN TABLES where In_use > 0;），查看进程（show processlist），杀死进程（kill id）。
查看当前锁定的事务（SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;）和等待的事务（SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;），杀死进程（kill id）。

避免死锁：

1、加锁顺序一致。如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、尽可能一次锁定所需数据行。在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、使用较低的隔离级别。对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

4、合理使用索引，减少不必要的索引。

5、保持简短的事务，单次操作数量不宜过多。

页级锁

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

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

悲观锁&乐观锁

悲观锁(Pessimistic Locking)

悲观锁(Pessimistic Locking)，悲观锁是指在数据处理过程，使数据处于锁定状态，一般使用数据库的锁机制实现。

现在互联网高并发的架构中，受到fail-fast思路的影响，悲观锁已经非常少见了。

思考：表锁、行锁、页锁是悲观锁吗？

MySQL默认使用自动提交，使用悲观锁，必须关闭MySQL的自动提交。

set autocommit=0;

select…for update是MySQL提供的实现悲观锁的方式，可以保证当前的数据不会被其它事务修改。

//0.开始事务
begin; 
//1.查询出商品库存信息
select stock from tb where id=1 for update;
//2.扣减商品库存
update tb set stock=stock-1 where id = 1;
//3.提交事务
commit;

注意事项：

不带for update仍能正常查询出数据
必须要开启事务，确保执行完所有操作之后才释放掉锁。否则第一步查询操作后事务就提交释放锁了。
扫描过的行都会被锁上（行锁），确保select…for update走索引，否则全表扫描会将整个表锁住（表锁）。
Oracle还存在了select … for update nowait，即发现被锁后不等待，立刻报错。

乐观锁（Optimistic Lock）

乐观锁认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测。

一般利用数据版本号（version）/时间戳机制实现。

数据库表增加一个数字类型的 “version” 字段，读取数据时同时读取version字段，更新数据时对version值+1。提交更新时，判断数据库version值与第一次读取的version值是否相等，相等予以更新，否则更新失败。

//1.查询出商品库存信息
select (stock,version) from tb where id=1;
//2.扣减商品库存
update tb set stock=stock-1,version=version+1 where id=1 and version=#{version};

采用version号作为乐观锁，则每次只有一个事务能更新成功，业务感知上就是大量操作失败。优化后的sql减小锁力度，从而提升吞吐。

update tb set stock=stock-1 where id = 1 and stock-1>=0

注意事项：

读写分离需要强制读主库，或者把select语句放在事务中，查询的就是主库。
若行数据中存在多字段并发更新的场景，会因为version的失败而导致非常高的失败率。可以针对单字段，设计该字段版本号。若变更太频繁，可以提出来单独维护，做到冷热数据分离。

共享锁&排他锁&意向锁

共享锁（shared locks，S锁）

共享锁又叫读锁（shared locks，S锁），语法如下：

select … lock in share mode;

事务加上S锁后只能读数据，不能修改
其它事务只能再加S锁，不能加其它锁

排他锁（exclusive locks，X锁）

排它锁又叫写锁（exclusive locks，X锁），语法跟悲观锁一样：

select ... for update

获得X锁的事务既能读数据，也能修改数据
其它事务不能加任何类型的锁，直到X锁释放。

意向锁(Intention Locks)

InnoDB中的S锁和X锁是行锁，当事务需要遍历所有行的锁持有情况，首先需要判断表A是否有表级锁，然后再判断表A每一行是否有行级锁，性能较低。

SELECT * FROM A for update; (加X锁)

意向锁(Intention Locks)是表锁，多用在innoDB中，是由数据库自己维护的加锁标识，可以快速判断表中是否有记录被上锁，避免遍历，提高加锁效率。

意向锁的主要作用是提升存储引擎性能，检查行锁前先检查意向锁是否存在，如果存在则阻塞线程。

意向锁分为意向共享锁(IS锁)和意向排它锁(IX锁)

IS锁：表示事务中将要对某些行加S锁
IX锁：表示事务中将要对某些行加X锁

意向锁协议

事务要获取表A某些行的S锁必须要获取表A的IS锁
事务要获取表A某些行的X锁必须要获取表A的IX锁

记录锁&间隙锁&临键锁

记录锁（Record Lock）

记录锁锁的是表中的某一条记录，记录锁的出现条件必须是精准命中索引并且索引是唯一索引，如主键id

间隙锁（Gap Lock）

间隙锁又称之为区间锁，每次锁定都是锁定一个区间，隶属行锁。

当我们查询数据用范围查询或者相等条件查询时，查询条件命中索引，请求共享或排他锁并且没有查询到符合条件的记录，此时就会将查询条件中的范围数据进行锁定(即使是范围库中不存在的数据也会被锁定)。对于键值在条件范围内但并不存在的记录，称为间隙，InnoDB也会为这些间隙加锁，即间隙锁。

比如数据库中只有1，4两条数据，如果查找不存在的3，则会导致（1，4）区间锁定。

在窗口A中我们根据id做一个范围更改操作，不提交事务，然后在范围B中插入一条记录，该记录的id值位于窗口A中的条件范围内。程序报错：Lock wait timeout exceeded; try restarting transaction 。锁定的区间就是 (1,3]这个左开右闭的区间。

间隙锁只会出现在可重复读的事务隔离级别中，mysql5.7默认就是可重复读。

间隙锁的目的是为了防止幻读，防止其他事务在间隙中执行insert语句，或者将已有数据update到间隙中，保障数据的恢复和复制。

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能