南宁企业网站推广技巧,深圳设计院工资一般多少,贵州建设职业学院官方网站,鹤壁做网站多少钱目录 概述三种层级的锁锁相关的 SQLMyISAM引擎下的锁InnoDB引擎下的锁InnoDB下的表锁和行锁InnoDB下的共享锁和排他锁InnoDB下的意向锁InnoDB下的记录锁#xff0c;间隙锁#xff0c;临键锁记录锁#xff08;Record Locks#xff09;间隙锁#xff08;Gap Locks#xff0… 目录 概述三种层级的锁锁相关的 SQLMyISAM引擎下的锁InnoDB引擎下的锁InnoDB下的表锁和行锁InnoDB下的共享锁和排他锁InnoDB下的意向锁InnoDB下的记录锁间隙锁临键锁记录锁Record Locks间隙锁Gap Locks临键锁Next-Key Locks InnoDB下的插入意向锁 概述
数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问变得有序所设计的一种规则。
MySQL数据库由于其自身架构的特点存在多种数据存储引擎每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计所以各存储引擎的锁定机制也有较大区别。
总体锁的知识分类架构如下 三种层级的锁
表级锁定
表级锁是MySQL中锁定粒度最大的一种锁表示对当前操作的整张表加锁它实现简单资源消耗较少被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
当然锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高致使并发度大打折扣。
页级锁定
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快但冲突多行级冲突少但速度慢。所以取了折衷的页级一次锁定相邻的一组记录。BDB支持页级锁。
页级锁定
行级锁是Mysql中锁定粒度最细的一种锁表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小但加锁的开销也最大
从上到下锁的粒度逐渐细粒化, 但实现开销逐渐增大。 同时我们也要须知表锁页锁行锁并不是一个具体的锁仅代表将数据库某个层级上的数据进行锁定。具体怎么去锁这个数据还要看具体的锁实现是什么 锁相关的 SQL
要认识事务SQL与锁的知识最好的办法就是调试。所以我这里提供给大家MySQL 8.0以及MySQL 8.0以下的查询事务持有锁情况的SQL
MySQL 8.0 以下
在TRANSACTION位置可以看到事务持有锁的情况但是需要经验分析名词比较难懂
show engine innodb status;查看innodb引擎下所有表所有事务的加锁情况无法分别gap,record,next-key锁的具体类型。没有冲突情况无法显示持有的锁
select * from information_schema.innodb_locks; 查看innodb引擎下所有表所有事务因锁的阻塞情况谁被谁block住了
select * from information_schema.innodb_locks_wait;MySQL 8.0
在TRANSACTION位置可以看到事务持有锁的情况但是需要经验分析名词比较难懂
show engine innodb status;查看数据库中所有表所有事务的加锁情况
没有冲突也看到持有了什么锁具有具体的分类简单好用更容易分析
select * from performance_schema.data_locks; 查看数据库中所有表所有事务因锁的阻塞情况谁被谁block住了
select * from performance_schema.data_locks_wait;MyISAM引擎下的锁
MyISAM只支持表锁不支持行锁和页面锁。
表锁并不是一个真正的锁只是代表对数据库表层级的数据进行锁定。具体以什么形式锁定则要看具体的具体锁实现。
MyISAM表锁的具体实现有表级共享锁和表级排他锁。
表共享读锁表级共享锁
获得表共享读锁的事务可以读取该表的任意数据。MyISAM引擎在执行select语句前会自动给涉及的表加表共享读锁
表独占写锁表级排他锁
获得表独占写锁的事务可以更新或删除该表中任意数据。在执行update,delete,insert等语句前会自动给涉及的表加表独占写锁
很多多场合更喜欢把表级共享锁和表级排他锁叫做表共享读锁和表独占写锁实际上它们的意思是一样的。
表共享读锁和表独占写锁的兼容性 即MyISAM的表锁可以做到读读共享读写互斥写写互斥的功能
读读共享 事务A获得表读锁事务B依然可以获得表读锁读写互斥 事务A获得表读锁事务B申请该表写锁则会阻塞因为事务A获得了该表读锁有互斥性写写互斥 事务A获得表写锁事务B申请该表写锁则会阻塞因为事务A获得了该表写锁也有互斥性
MyISAM在一定程度上还是可以支持并发的进行查询和插入操作的。即如果MyISAM表中没有空洞即表的中间没有被删除的行MyISAM允许在一个线程读表的同时另一个线程从表尾插入记录。这属于MyISAM的特性所以InnoDB存储引擎是不支持的
总结
虽然MySQL支持表页行三级锁定但MyISAM存储引擎只支持表级的数据锁定即表锁。所以MyISAM的加锁相对比较开销低但数据操作的并发性能相对就不高。但如果写操作都是尾插入那么还是可以支持一定程度的读写并发MyISAM的数据操作如果导致加锁行为那么该锁肯定是一个表锁会对全表数据进行加锁。但也要看具体什么形式的表锁不同形式的表锁有着不同的特性从MyISAM所支持的锁中也可以看出MyISAM是一个支持读读并发但不支持通用读写并发写写并发的数据库引擎所以它更适合用于读多写少的应用场合
InnoDB引擎下的锁
InnoDB功能则更为强大锁也更为复杂主要以下几种 共享锁和排他锁 (Shared and Exclusive Locks) 意向锁Intention Locks 记录锁Record Locks 间隙锁Gap Locks 临键锁 Next-Key Locks 插入意向锁Insert Intention Locks 主键自增锁 (AUTO-INC Locks) 空间索引断言锁Predicate Locks for Spatial Indexes
这里只讲重点不讲主键自增锁空间索引断言锁。
InnoDB下的表锁和行锁
表锁行锁并不是一个真正的锁只是代表可以对数据库对应层级的数据进行锁定。具体以什么形式去锁定则要看具体的具体锁实现。
表锁具体的锁实现则有共享锁排他锁意向锁等。而行锁则有共享锁和排他锁另外行锁本身还有记录锁间隙锁和临键锁的不同算法实现。
InnoDB行锁是通过给索引上的索引项加锁来实现的这一点MySQL与Oracle不同后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁的实现也意味着只有通过索引条件检索数据InnoDB才使用行级锁否则InnoDB将使用表锁
由于MySQL的行锁是针对索引加的锁不是针对记录加的锁所以虽然是访问不同行的记录但是如果是使用相同的索引键还是会出现锁冲突的因为他们使用的是同一把锁。
当表有多个索引的时候不同的事务可以使用不同的索引锁定不同的行另外不论是使用主键索引、唯一索引或普通索引InnoDB都会使用行锁来对数据加锁。但是使用不同索引查询相同行会有锁冲突。
InnoDB下的共享锁和排他锁 共享锁(Shared Lock)既S锁又称读锁。事务拿到某一行记录的S锁才可以读取这一行的数据。共享锁可以实现读读共享读写互斥 排他锁(Exclusive Lock)既X锁又称写锁独占锁。事务拿到某一行记录的X锁才可以修改或者删除这一行的数据。排他锁可以实现读写互斥写写互斥。
因为InnoDB支持表锁和行锁。所以在数据库层次结构的表级和行级都可以对数据进行锁定。
表级共享锁又称为表共享读锁即在表的层级上对数据加以共享锁实现读读共享表级排他锁又称为表独占写锁即在表的层级上对数据加以排他锁实现读写互斥写写互斥行级共享锁即在行的层级上对数据加以共享锁实现对该行数据的读读共享行级排他锁即在行的层级上对数据加以排他锁实现对该行数据的读写互斥写写互斥
共享锁和排它锁的兼容性
标题共享锁排他锁共享锁兼容不兼容排他锁不兼容不兼容
多个事务可以拿到一把S锁可以实现读读共享而只有一个事务可以拿到X锁可以实现读写互斥写写互斥。
一句话就是读读共享读写互斥写写互斥。
怎么显式地加共享锁或排他锁
select * from table lock in share mode 。为table的所有数据加上共享锁既表级共享锁select * from table for update。 为table的所有数据加上排他锁既表级排他锁select * from table where id 1 for update。 为table中id为1的那行数据加上排他锁既行级排他锁select * from table where id 1 lock in share mode。为table中id为1的那行数据加上共享锁既行级共享锁
当然以上加的是行锁的前提是id为主键且在查询命中否则行锁会轮为表锁。
InnoDB下的意向锁
通常情况下表锁和行锁是相互冲突的获得了表锁就无法再获得该表具体行的行锁反之亦然。但是有的时候表锁和行锁实现部分的共存有利于更细粒度的对锁进行控制以便得到更加的并发性能。
例如
事务 A 获取了某一行的排他锁并未提交
SELECT * FROM users WHERE id 6 FOR UPDATE;事务 B 想要获取 users 表的表锁
LOCK TABLES users READ;因为共享锁与排他锁互斥所以事务 B 在视图对 users 表加共享锁的时候必须保证
当前没有其他事务持有 users 表的排他锁。当前没有其他事务持有 users 表中任意一行的排他锁 。
为了检测是否满足第二个条件事务 B 必须在确保 users 表不存在任何排他锁的前提下去检测表中的每一行是否存在排他锁。很明显这是一个效率很差的做法但是有了意向锁之后情况就不一样了事务 B 只要看表上有没有意向共享锁有则说明表中有些行被共享行锁锁住了因此事务 B 申请表的写锁会被阻塞。这样是不是就高效多了。
所以InnoDB为了实现行锁和表锁共存的多粒度锁机制特性InnoDB存储引擎也就还支持一种额外的锁方式以对多粒度锁机制进行支持称之为意向锁Intention Lock。
意向锁的维护是由存储引擎隐式帮我们做了意向锁是由存储引擎自己维护的用户无法手动操作意向锁在为数据行加共享 / 排他锁之前InooDB 会先获取该数据行所在的表的对应意向锁。
怎么理解意向锁
意向锁Intention Lock就是一种不与行级锁冲突的表级锁重点意向锁的主要目的展示出某事务已对表中某行加锁或即将对表中某行加锁意向锁就是指未来的某个时刻事务可能要对某行加共享锁或排它锁了先提前声明一个意向
并且意向锁具体实现又可以分为以下两种
意向共享锁intention shared lock,IS事务打算给表中的某些行加行级共享锁事务在给某些行加S锁前必须先取得该表的IS锁。意向排他锁intention exclusive lock,IX事务打算给表中的某些加行级排他锁事务在给某些行加X锁前必须先取得该表的IX锁。
总之所谓意向锁就是提前表明了一个要加行锁的“意向”。当某表存在排他意向锁时代表该表的某行可能存在行级排他锁。同理当某表存在共享意向锁时代表该表的某行可能存在行级共享锁
意向锁的兼容性
标题意向共享锁(IS)意向排他锁(IX)表级共享锁(S)表级排他锁(X)意向共享锁(IS)兼容兼容兼容不兼容意向排他锁(IX)兼容兼容不兼容不兼容表级共享锁(S)兼容不兼容兼容不兼容表级排他锁(x)不兼容不兼容不兼容不兼容
意向锁与意向锁之间永远是兼容的因为当你不论加行级的 X 锁或 S 锁都会自动获取表级的 IX 锁或者 IS 锁。也就是你有 10 个事务对不同的 10 行加了行级 X 锁那么这个时候就存在 10 个 IX 锁。
这 10 个 IX 存在的作用是啥呢就是假如这个时候有个事务想对整个表加排它 X 锁,那它不需要遍历每一行是否存在 S 或 X 锁而是看有没有存在 意向锁只要存在一个意向锁那这个事务就加不了表级排它(X)锁要等上面 10 个 IX 全部释放才行。
意向锁还有一个好处
1事务A向student表申请某个行记录的S锁行级所以首先要为student表上IS锁。 2事务A获得锁完毕后事务B向student表申请全表的S锁表级。于是数据库开始锁冲突的判定 3数据库首先判断事务B申请表锁前student表是否已经存在表级S,X锁发现没有 4数据库再判断student是否已经存在意向锁IS,IX然后发生之前有事务A申请过IS锁 5根据表级S锁和意向IS锁的兼容性策略两者是兼容的。所以事务B直接就获得对student表的表级S锁 6此时student表已经共存了表级S锁和行级S锁。
所以意向锁的出现也让表锁和行锁得到一定的共存提供了部分的并发性能
InnoDB下的记录锁间隙锁临键锁
我们的行锁根据锁的粗细粒度又可以分为三种锁记录锁间隙锁和临键锁。
记录锁Record Locks
记录锁(Record Lock)就是我们最单纯认知的行锁既只锁住一条行记录准确的说是一条索引记录。 InnoDB的行锁是依赖索引实现的而其锁住某行数据的本质是锁住行数据对应在聚簇索引的索引记录。
例如某个事务执行了select * from t where id1 for update;语句就相当于它会在id 1的索引记录上加上一把锁以阻止其他事务插入更新删除id 1的这一行。
记录锁是锁住记录锁住索引记录而不是真正的数据记录。如果要锁的列没有索引进行全表记录加锁。记录锁也是排它 (X) 锁所以会阻塞其他事务对其插入、更新、删除。
间隙锁Gap Locks
间隙锁Gap Lock顾名思义它会封锁索引记录中的“缝隙”不让其他事务在“缝隙”中插入数据。 它锁定的是一个不包含索引本身的开区间范围 (index1,index2)。间隙锁是封锁索引记录之间的间隙或者封锁第一条索引记录之前的范围又或者最后一条索引记录之后的范围
说白了间隙锁的目的就是为了防止索引间隔被其他事务的 “插入”。 间隙锁是InnoDB权衡性能和并发性后出来的特性。
间隙锁之间是互相兼容的既不同事务的Gap锁是可以共存的可以存在重合区域或是完全重合。多个事务的Gap锁重合代表多个事务都想阻止其他事务往该索引间隙 “插入” 数据。也因为Gap一次就对插入行为锁住了一片的区域不利于多事务并发写。所以后续MySQL也就提供了一种特殊的间隙锁插入意向锁以解决并发写的问题
间隙锁实际也分共享间隙锁排他间隙锁虽然有分类但事实上他们的意义是等价毫无区别的所以可以忽略这一层只要知道它们的作用都是阻止其他事务往间隙插入数据。
间隙锁只发生在事务隔离级别为RR(Repeatable Read)的情况下它用于在隔离级别为RR时阻止幻读(phantom row)的发生隔离级别为RC时搜索和索引扫描时Gap锁是被禁用的只在外键约束检查和 重复key检查时Gap锁才有效正是因为此RC时会幻读问题。
比如某个事务执行select * from table where id between 10 and 20 for update;语句当其他事务往表里 “插入” id在(10,20)之间的值时就会被(10,20)的间隙锁给阻塞。
临键锁Next-Key Locks
Next-key 锁是记录锁和间隙锁的组合它指的是加在 某条记录以及这条记录前面间隙上 的锁。
也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列该索引里面的值允许重复上都会存在一把临键锁当某个事务持有该数据行的临键锁时会锁住一段 左开右闭 区间的数据。
需要强调的一点是InnoDB 中行级锁是基于索引实现的临键锁只与非唯一索引列有关在唯一索引列包括主键列上不存在临键锁。
假设有如下表id 主键age 普通索引。
idnameage1zhangsan103lisi245wangwu327zhaoliu45
该表中 age 列潜在的临键锁有(-∞, 10](10, 24](24, 32](32, 45](45, ∞]。
在事务 A 中执行如下命令
-- 根据非唯一索引列 UPDATE 某条记录
UPDATE table SET name Vladimir WHERE age 24; -- 或根据非唯一索引列 锁住某条记录
SELECT * FROM table WHERE age 24 FOR UPDATE;不管执行了上述 SQL 中的哪一句之后如果在事务 B 中执行以下命令则该命令会被阻塞
INSERT INTO table VALUES(100, 26, tianqi);很明显事务 A 在对 age 为 24 的列进行 UPDATE 操作的同时也获取了 (10, 32] 这个区间内的 临键锁。
这里对 记录锁、间隙锁、临键锁 做一个总结
我们知道InnoDB的行锁默认是基于BTree的。所以行锁依赖的索引是有序的。
记录锁就是单纯意义上的行锁锁的就是单行数据该数据是真实存在的而间隙锁则是锁住一个区间中多行数据但这些多行的数据实际是并不存在的。既只锁住真实数据对应索引项之间的一个空间范围而临键锁说白了就是记录锁间隙锁的组合。只要把记录锁和间隙锁组合在一起就是临键锁既锁住索引项本身的真实数据又锁住两两索引之间没有数据的空间范围。
InnoDB下的插入意向锁
插入意向锁是在插入一条记录行前由 INSERT 操作产生的一种特殊的间隙锁。
该锁用以表示插入意向当多个事务在同一区间gap插入位置不同的多条数据时事务之间不需要互相等待。
假设存在两条值分别为 4 和 7 的记录两个不同的事务分别试图插入值为 5 和 6 的两条记录每个事务在获取插入行上独占的排他锁前都会获取47 ] 之间的间隙锁但是因为数据行之间并不冲突所以两个事务之间并不会产生冲突阻塞等待。
总结来说插入意向锁 的特性可以分成两部分
插入意向锁是 一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
插入意向锁之间互不排斥所以即使多个事务在同一区间插入多条记录只要记录本身主键、唯一索引不冲突那么事务之间就不会出现冲突等待。虽然插入意向锁中含有意向锁三个字但是它并不属于意向锁而属于间隙锁因为意向锁是表锁而插入意向锁是行锁。
插入意向锁的作用以及为何替代Gap锁
为普通的Gap锁一旦锁住了某个间隙这个间隙就不再允许其他事务插入数据了。既其他事务需要等待当前事务释放该区间的Gap锁后才能插入数据。所以我们可以知道因为Gap锁对插入行为的相对锁粒度比较大就不利于提高多事务对同一个索引间隙的数据插入性能了。
插入意向锁就不一样了插入意向锁虽然跟Gap锁一样一次锁的是一个区间。但是它可以允许多个事务在这个区间内执行不同数据的插入互不干扰。
参考 【MySQL笔记】正确的理解MySQL中让你想到就烦的各种锁
【数据库】MySQL 中的锁机制
