• MySQL 锁
  • 锁的类型
  • 锁的粒度
  • 死锁
  • 乐观锁与悲观锁
    • 乐观锁
    • 悲观锁
  • 锁升级
• MySQL 并发更新数据的加锁处理
  • SELECT 显式加锁
  • 使用乐观锁

MySQL 锁

锁的类型

• InnoDB实现了两种标准的行级锁：
  1. 共享锁（S Lock）
     • 语法：SELECT * FROM table LOCK IN SHARE MODE。
  2. 排他锁（X Lock）
     • 语法：SELECT * FROM table FOR UPDATE。
• 如果一个事务T1已经获取了行r的共享锁，另一个事务T2可以立即获得行r的共享锁。因为读取不会改变行的数据，所以多个事务可以同时获取共享锁，这称为锁兼容。
• 但是，如果有其他事务T3想获得行r的排他锁，则必须等待事务T1和T2释放行r上的共享锁，这称为锁不兼容。

	X	S
X	不兼容	不兼容
S	不兼容	兼容

• 普通的 SELECT 语句默认不加锁，而插入（INSERT）、更新（UPDATE）、删除（DELETE）操作默认加排他锁。

锁的粒度

锁级别	说明
表级锁	开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁	开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
页面锁	开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

• 在以下情况下，表锁优先于页级或行级锁：
  • 表的大部分行用于读取。
  • 对严格的关键字进行读取和更新：
    • UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
    • DELETE FROM tbl_name WHERE unique_key_col=key_value;
  • SELECT 结合并行的 INSERT 语句，并且只有很少的 UPDATE 或 DELETE 语句。
  • 在整个表上有许多扫描或 GROUP BY 操作，没有任何写操作。

死锁

MySQL 提供了有效的死锁检测策略，当检测到死锁后，InnoDB 会将持有最少行级排他锁的事务回滚，以打破死锁。

乐观锁与悲观锁

乐观锁

• 通过数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。
  • 为数据增加一个版本标识，通常通过为数据库表增加一个数字类型的 version 字段来实现。
  • 当读取数据时，将 version 字段的值一同读出，每次更新数据时，对 version 值加 1。
  • 当提交更新时，判断数据库表对应记录的当前版本信息与第一次取出的 version 值是否相等，如果相等，则予以更新，否则认为是过期数据。

-- 读取数据和版本号
SELECT id, value, version FROM TABLE WHERE id = <id>;

-- 按特定版本号更新
UPDATE TABLE
SET value = 2, version = version + 1
WHERE id = <id> AND version = <version>;

悲观锁

• 即上面提到的共享锁和排他锁。

锁升级

• 锁升级（Lock Escalation）是指将当前锁的粒度加大，锁粒度：行锁 < 页锁 < 表锁。
• InnoDB 的锁升级机制：
  • 由单独的 SQL 语句在一个对象上持有的锁的数量超过阈值时触发，默认阈值为 5000。如果是不同对象，则不会发生锁升级。
  • 锁资源占用的内存超过了活动内存的 40% 时发生锁升级。
• InnoDB 通过每个事务访问的每个页对锁进行管理，采用位图方式。因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

MySQL 并发更新数据的加锁处理

• MySQL 支持给数据行加锁（InnoDB），并且在 UPDATE/DELETE 等操作时会自动加上排他锁。
• 但并非只要有 UPDATE 关键字就会全程加锁，例如：

UPDATE table1 SET num = num + 1 WHERE id = 1;

• 这条语句实际上相当于两条 SQL 语句（伪代码）：

a = SELECT * FROM table1 WHERE id = 1;
UPDATE table1 SET num = a.num + 1 WHERE id = 1;

• 其中执行 SELECT 语句时没有加锁，只有在执行 UPDATE 时才加锁，这会导致并发操作时的数据更新不一致。
• 解决方法有两种：
  • 通过事务显式对 SELECT 加锁。
  • 使用乐观锁机制。

SELECT 显式加锁

• 对 SELECT 进行加锁的方式有两种：

SELECT ... LOCK IN SHARE MODE;  -- 共享锁，其它事务可读，不可更新
SELECT ... FOR UPDATE;          -- 排它锁，其它事务不可读写

• 对于上述场景，必须使用排它锁。
• 上述两种语句只有在事务中才能生效，否则不会生效。在 MySQL 命令行中使用事务的方式如下：

SET AUTOCOMMIT = 0;
BEGIN WORK;
    a = SELECT num FROM table1 WHERE id = 2 FOR UPDATE;
    UPDATE table1 SET num = a.num + 1 WHERE id = 2;
COMMIT WORK;

• 这样在更新数据时使用事务操作，可以在并发情况下通过锁将并发改为顺序执行。

使用乐观锁

• 乐观锁是锁实现的一种机制，它假定所有需要修改的数据都不会冲突。
• 在更新之前不加锁，而是查询数据行的版本号（版本号是自定义字段，需在业务表上增加，每次更新时自增或更新）。
• 在具体更新数据时，更新条件中添加版本号信息。当版本号未变化时，说明数据行未被更新过，满足更新条件，因此更新成功。
• 当版本号变化时，条件不满足，需要重新查询数据行，再次使用新的版本号进行更新。

原则上，两种方式都可支持，具体使用哪一种取决于实际业务场景，对哪种支持更好，并且对性能影响最小。