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%';
- innodb_row_lock_current_waits //当前正在等待锁定的数量
- innodb_row_lock_time //从系统启动到现在锁定总时间长度
- innodb_row_lock_time_avg //每次等待所花平均时间
- innodb_row_lock_time_max //从系统启动到现在等待最长的一次所花时间
- innodb_row_lock_waits //系统启动后到现在总共等待的次数
显式上锁:
select * from tableName lock in share mode; -- 读锁
select * from tableName for update; -- 写锁
显式解锁:
- 提交事务(commit)
- 回滚事务(rollback)
- 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 的修改。
建议
- 尽量更新都走索引,避免行锁退化成表锁
- 合理设计索引,尽量缩小锁的范围
- 尽可能减少检索条件,避免间隙锁
- 控制事务大小,减少事务时间
- 尽可能低级别事务隔离
参考:
- 一张图彻底搞懂 MySQL 的锁机制:https://learnku.com/articles/39212?order_by=vote_count&