mysql之update语句锁分析

目录

1. 基础知识

2. 常见锁

3 加锁分析

---

1. 基础知识

• 数据库的隔离级别：show variables like 'transaction_isolation'，默认为REPEATABLE-READ，在这个隔离级别下，在多个事务并发的时候，会出现幻读的问题。所谓的幻读是指在同一事务下，连续执行两次同样的查询语句，第二次的查询语句可能会返回之前不存在的行；因此InnoDB 存储引擎自己实现了行锁，通过 next-key 锁（记录锁和间隙锁的组合）来锁住记录本身和记录之间的“间隙”，防止其他事务在这个记录之间插入新的记录，从而避免了幻读现象；
• 锁是加在记录上还是索引上：锁是加在索引上的，即使表中没有建立索引，也会建立一个默认的索引用于加锁；
• update...where加锁基本单位：加锁的基本单位是Next-Key Locks；
• 查看数据库中当前的锁：select * from performance_schema.data_locks，结果包含以下字段

2. 常见锁

• Record Locks(记录锁): 只会锁定一条记录。其实是锁定这条记录的索引。
• Gap Locks(间隙锁)：间隙锁是在索引记录之间的间隙上的锁，即锁定一个区间。前开后开区间，不包括记录本身，如果间隙锁如果是使用单列唯一索引值进行更新的话，是会退化成Record Lock。因此间隙锁目的主要包含两个：

1. 1. 防止新的数据插入到间隙中
   2. 防止已经存在的数据被更新到间隙中

• Next-Key Locks：Next-Key Lock 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。也是锁定一个区间，前开后闭区间，且包括记录本身。

3 加锁分析

当某个事务执行 update 语句时，会对记录加独占锁（X 锁），如果此时其他事务对加有独占锁的记录进行修改会被阻塞，且锁并不是执行完 update 语句就会释放，而是在事务结束时释放。在 InnoDB 事务中，对记录加锁的基本单位是 next-key 锁，但是会因为一些条件会降级成间隙锁，或者记录锁，比如在 update 语句的 where 条件使用了唯一索引，那么 next-key 锁会降级成记录锁，也就是只会给一行记录加锁，而如果where 条件没有索引列，则会对所有记录加锁，相当于锁表。

注意：其实where 条件带上索引也不能避免全表记录加锁，关键还是要看sql语句执行过程中，执行器选择的是索引扫描，还是全表扫描。因此，为了避免带了索引列还是走了全表扫描，我们可以分析sql语句，同时使用 force index([index_name]) 告诉优化器使用哪个索引。同时可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。

安全更新模式定义：当 sql_safe_updates 设置为 1 时

update 语句必须满足如下条件之一才能执行成功：

• 使用 where，并且 where 条件中必须有索引列；
• 使用 limit；
• 同时使用 where 和 limit，此时 where 条件中可以没有索引列。

delete 语句必须满足如下条件之一才能执行成功：

• 使用 where，并且 where 条件中必须有索引列；
• 同时使用 where 和 limit，此时 where 条件中可以没有索引列。