01.MySQL中的锁

1.0 锁分类

• 按使用方式划分：乐观锁、悲观锁

• 按锁级别划分：共享锁、排他锁

• 按锁的粒度划分：表级锁、行级锁、页级锁

• 按操作划分：DML锁，DDL锁

• 按加锁方式划分：自动锁、显示锁

1.1 共享锁和排它锁

1、共享锁（读锁）

• 共享锁又叫读锁，一旦加锁成功，其它事务只能对A再加共享锁，不能加其它锁
• 获准共享锁的事务只能读数据，不能写数据
• eg: 对工资大于60000的员工加共享锁读取
  • 共享锁会锁定整个行，不仅仅是salary字段（其他字段也不能修改）
  • 读锁只会对符合条件行加锁（小于60000工资数据的行可以修改）

-- 开始事务
START TRANSACTION;
-- 对工资大于60000的员工加共享锁
SELECT * FROM employees WHERE salary > 60000 FOR SHARE;

-- 执行其他读操作
-- 提交事务
COMMIT;

2、排它锁（写锁）

• 排它锁又叫写锁，一旦加锁成功，其它事务都不能加任何类型的锁
• 获准排它锁的事务既能读数据，又能写数据
• eg: 对工资大于60000的员工加排它锁修改
  • 可重复读或读提交隔离级别下，其他事务仍然可以读取这些行
  • 写锁只会对符合条件行加锁（小于60000工资数据的行可以修改）

-- 开始事务
START TRANSACTION;
-- 对工资大于60000的员工加排它锁
SELECT * FROM employees WHERE salary > 60000 FOR UPDATE;

-- 执行其他写操作，例如修改工资
-- 提交事务
COMMIT;

1.2 乐观锁和悲观锁

• 乐观锁和悲观锁都不是MySQL自带的锁，是开发中根据业务需求自己实现的

1、悲观锁：写少读多

• 悲观锁本质使用的就是MySQL的排它锁（写锁），在读取时就加锁

• MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞

• 申请前提：没有线程对该结果集中的任何行数据使用排他锁或共享锁，否则申请会阻塞。

• 适用场景：悲观锁适合写入频繁的场景。

-- 开始事务
START TRANSACTION;

-- 选择并锁定 salary 大于 60000 的行
SELECT * FROM employees WHERE salary > 60000 FOR UPDATE;

-- 执行其他写操作，例如修改工资
UPDATE employees SET salary = salary + 5000 WHERE salary > 60000;
-- 提交事务
COMMIT;

2、乐观锁：读多写少

• 乐观锁读取和写入都不加锁，只在更新操作是判断版本号是否修改过，版本号一致才执行操作

• MySQL中的乐观锁主要是通过版本控制来实现的，它通常涉及到在表中增加一个版本号
• 如果版本号没有变化，说明没有其他事务修改过这条记录，更新可以继续
• 如果版本号发生变化，说明有其他事务修改过这条记录，更新操作可能需要进行冲突解决

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary INT,
    version INT
);

• 乐观锁实现

-- 客户端1开始事务
START TRANSACTION;
-- 客户端1读取数据version版本比如: version=1 
SELECT * FROM employees WHERE id = 1;

-- 客户端1更新数据，版本号+1
UPDATE employees SET salary = 55000, version = version + 1 WHERE id = 1 AND version = 1;
-- 获取受影响的行数（如果影响行数不等于1代表 有其他事务已经修改了这条数据，需要重新获取版本号然后再次乐观锁执行）
SELECT ROW_COUNT();
-- 客户端1提交事务
COMMIT;
-- ROLLBACK;

1.3 行级锁 & 表级锁

• 行级锁（写多读少）开销大，加锁慢，锁定粒度最小，发生锁冲突概率最低，并发度最高
• 表级锁（适合大量的读操作）开销小，加锁快，锁定粒度大、发生锁冲突最高，并发度最低
• MySQL 常用存储引擎的锁机制
  • MyISAM 和 Memory 采用表级锁（table-level locking）
  • InnoDB 支持行级锁（row-level locking）和表级锁，默认为行级锁。
  • BDB 采用页级锁（page-level locking）或表级锁，默认为页级锁；

1、行级锁

• 行级锁是指对表中操作的行进行锁定，允许多个事务同时对不同行进行读写操作，而不会互相阻塞

• 共享锁和排它锁都是行级锁

  • 共享锁它允许多个事务同时持有锁，但只允许读操作
  • 排它锁只允许一个事务持有锁，且该事务可以进行读写操作

• 行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大

• 特点

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

• 行级别共享锁

-- 开始事务
START TRANSACTION;

-- 对特定行加共享锁
SELECT * FROM your_table_name WHERE your_condition FOR SHARE;
-- 执行其他读操作

-- 提交事务
COMMIT;

2、表级锁

• 表级锁是数据库中一种粗粒度的锁定机制，它锁定的是整个表，而不是表中的特定行

• 表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

  • 在表级别上，如果一个事务获取了共享锁，它可以同时读取整个表的数据

  • 在行级别上，排他锁表示只有一个事务能够持有锁，并且可以进行读写操作

• 特点

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

• eg: 表级别共享锁

-- 开始事务
START TRANSACTION;

-- 对整个表加共享锁
LOCK TABLES your_table_name READ;
-- 执行其他读操作
-- 提交事务
COMMIT;

-- 解锁表
UNLOCK TABLES;

3、页级锁

• InnoDB不支持页级锁，BDB引擎支持页级锁

• 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁

• 页级锁 锁定的是数据库中的页（Page），而一页通常包含多行数据

• 这样的锁定机制允许在一次操作中锁定或释放多行数据，提高了并发性，相较于行级锁，减少了锁定管理的开销

• 特点

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

02.引擎加锁时机

2.1 MyISAM加锁

• MySAM（SELECT）
  • MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁
• MYSAM（UPDATE、DELETE、INSERT）
  • 在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁
• 这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁

2.2 InnoDB加锁

• InnoDB SELECT 加锁时机
  • 支持MVCC机制隔离级别，select 默认不存在锁（不可重复读和可重复读才有MVCC机制）
  • 不支持MVCC机制隔离级别，索引查找，只对索引加共享锁，非索引查找使用表级锁
• InnoDB（UPDATE、DELETE、INSERT）
  • 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)

2.3 行级锁与死锁

• MyISAM 中是不会产生死锁的，因为 MyISAM 总是一次性获得所需的全部锁，要么全部满足，要么全部等待。

• 而在 InnoDB 中，锁是逐步获得的，就造成了死锁的可能。

• 在 MySQL 中，行级锁并不是直接锁记录，而是锁索引。

• 索引分为主键索引和非主键索引两种，如果一条 SQL 语句操作了主键索引，MySQL 就会锁定这条主键索引；

• 如果一条 SQL 语句操作了非主键索引，MySQL 就会先锁定该非主键索引，再锁定相关的主键索引。

• 在进行UPDATE、DELETE操作时，MySQL 不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking.

• 当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引；

• 另一个锁定了非主键索引，在等待主键索引，这样就会发生死锁。

• 发生死锁后，InnoDB 一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

• 避免死锁的方法

  • 1.如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低发生死锁的可能性；
  • 2.在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
  • 3.对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。