本文主要参考于B站视频,记录了事务和锁机制,解释了并发事务带来的问题(脏读、幻读、不可重复读),以及面对这些问题,MySQL InnoDB又是如何处理的。
文章目录
一、事务的定义
事务是数据库管理系统(DBMS)执行过程中的一个逻辑单位,由一个有限的数据库操作序列构成。
二、事务四大特性(ACID)
- 原子性(Atomicity)
- 一致性(Consistency)
- 隔离性(Isolation)
- 持久性(Durability)
三、并发事务问题
1、脏读(Dirty Read)
简单讲,就是事务A查询到了事务B修改后未提交的数据,若事务B回滚了,则事务A之前读到的数据就变成了脏数据。
2、幻读(Phontom)
简单讲,就是事务A对表中某个条件下数据进行修改,读取到10条数据,在修改前,事务B也对这个表中数据进行了修改,如插入或删除了某条数据,导致事务A再次用相同条件查询时,得到的数据不是10条,像是发生了幻觉一样。
3、不可重复读(Non-repeatable Read)
简单讲,就是事务A查询到了事务B修改提交后的数据,导致与事务A前后查询结果不一致的情况。
并发事务的三大问题其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。
四、事务的四种隔离标准(SQL92 ANSI/ISO标准)
1、未提交读(Read Uncommitted)
最低级的事务级别,可以读取到其他事务未提交的数据。未解决任何并发事务问题。
2、已提交读(Read Committed)
只能读取已提交事务数据,不能读取未提交事务数据,解决了脏读
3、可重复度(Repeatable Read)
解决脏读、不可重复读,是mysql默认的事务隔离级别。
4、串行化(Serializable)
所有事务串行化执行,解决事务并发的所有问题(直接不存在并发了)
五、mysql IndoDB对事务隔离级别的支持程度
mysql InnoDB对事务的隔离级别默认是可重复读。
mysql> show global variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.04 sec)
mysql InnoDB完全解决了幻读,但它是如何解决的?请接着往后看。
mysql手动开启事务
START TRANSACTION; -- 开启事务
INSERT INTO `t1` (t, t1) VALUES('124', NOW()); -- do something
ROLLBACK; -- 事务回滚
COMMIT; -- 事务提交
六、事务隔离级别的解决方案
1、LBCC—基于锁的并发控制
在读取数据前,对其加锁,阻止其他事务对数据进行修改:Lock Based Concurrency Control
会降低并发量
2、MVCC—多版本并发控制
生成一个数据请求时间点的一致性数据快照,并用这个快照来提供一定级别(语句级或事务级)的一致性读取:Multi Version Concurrency Control
MVCC部分请参考博主SnailMann的这篇博客
七、锁分类
锁的作用:解决资源的竞争问题。
1、行锁—共享锁(Shared Locks)
S锁,又称为读锁,即多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能改(其他事务无法对加了共享锁的数据进行修改)。
加锁方式:select * from user where id=1 LOCK IN SHARE MODE
释放锁:commit
、rollback
2、行锁—排他锁(Exclusive Locks)
X锁,又称为写锁,排他锁不能与其他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的锁(共享锁、排他锁),只有该获取了排他锁的事务才可以对数据进行读取和修改。
自动加锁:delete
、update
、insert
,默认会自动加锁
手动加锁:select * from user where id=1 FOR UPDATE
3、表锁—意向共享锁(IS)与意向排他锁(IX)
加表锁成功的前提:没有任何事务已经锁定了这张表的任意一行数据,加锁时会进行全表扫描检测。
意向锁是由数据引擎自己维护的,用户无法手动操作意向锁。
意向共享锁(Intention Shared Locks,简称IS锁),表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(Intention Exclusive Locks,简称IX锁),表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
思考:为什么需要(表级别的)意向锁?
答案:提高加表锁的效率,因为在加表锁之前,如果没有意向锁,则需要扫描全表的每一行数据判断能不能加表锁成功,而有了意向锁,则只需要看这个表上有没有意向锁,即可知道能不能加锁成功。
八、锁的本质
思考:加锁后锁住的是什么?是整张表吗?还是一行数据吗?又或是对应字段?
答案:如果是行锁,则锁住的是记录对应的索引项,InnoDB行锁就是通过给索引上的索引项加锁来实现的,而如果未走索引或索引失效,则会从行锁升级为表锁。
案例一:
start transaction;
select * from user where id=1 for update;
-- rollback;
如上面的sql语句,如果id字段有索引,则查询会走索引,上面的语句锁住的是id=1的那条数据的索引项;如果id字段没有索引,则上面的语句会锁住整个表。
案例二:
alter user add index idx_nae(name,age,email); -- 加索引
start transaction;
select * from user where age=16 for update;
-- rollback;
如上面的语句,age在组合索引中,但上面的语句不会走索引(最左匹配原则),所以上面的语句最终会锁的是整张表。
案例三:
alter user add index idx_nae(name,age,email); -- 加索引
start transaction;
select * from user where name='Jack' for update;
-- rollback;
假设user表中有id=1,name='Jack’的记录,则上面的语句执行后,最终锁住的是主键索引中id=1的索引项,因为这里的辅助索引在查询时会涉及到回表。
九、死锁
本节参考自Eternal_yys的博客,以下面的图来举例,当处理完事务B的最后一行update,回车会出现下面的报错:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
1、如何处理死锁
(1)直接进入等待,直到超时,InnoDB默认超时时间为50秒
-- 查询超时时间
show variables like 'innodb_lock_wait_timeout';
-- 设置超时时间
set innodb_lock_wait_timeout = 50;
(2)发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以执行。InnoDB默认开启死锁检测。
-- 查询死锁检测开关状态
show variables like 'innodb_deadlock_detect';
-- 开启
set global innodb_deadlock_detect = on;
死锁检测的条件:当前事务需要加锁访问的行被其他事务锁住时,才会进行死锁检测。
十、InnoDB是如何解决幻读的
间隙锁(Gap Lock)
锁定的是一个范围内(区间)的索引项,在这个被锁定的范围内,无法新增数据,也无法对已有数据进行更新或删除操作。
假设有个user表,表字段id为自增主键,现有10条数据,id从1到10。
事务A
start transaction;select * from user where id > 5 for update; -- 会将id范围为6到无穷大的锁住-- rollback;
事务B
start transaction;select * from user where id = 6 for update; -- 会被阻塞,如果不回滚或不关闭会话,则会阻塞直到超时(默认50秒)
select * from user where id = 11 for update; -- 不会阻塞,因为没有id=11的数据
update user set name = 'Jack' where id = 6; -- 会被阻塞
delete from user where id = 6; -- 会被阻塞
insert user(id,name) values(11,'Mary'); -- 会被阻塞
select * from user where id = 6; -- 不会阻塞,可以正常查询-- rollback;
如上,被事务A锁住的数据(6~10),另一个事务无法获取对应的锁,无法对锁住的数据进行更新、删除操作,也无法将数据插入到这个范围内。
需要注意的是,如果将事务A中锁的条件范围修改为 id > 20
,最终生效的还是 id > 10
事务A
start transaction;select * from user where id > 20 for update; -- 由于当前表中id最大为10,所以这个锁锁住的数据范围是11到正无穷
-- rollback;
事务B
start transaction;
select * from user where id = 10 for update; -- 不会阻塞
select * from user where id = 11 for update; -- 不会阻塞,因为没有id=11的数据
insert user(id,name) values(11,'Mary'); -- 会被阻塞,因为事务A的锁从11开始
-- rollback;
临键锁(Next-key Lock)
锁定的也是一个范围,但它的范围与间隙锁略有不同,它锁定的是最后一个索引记录的下一个左开右闭区间。
还是使用上面的user表为例,在上面id从1到10的基础上,新增一条id为20的记录,即总的有11条数据,分别是id从1到10的10条,外加一条id为20的数据。
事务A
start transaction;
select * from user where id > 13 and id < 18 for update; -- 会锁住范围 (10,20],不包含10,但包含20
-- rollback;
事务B
start transaction;select * from user where id = 10 for update; -- 不会阻塞
select * from user where id = 11 for update; -- 不会阻塞,因为没有id=11的数据
insert user(id,name) values(11,'Mary'); -- 会被阻塞,锁的范围 (10,20]
select * from user where id = 20 for update; -- 会被阻塞,左开右闭包含20
-- rollback;