MySql教程

MySQL基础7——隔离级别,锁

本文主要是介绍MySQL基础7——隔离级别,锁,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

隔离级别

ISO和ANIS SQL标准制定了四种事务隔离级别的标准,各数据库厂商在正确性和性能之间做了妥

协,并没有严格遵循这些标准;MySQL innodb默认支持的隔离级别是 REPEATABLE READ;

READD UNCOMMITTED

读未提交;该级别下读不加锁,写加排他锁,写锁在事务提交或回滚后释放锁;

READ COMMITTED

读已提交;从该级别后支持 MVCC (多版本并发控制),也就是提供一致性非锁定读;此时读取操作

读取历史快照数据;该隔离级别下读取历史版本的最新数据,所以读取的是已提交的数据;

REPEATABLE READ

可重复读;该级别下也支持 MVCC,此时读取操作读取事务开始时的版本数据;

SERIALIZABLE

可串行化;该级别下给读加了共享锁;所以事务都是串行化的执行;此时隔离级别最严苛;

不同隔离级别下并发异常

隔离级别脏读不可重复读幻读
READ UNCOMMITTED存在存在存在
READ COMMITTED不存在存在存在
REPEATABLE READ不存在不存在存在(加锁解决)
SERIALIZABLE不存在不存在不存在

相关命令

-- 设置隔离级别 
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
-- 或者采用下面的方式设置隔离级别 
SET @@tx_isolation = 'REPEATABLE READ'; 
SET @@global.tx_isolation = 'REPEATABLE READ'; 
-- 查看全局隔离级别 
SELECT @@global.tx_isolation; 
-- 查看当前会话隔离级别 
SELECT @@session.tx_isolation;
SELECT @@tx_isolation; 
-- 手动给读加 S 锁 
SELECT ... LOCK IN SHARE MODE; 
-- 手动给读加 X 锁 
SELECT ... FOR UPDATE; 
-- 查看当前锁信息 
SELECT * FROM information_schema.innodb_locks;

锁机制用于管理对共享资源的并发访问;用来实现事务的隔离级别 ;

锁类型

共享锁和排他锁都是行级锁;MySQL当中事务采用的是粒度锁;针对表(B+树)、页(B+树叶子节点)、行(B+树叶子节点当中某一段记录行)三种粒度加锁;

(意向共享锁和意向排他锁都是表级别的锁)

在这里插入图片描述

共享锁

事务读操作加的锁;对某一行加锁;

在 SERIALIZABLE 隔离级别下,默认帮读操作加共享锁;

在 REPEATABLE READ 隔离级别下,需手动加共享锁,可解决幻读问题;

在 READ COMMITTED 隔离级别下,没必要加共享锁,采用的是 MVCC;

在 READ UNCOMMITTED 隔离级别下,既没有加锁也没有使用 MVCC;

排他锁

事务删除或更新加的锁;对某一行加锁;

在4种隔离级别下,都添加了排他锁,事务提交或事务回滚后释放锁;

意向共享锁

对一张表中某几行加的共享锁;

意向排他锁

对一张表中某几行加的排他锁;

锁兼容性

SXISIX
S兼容冲突兼容冲突
X冲突冲突冲突冲突
IS兼容冲突兼容兼容
IX冲突冲突兼容兼容

由于innodb支持的是行级别的锁,意向锁并不会阻塞除了全表扫描以外的任何请求;

意向锁之间是互相兼容的;

IX 对共享锁和排他锁都不兼容;

IS 只对排他锁不兼容;

当想为某一行添加 S 锁,先自动为所在的页和表添加意向锁 IS,再为该行添加 S 锁;

当想为某一行添加 X 锁,先自动为所在的页和表添加意向锁 IX,再为该行添加 X 锁;

锁算法

Record Lock (对主键索引加锁)

记录所,单个行记录上的锁

Gap Lock

间隙锁,锁定一个范围,但不包含记录本身;全开区间;REPEATABLE READ级别以上支持间隙锁

如果 REPEATABLE READ 修改 innodb_locks_unsafe_for_binlog = 0 ,那么隔离级别相当于

退化为 READ COMMITTED;

-- 查看是否支持间隙锁,默认支持,也就是 innodb_locks_unsafe_for_binlog = 0; 
SELECT @@innodb_locks_unsafe_for_binlog;
Next-Key Lock

记录锁+间隙锁,锁定一个范围,并且锁住记录本身;左开右闭区间;

Insert Intention Lock

插入意向锁,insert操作的时候产生;在多事务同时写入不同数据至同一索引间隙的时候,并不需

要等待其他事务完成,不会发生锁等待。

假设有一个记录索引包含键值4和7,两个不同的事务分别插入5和6,每个事务都会产生一个加在

4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。

锁兼容

GAPInsert IntentionRecordNext-key
GAP兼容兼容兼容兼容
Insert Intention冲突兼容兼容冲突
Record兼容兼容冲突冲突
Next-key兼容兼容冲突冲突

横向:表示已持有,纵向:表示正在请求

AUTO-INC Lock

自增锁,是一种特殊的表级锁,发生在 AUTO_INCREMENT 约束下的插入操作;采用的一种特殊

的表锁机制;完成对自增长值插入的SQL语句后立即释放;在大数据量的插入会影响插入性能,因

为另一个事务中的插入会被阻塞;从MySQL 5.1.22开始提供一种轻量级互斥量的自增长实现机

制,该机制提高了自增长值插入的性能;

锁对象

行级锁是针对表的索引加锁;索引包括聚集索引和辅助索引;

表级锁是针对页或表进行加锁;

考虑 InnoDB 在 read committed 和 repeatable read 级别下锁的情况;

示例1
select * from t where id = 5 for update; lock in share mode 
-- id 为主键 Read committed 隔离级别 
-- 在主键 id = 5 行上加 X 锁 

-- id 是唯一索引 Read committed 隔离级别 
-- 在唯一索引id=5行上加X锁,在主键索引上对应行加X锁 

-- id 是非唯一索引 Read committed 隔离级别 
-- 在非唯一索引上所有id=5行加上X锁,对应的主键索引列加上X锁 

-- id 不是索引 Read committed 隔离级别 
-- 在聚集索引上扫描,所有行上加X锁,此处有个优化,不满足的行在加锁后,判断不满足即可释放锁 

-- id 为主键 repeatable read 隔离级别
-- 在主键id=5行上加X锁

-- id 是唯一索引 repeatable read 隔离级别
-- 在唯一索引id=5行上加X锁,在主键索引上对应列加X锁 

-- id 是非唯一索引 repeatable read 隔离级别 
-- 在非唯一索引上查找id=5行,找到则加上X锁和GAP锁,然后对应的聚集索引加上X锁; 没有找到则 加上GAP锁 

-- id 不是索引 repeatable read 隔离级别 
-- 在聚集索引上扫描,所有行加上X锁和GAP锁
示例2
-- 在 RR 下 
-- 不加任何锁
select .. from t; 
-- 扫描到任何索引行上加S锁(next-key lock) 在聚集索引上加X锁 
select...from t lock in share mode; 
-- 扫描到任何索引行上加X锁(next-key lock) 在聚集索引上加X锁 
select..from t for update; -- 扫描到任何索引行上加X锁(next-key lock) 在聚集索引上加X锁
update..where condition delete from..where condition 
-- 如果是间隙插入,先添加 insert intention lock, 后在该行上添加X锁; 
-- 如果是递增插入,添加 auto-inc lock 或者 轻量级的互斥锁; 
insert into ...
这篇关于MySQL基础7——隔离级别,锁的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!