MySQL锁

笔记来源：https://www.bilibili.com/video/BV1zK4y1X7oG?p=1

MySQL锁机制

MySQL支持的锁

锁的粒度：表级锁、行级锁（InnoDB）、页级锁

锁的操作：读锁和写锁

实现方式：乐观锁和悲观锁

使用场景：

修改数据表结构会自动加表级锁 - 元数据锁

更新数据未使用索引，行锁会自动升级表级锁

更新数据使用索引会使用行级锁

select ... for update --会使用行级锁

什么是读写锁？什么是排他锁？

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁使用独占的方式保证在只有一个版本的情况下事务之间相互隔离，所以锁可以理解为单版本控制。

排他锁

引入锁之后就可以支持并行处理事务，如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥锁，先进入的事务独占数据项之后，其他事务被阻塞，等待前面的事务释放锁。

读写锁

读写锁，可以让读和读并行，而读和写，写和读，写和写这几种情况还是要加排他锁。

如果几个事务之间没有共享数据项，完全可以并行处理。

表级锁

每次操作锁住整张表。锁粒度大。发生冲突的概率高，并发度最低。

应用在 MySAM、InnoDB、BDB 等存储引擎中

MySQL的表级锁右两种：表锁、元数据锁（mate data lock ，MDL）

表锁的两种表现形式：

表共享读锁（Table Read Lock）

表独享占写锁（Table Write Lock）

表级锁的使用

表读锁

1.session1: lock table user read; -- 给user表加写锁
2.session1: select * from user;   -- 可以查询
3.session1: select * from dept;     -- 不能访问非锁定表
4.session2: select * from user; -- 可以查询 没有锁
5.session2: update user set name = 'XX' where id = 2; -- 修改阻塞，自动加写锁
6.session1: unlock tables;  -- 释放锁
7.session2: Rows matched:1 Changed: 1 Warnings:0 -- 修改完成
8.session1: select * from dept; -- 可以访问

表写锁

1.session1: lock table user write;  -- 给user表加写锁
2.session1: select * from user;     -- 可以查询
3.session1: select * from dept;         -- 不能访问非锁定表
4.session1: update user set name = 'XX' where id = 2;   -- 可以执行
5.session2: select * from user;     -- 查询阻塞
6.session1: unlock tables;              -- 释放锁
7.session2: 4 Row in set (22.57 sec)    -- 查询执行完成
8.session1: select * from dept;         -- 可以查询

元数据锁

MDL（mateDataLock）元数据：表结构

在MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

元数据读锁：

1.session1: begin; -- 开启事务
            select * from user; -- 加 MDL 读锁
2.session2: alter table user add f int; -- 修改阻塞
3.session1: commit; -- 提交事务 或者 rollback 释放读锁
4.session2: Query OK, 0 rows affected (8.26 sec) -- 修改完成
            Records: 0  Duplicates: 0  Warnings: 0 

行级锁【InnoDB实现】

行级锁：每次操作锁住一行数据。锁定粒度小，发生锁冲突的概率最低，并发度最高。

RecordLock锁：锁定单个记录的锁。RC(Read Commit)、RR(Read REPEATABLE)隔离级别都支持。

GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。RR 隔离级别支持。

Next-key Lock 锁：行锁和间隙锁组合，同时锁住数据，并且锁住数据前面的Gap。RR隔离级别支持。

按照功能分为两种：

1. 共享读锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁【也就是其他事务不能去加写锁】。
2. 排他写锁（X）：允许获得排他锁的事务更新数据，阻止其他事务获取相同数据集的共享读锁（不是读锁）和排他锁。

行读锁【注：使用索引加行锁，未锁定的行可以访问】

show status like 'innodb_row_lock'; -- 查看行锁状态
1.session1: begin 
2.session1: select * from user where id = 1 lock in share mode;
3.session2: update user set name = 'Y' where id = 2; -- 未锁定行可以修改
4.session2: update user set name = 'Y' where id = 1; -- 锁定行，修改阻塞
5.session1: commit; -- 提交事务 或者 rollback 释放锁
6.session2: Query OK, 1 row affected (9.33 sec) -- 修改成功
            Rows matched: 1  Changed: 1  Warnings: 0

行读锁升级为表锁【注：未使用索引行锁升级为表锁】

1.session1: begin; -- 开启事务未提交
            select * from user where name = 'Y' lock in share mode; -- 手动加 name='Y' 的行读锁，未使用索引
2.session2: update user set name = 'Y' where id = 4; -- 修改阻塞， 未用索引行锁升级为表锁
3.session1: commit; -- 提交事务 或者 rollback 释放读锁
4.session2: Query OK, 1 row affected (5.02 sec) -- 修改成功
            Rows matched: 1  Changed: 1  Warnings: 0

行写锁【注：主键索引产生记录锁】

1.session1: begin; -- 开启事务未提交
            select * from user where id = 1 for update; --手动加 id=1 的行写锁
2.session2: select * from user where id = 2;    -- 可以查询
3.session2: select * from user where id = 1;    -- 可以查询
4.session2: select * from user where id = 1 lock in share mode; -- 加读锁被阻塞
5.session1: commit; -- 提交事务 或者 rollback 释放读锁
6.session2: 查询成功

死锁是如何产生的，如何解决？

加锁是实现数据库并发控制的一个非常重要的技术。当两个事务的锁发生冲突，互相等待对方的锁释放，不能继续执行事务逻辑，就会出现死锁，严重影响应用的正常执行。

死锁的现象主要有：表锁死锁，行级锁死锁、共享锁转换为排他锁。

表锁死锁

产生原因：两个事务相互持有对方想要拿到的锁，于是产生死锁。

解决方案：调整程序的逻辑、尽量避免同时锁定两个资源。

行级锁死锁

产生原因1：避免行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。

解决方案：SQL语句中不要使用太复杂的关联多表的查询，优化索引。

产生原因2：两个事务相互持有对方想要拿到的锁，于是产生死锁。

解决方案：在同一事务中，尽可能做到一次锁定所需要的所有资源。按照id对资源排序，然后顺序进行处理，采用MVCC机制处理。

共享锁转换为排他锁

产生原因：事务A本身持有共享锁，事务B持有排他锁，事务A去尝试获取同一数据项的排他锁。导致死锁。

image.png

解决方案：避免引发同时对一条记录多次操作；使用乐观锁进行控制。