MySQL 事务隔离级别与锁机制

发表于 2023-03-15 更新于 2024-07-22 分类于 Database ， MySQL 本文字数： 972 阅读时长 ≈ 3 分钟

事务

ACID

原子性
一致性
隔离性
持久性

并发问题

更新丢失或者脏写，最后更新覆盖了其他事务的更新
脏读，事务 A 读取到事务 B 已修改但未提交的数据，不符合一致性
不可重读，事务 A 内部查询语句在不同时刻读出结果不一致，不符合隔离性
幻读，事务 A 读取到事务 B 提交的新增数据，不符合隔离性

1 2	show variables like 'tx_isolation' -- 默认是不可重复读

锁详解

锁分类

性能上为乐观锁和悲观锁
数据库操作类型，读锁（共享锁）和写锁（排它锁）（都属于悲观锁）
颗粒度，表锁和行锁

加锁操作

表锁

一般用于整张表数据迁移，开销小，加锁快，粒度大，锁冲突高，并行度低

-- 手动加表锁
lock
table 表名 read(write), 表 2 read(write);
-- 查看表上的锁
show
open tables;
-- 删除表锁
unlock
tables
；

行锁

开销大，加锁慢，会出现死锁，粒度小，锁冲突低，并发度高

总结

MyISAM 查询时直接给表加读锁，执行增删改时自动给表加写锁
InnoDB 查询时有 mvcc 机制不会加锁。但增删改会加行锁
读锁只会阻塞写不阻塞读，写锁都阻塞

案例分析

库存更新要用实时的值，即 number = number - 50，不要用 java 代码里的值，如果有其他事务操作，会导致数据有误

每一个事务的第一个 select 就会生成一个当前事务的快照

读未提交

事务 B 修改数据后如未提交，事务 A 还是会拿到了 B 修改后数据，如果 B 不提交，则 A 读取的数据有误

读已提交 - 可解决脏读

即事务 B 修改数据后如果未提交，A 拿不到 B 修改后的数据。

但是事务 A 会查询到事务 B 的提交数据，每次 select 结果可能会不一样

可重复读 - 可解决脏读不可重复读

A 查不到 B 的提交数据，但是可以修删 B 提交过的数据

串行化

把所有查询出来的数据加锁，保证每个事务依次执行

相关机制

间隙锁

即记录和记录之间的间隙会加上锁

-- 如下
update account
set name = 'zhuge'
where id > 8
  and id < 18;
-- 此时其他 session 不能在 (3,10),(10,20), 以及 20(行锁) 之间插入修改任何数据，此时 20 也包含其中
-- 间隙锁只会在可重复读情况下使用

临键锁

即行锁和间隙锁的组合，即上所示，(3,20] 为（3，20）和行锁 20 的组合，此时（3,20] 为临键锁，但不会包括三，只拿末尾的行锁

无索引行锁会变成表锁

因为锁是加到索引上的，所以对非索引字段修改，可能会导致表锁

行锁分析

1 2	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: 启动到现在总共等待次数

查看 INFORMATION_SCHEMA 系统库锁相关数据库

-- 查看事务
select *
from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁
select *
from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待
select *
from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 释放锁，trx_mysql_thread_id 可以从 INNODB_TRX 表中查看
kill
trx_mysql_thread_id
-- 查看近期死锁日志
show engine innodb status
\G

锁优化

尽可能所有数据检索通过索引，避免索引行锁升级为表锁
合理设计索引，缩小锁范围
尽可能减少索引条件范围，避免间隙锁
控制事务大小，减少锁定资源量和时间长度，事务加锁 sql 放事务最后执行
尽量低级别事务隔离

事务