一、MySQL中的锁及解决的问题
1、MySQL中的锁
2、锁的作用
-- 解决事务并发问题;
-- 和MVCC的区别:
| MVCC | 锁 |
实现方式 | 快照 -- 读视图 read view | |
特点 | 并发性好; 解决:脏读、不可重复读、幻读 | |
总结 | 读取一致性; 在普通查询时生效; |
3、方案一:读-MVCC;写-锁
4、方案二:读、写都用锁
二、锁定读(LockingReads)与 LBCC
1、定义:
-- 出现在MVCC中;
-- 读取当前版本的数据,读取数据时加锁;
-- 阻塞其他事务的时候,同时做数据修改(相同记录),避免安全问题;
2、共享锁和独占锁
共享锁 | 独占锁 |
Shared Locks -- S锁 | Exclusive Locks -- X锁 |
select * from table lock in share mode; | select * from table for update; |
事务1 -> 获取一条记录,获取S锁; 事务2 -> 可以获取上一个S锁; 两个事务获取的同一把锁; | 事务1 -> 获取一条记录,获取X锁; 事务2 -> 获取上一个X锁,阻塞; X锁不兼容 S锁 和 X锁; |
delete from table where XXX; | |
先给目标记录加X锁,再执行delete操作; 定位删除记录的B+树的过程,获取X锁定读; | |
insert 语句,不触发覆盖数据,那就不加锁 | |
update | |
1、被修改的记录 ,修改前后存储地址不会发生变化(在B+树上的位置没动):A(1024 -> 1024); 那就获取 X锁; | |
2、被修改的记录,修改前后存储地址会变化(在B+树上的位置动了):A(1024 -> 100); -- B+树定位到这条记录的位置,获取 X锁(1024); -- 把该记录彻底删除掉; -- 插入一条新记录,获取 X锁(100); | |
3、修改键:先delete 再insert; 按delete 和 insert 的加锁规则来; |
三、锁的粒度
| 行锁 | 表锁 |
粒度 | 小< | 大 |
加锁效率 | 低< | 高 |
冲突概率 | 低< | 高 |
并发性能 | 高> | 低 |
1、问题:
-- 如果想对一张表加X锁,那就需要先确认这个表中没有任何行锁和表锁;如果逐行排查会很慢;
2、引入意向锁:
意向锁 |
解决效率问题; 是一个表级别的锁; 像是一个记录表,在哪行加了啥锁; |
事务想要加 行S锁; 要先在表级别加 -- 意向S锁 -> IS 锁; -- 或者 -- 意向X锁 -> IX锁; |
3、意向锁没有行锁,全部是表锁
组合性 | X | IX | S | IS |
表锁 | √ | √ | √ | √ |
行锁 | √ | | √ | |
四、MySQL中的行锁和表锁
1、定义:
-- 和存储引擎有关;
-- 非innoDB,任何写操作语句,都是表锁;它们的表同一时刻只允许一个会话写; //串行。低效
-- innoDB,有了行锁;
2、行锁和表锁
行锁 | 表锁 |
-- 记录加锁; -- 索引上加锁才是行锁; -- 如果锁不在索引上,那就是个表锁; -- 如果 select 语句没用到索引列,那就是个表锁; -- 在执行计划中,确定了真正使用了索引,才会去使用行锁;//如果数据量太小,可能不走索引; | 1、S锁 lock tables teacher read; |
2、X锁 lock tables teacher write; | |
3、元数据锁 alter、drop table;等DDL语句; 过程 -- 事务 ; 表级别的排他锁、共享锁;但是与1、2不一样; | |
4、IS锁、IX锁 判断作用; 防止遍历全部数据; 无法手动管理; | |
5、AUTO-INC锁 -- 自增锁 表级别锁; 使用自增插入的时候开这个锁; 防止多个事务同时插入导致id一样; ------------------------------------------------------------- 插入语句执行之前,确定了要插入多少条数据。 100条 (初始id = 0) 事务A:100条; 事务B:id从101开始; MySQL自动维护的一个轻量级锁; ------------------------------------------------------------- 有一个参数来控制:innodb_autoinc_lock_mode = 0:全部用表锁; = 1:全部用轻量级锁;(主从复制不安全) = 2:自适应;不能确定用表锁,能确定用轻量级锁; |
五、锁实战 -- 间隙锁
1、间隙锁处理幻读问题:
-- 幻读:MVCC的可重复读,undo日志没生成版本链,没法对不存在的数据加锁,所以会出现幻读问题;
-- innoDB提出间隙锁:Gap Locks;
-- 间隙锁:对索引id = 6的前后上锁;2和6之间 & 6和10之间;
-- 如果这时往间隙锁范围插数据,那么就阻塞;
-- 一句 update 创建两个锁,一个排他锁(锁数据),一个间隙锁(锁数据前后);
六、死锁及实战演示
1、死锁
-- 多个事务之间竞争资源时,一直阻塞的现象;
2、在MySQL中,会自动处理死锁,不会一直阻塞;
-- 会尝试重启一个事务,释放资源;
-- 被重启的事务会rollback,继续执行死锁前的操作;
-- show variables like 'innodb_status_output_locks%' //是否输出死锁情况
-- set global innodb_status_output_locks = ON; //设置输出死锁情况
-- show engine innodb status\G; //查看死锁信息