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)前,会自动给涉及的表加读锁,在执行更新操作(UPDATEDELETEINSERT 等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预。
手动加锁: 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