【澳门金莎娱乐网站】MySQL种种SQL语句的加锁机制

作者: 数据库信息  发布:2019-07-18

 

record lock:记录锁,也就是仅仅锁着单独的一行
gap lock:区间锁,仅仅锁住一个区间(注意这里的区间都是开区间,也就是不包括边界值。
next-key lock:record lock gap lock,所以next-key lock也就半开半闭区间,且是下界开,上界闭。 www.jb51.net
next-key 锁定范围:(负无穷大,最小第一记录],(记录之间],(最大记录,正无穷大)

如果执行的SQL找不到合适的索引,InnoDB不得不去进行全表扫描,那么InnoDB会把表的每一个聚集索引记录都锁住,这可以看作是表级锁,同样参考的表锁部分。这种全表锁定会导致其他事务无法插入和更改(同样参考链接中的表锁兼容性部分),因此为SQL创建合适的索引是很有必要的,因为表锁(非意向锁)会导致DML操作阻塞。

您可能感兴趣的文章:

  • MySQL InnoDB之事务与锁详解
  • MySQL中InnoDB存储引擎的锁的基本使用教程
  • 详谈innodb的锁(record,gap,Next-Key lock)

REPLACE INTO t SELECT ... FROM s WHERE ...或者UPDATE t ... WHERE col IN (SELECT ... FROM s ...)这两种SQL语句对s表的行添加S模式的next-key行锁。

一、innodb行锁分类

5.当UPDATE语句修改的是主键索引时,InnoDB会隐式的将所有的次级索引锁定(二级索引都是用主键做书签的,因此修改主键索引是很耗资源的操作)。在插入二级索引记录或者为插入二级索引做重复性检查扫描时(unique index),update也会把受影响的二级索引锁定。

INSERT INTO T SELECT ... FROM S WHERE ... 对每个插入到T的行设置独占(非next-key)锁定。它在S上把搜索当作一个持续读,但是如果MySQL二进制日志功能被打开,它就对S设置一个共享的next-key锁
定。InnoDB在后一种情况不得不设置锁定:在从一个备份的前滚恢复中,每个SQL语句不得不以与它最初被执行的方式完全同样的方式执行。

 

· CREATE TABLE ... SELECT ... 把SELECT当作一个持续读来执行,或者带着共享锁定来执行,如前面的条目所述。
· 如果唯一键没有冲突,REPLACE象一个插入一样被做。另外,对必须更新的行设置一个独占的nextkey锁定。
· UPDATE ... WHERE ... 对搜索遇到的每个记录设置一个独占的next-key锁定。
· DELETE FROM ... WHERE ... 对搜索遇到的每个记录设置一个独占的next-key锁定。
· 如果对一个表定义FOREIGN KEY约束,任何需要检查约束条件的插入,更新或删除对它看着检查约束的记录设置共享行级锁定。InnoDB在约束失败的情况下也设置这些锁定。

 

SELECT ... FROM ... FOR UPDATE对读遇到的所有索引记录设置独占的next-key锁定。
INSERT INTO ... VALUES (...)对被插入的行设置独占锁定。注意,这不是一个next-key锁定,并且不阻止其它用户在已插入行之前的间隙插入。如果发生重复键错误,对重复的索引记录设置共享锁定。
· 在一个表上初始化之前指定的AUTO_INCREMENT列之时,InnoDB在与AUTO_INCREMENT列相关联的索引的末尾设置独占锁定。在访问自动增长计数器中,InnoDB使用专用的表锁定模式AUTO-INC,其中锁定仅持续到当前SQL语句的结束,而不是到整个事务的结束。InnoDB取回先前初始化的AUTO_INCREMENT列的值而不设定任何锁定。

13.如果表上有外键约束,那么任何需要做外键约束检测的DML语句都会在相应的外键上添加S模式的行锁。即便约束失败也会设置这些行锁。

二、语句锁定情况分析

官网参考:https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

3.SELECT ... FROM ... FOR UPDATE在扫描到的索引记录上添加X模式的Next-key行锁,同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。

这些锁定添加的锁通常是next-key lock,这种锁既锁定扫描到的索引记录,也锁定索引间的gap。不过gap锁可以被显示的禁用,参考的Gap lock部分。

14.LOCK TABLES也会在表上设置表锁,只是这种表锁并非是InnoDB层的表锁,而是MySQL层的表锁。因此如果死锁涉及到这些表锁时,InnoDB的死锁自动检测机制无法检测到这些表锁。而且由于MySQL层对InnoDB层的行锁机制并不清楚,因此此类表锁甚至可以加在正在使用行锁的InnoDB表上。不过这并不会危及到事务的完整性,具体说明详见:

12.关于AUTO-INC Locks参考的AUTO-INC Locks部分。

如果在SQL执行时你需要对次级索引记录加X模式的行锁,那么InnoDB也会检索相应的主键索引并加锁。

对于select...for update和select...lock in share mode这种锁定读来说,开始时InnoDB会锁定所有扫描的索引记录,但是最后会释放那些不符合条件的索引记录上的锁(例如被where语句过滤掉的行)。但是在某些情况下由于结果行与源表的联系丢失,导致这些行锁不会被释放,例如:union操作,被扫描的中间结果行会被插入到一个临时表中以便形成最终的结果集,在这种情况下锁定行与原表之间的联系丢失,那么剩余的扫描行直到整个SQL执行结束才会被释放(不是事务执行结束)。

 

 

 

 

 

6.DELETE FROM ... WHERE ...语句会在扫描到的所有记录上添加X模式的next-key lock(即便被删的行不存在),同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。

 

 

 

虽然Insert语句不使用gap行锁,但是会使用一种叫插入意向锁的gap锁,即Insert Inrention Locks。这种锁的作用是为添加行锁做锁冲突检测,具体示例参考的插入意向锁部分。

 

本文由金沙澳门官网发布于数据库信息,转载请注明出处:【澳门金莎娱乐网站】MySQL种种SQL语句的加锁机制

关键词: 金沙澳门官网

上一篇:没有了
下一篇:没有了