MySQL 锁

date
Aug 6, 2021
slug
mysql-innodb-basic-concept-lock
status
Published
tags
MySQL
summary
type
Page

锁的种类

MyISAM 和 MEMORY 存储引擎采用的是表级锁，InnoDB 也支持表锁，但一般使用行锁。
  • 表锁
  • 行锁
  • 页锁
 

表锁 Table-Level Locking

MyISAM 会自动加锁：在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预。
手动加锁： LOCK TABLE tableName read；/ LOCK TABLE tableName write；
解锁：UNLOCK TABLE tableName ; / UNLOCK TABLES;
分析表锁：show status like 'table%'：两个指标比较关键：
  • Table_locks_immediate：产生表锁的次数
  • Table_locks_waited：出现表锁争用而发生的等待次数
查看当前表锁： show OPEN TABLES where In_use > 0;
 
表读锁 read lock：
也叫共享锁 shared lock
其他 session：
  • 读当前表 - 可以
  • 写当前表 - 阻塞直到锁被释放
当前 session：
  • 读当前表 - 可以
  • 读其他表 - 报错：Table 'word' was not locked with LOCK TABLES
  • 写当前表 - 报错： Table 'user' was locked with a READ lock and can't be updated，
  • 写其他表 - 报错：Table 'word' was not locked with LOCK TABLES
即：当前 session 只能在当前的锁表上操作，且必须将持有的读锁释放才可以做其他操作。
 
表写锁 write lock：
也叫排它锁 exclusive lock
其他 session：
  • 读写当前表都会阻塞，直到锁被释放
当前 session：
  • 读当前表 - 可以
  • 读其他表 - 报错：Table 'word' was not locked with LOCK TABLES
  • 写当前表 - 可以
  • 写其他表 - 报错：Table 'word' was not locked with LOCK TABLES
即：在当前表的读写都可以，但在锁释放之前不能操作其他表。所以 MyISAM 不适合做主表的引擎，因为加了写锁之后所有操作都会阻塞，并发很低。
 

行锁 Row-Level Locking

MySQL 默认的隔离级别是 RR - Repeatable Read 级别，即操作的数据是事务开始时时间点的状态。
行锁分析：show status like 'innodb_row_lock%';
  1. innodb_row_lock_current_waits //当前正在等待锁定的数量
  1. innodb_row_lock_time //从系统启动到现在锁定总时间长度
  1. innodb_row_lock_time_avg //每次等待所花平均时间
  1. innodb_row_lock_time_max //从系统启动到现在等待最长的一次所花时间
  1. innodb_row_lock_waits //系统启动后到现在总共等待的次数
 
显式上锁：
select * from tableName lock in share mode; -- 读锁
select * from tableName for update; -- 写锁
显式解锁：
  1. 提交事务（commit）
  1. 回滚事务（rollback）
  1. kill 阻塞进程
 
行写锁
session1 和 session2 设置关闭自动提交 set autocommit=0;，session3 不做配置。
session1 执行更新语句：update user set username='aa' where id=1;，则此时：
session1：
  • 可以读到刚才更新内容
session2：
  • 读不到刚才更新的内容
  • 写同一条数据会阻塞等待
session3：
  • 读不到刚才更新的内容
  • 写同一条数据会阻塞等待
session1 commit 提交修改后，此时：
session1：
  • 可以读到刚才的修改
session2：
  • 读不到刚才的修改，因为 session2 自己也关闭了自动提交，需要自己执行 commit 来完成事务。而在事务结束前，看到的都是事务开启时的数据状态。
session3：
  • 可以读到更新的内容，因为它每次都是开启新事务
即基于 RR 隔离级别，事务只能看到自己的那一份，而写请求都会被阻塞。
 
间隙锁 gap lock
当使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，就叫做间隙。
由于间隙锁会锁住不存在的行，这会阻塞其他本来想要插入该行的 session 的执行。
 

行锁退化成表锁

当修改条件无法走上索引，行锁会退化成表锁，在不提交的情况下会阻塞所有其他 session 的修改。
 

建议

  • 尽量更新都走索引，避免行锁退化成表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少检索条件，避免间隙锁
  • 控制事务大小，减少事务时间
  • 尽可能低级别事务隔离
 

参考：

 

© 菜皮 2020 - 2023