MySQL 各种SQL语句加锁分析

概要

本文源于同事王航威的翻译,对原文做了简单的修改 ,原文地址: http://www.fordba.com/locks-set-by-different-sql-statements-in-innodb.html

Locking read( SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE),UPDATE以及DELETE语句通常会对通过索引扫描的记录加上 next-key locks ,忽略没有用到索引的那部分where语句。

举个例子:

CREATE TABLE `test` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

select * from test where id > 3 and name <'A' for update;

这条SQL语句的将所有id>3的记录进行加锁,而不是对范围 id>3 and name <'A' 进行加锁,因为name上面没有索引

如果一个SQL通过二级索引访问表记录,并且在二级索引上设置了一个锁,那么innodb将会在对应的聚簇索引(主键)记录上也加上一把锁。

如果一个SQL语句无法通过索引进行Locking read,UPDATE,DELETE,那么MySQL将扫描整个表,表中的每一行都将被锁定(在RC级别,通过semi-consistent read,能够提前释放不符合条件的记录,在RR级别,需要设置innodb_locks_unsafe_for_binlog为1,才能打开semi-consistent read)。在某些场景下,锁也不会立即被释放。例如一个union查询,生成了一张临时表,导致临时表的行记录和原始表的行记录丢失了联系,只能等待查询执行结束才能释放。

SQL分析

  1. SELECT ... FROM 是快照读,读取数据的快照,不会加任何锁,除非将隔离级别设置成了 SERIALIZABLE 。在 SERIALIZABLE 隔离级别下,如果索引是非唯一索引,那么将在相应的记录上加上一个共享的next key锁。如果是唯一索引,只需要在相应记录上加index record lock。
  2. SELECT ... FROM ... LOCK IN SHARE MODE 语句在所有索引扫描范围的索引记录上加共享的next key锁。如果是唯一索引,只需要在相应记录上加index record lock。
  3. SELECT ... FROM ... FOR UPDATE 语句在所有索引扫描范围的索引记录上加排他的next key锁。如果是唯一索引,只需要在相应记录上加index record lock。这将阻塞其他会话利用SELECT ... FROM ... LOCK IN SHARE MODE 读取相同的记录,但是快照读将忽略记录上的锁。
  4. UPDATE ... WHERE ...语句在所有索引扫描范围的记录上加上排他的next key锁。如果是唯一索引,只需要在相应记录上加行锁record lock当UPDATE 操作修改主键记录的时候,将在相应的二级索引上加上隐式的锁。当进行重复键检测的时候,将会在插入新的二级索引记录之前,在其二级索引上加上一把共享锁。
  5. DELETE FROM ... WHERE ... 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引,只需要在相应记录上加行锁record lock。
  6. INSERT 语句会对插入成功的记录上加排他锁,这个锁是一个行锁 index-record lock,并不是next-key 锁,因此就没有gap 锁,它将不会阻止其他会话在该条记录之前的gap插入记录。 在插入记录之前,将会加上一种叫 insert intention gap 的 gap 锁。这个 insert intention gap表示它有意向在这个index gap插入记录,如果其他会话在这个index gap中插入的位置不相同,那么将不需要等待。假设存在索引记录4和7,会话A要插入记录5,会话B要插入记录6,每个会话在插入记录之前都需要锁定4和7之间gap,但是他们彼此不会互相堵塞,因为插入的位置不相同。 如果出现唯一键冲突,将在重复键上加一个共享锁。如果会话1插入一条记录,没有提交,他会在该记录上加上排他锁,会话2和会话3都尝试插入该重复记录,那么他们都会被堵塞,会话2和会话3将尝试在该记录上申请加一个共享锁。如果此时会话1回滚,将发生死锁。 参考死锁案例: 死锁案例之三 死锁案例之一
  7. INSERT ... ON DUPLICATE KEY UPDATE 和普通的INSERT并不相同。如果碰到重复键值,INSERT ... ON DUPLICATE KEY UPDATE 将在记录上加排他的 next-key锁
  8. REPLACE 在没有碰到重复键值的时候和普通的INSERT是一样的,如果碰到重复键,将在记录上加一个排他的 next-key锁
  9. INSERT INTO T SELECT ... FROM S WHERE ... 语句在插入T表的每条记录上加上 index record lock 。如果隔离级别是 READ COMMITTED, 或者启用了 innodb_locks_unsafe_for_binlog 且事务隔离级别不是SERIALIZABLE,那么innodb将通过快照读取表S(no locks)。否则,innodb将在S表的记录上加共享的next-key锁。 CREATE TABLE ... SELECT ... 和 INSERT INTO T SELECT ... FROM S WHERE ... 一样,在S上加共享的next-key锁或者进行快照读取((no locks))
  10. REPLACE INTO t SELECT ... FROM s WHERE ... 和 UPDATE t ... WHERE col IN (SELECT ... FROM s ...) 中的select 部分将在表s上加共享的next-key锁。
  11. 当碰到有自增列的表的时候,innodb在自增列的索引最后面加上一个排他锁,叫AUTO-INC table lock 。AUTO-INC table lock会在语句执行完成后进行释放,而不是事务结束。如果AUTO-INC table lock被一个会话占有,那么其他会话将无法在该表中插入数据。innodb可以预先获取sql需要多少自增的大小,而不需要去申请锁,更多设置请参考参数innodb_autoinc_lock_mode。
  12. 如果一张表的外键约束被启用了,任何在该表上的插入、更新、删除都将需要加共享的 record-level locks来检查是否满足约束。如果约束检查失败,innodb也会加上共享的 record-level locks。
  13. lock tables 是用来加表级锁,它是由MySQL的server层来加这把锁的。当innodb_table_locks = 1 (the default) 以及 autocommit = 0的时候,innodb能够感知表锁,同时server层了解到innodb已经加了row-level locks。否则,innodb将无法自动检测到死锁,同时server无法确定是否有行级锁,导致当其他会话占用行级锁的时候还能获得表锁。

参考资料:

http://docs.fordba.com/mysql/refman-5.6-en/innodb-storage-engine.html#innodb-locks-set

原文发表时间:2018-03-31

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏landv

金蝶K/3 固定置产相关SQL语句

1011
来自专栏乐沙弥的世界

一次mysql 5.5升级到5.6导致的ERROR 1805

前阵子将mysql数据库由5.5.14升级到5.6.36,升级后所有的业务数据都正常。运行了几天后,发现在主库上添加用户失败,错误提示为:ERROR 1805 ...

1062
来自专栏散尽浮华

利用mk-table-checksum监测Mysql主从数据一致性操作记录

前面已经提到了mysql主从环境下数据一致性检查:mysql主从同步(3)-percona-toolkit工具(数据一致性监测、延迟监控)使用梳理 今天这里再介...

3858
来自专栏Dato

浅谈 DML、DDL、DCL的区别

一、DML DML(data manipulation language)数据操纵语言:     就是我们最经常用到的 SELECT、UPDATE、INSERT...

3767
来自专栏面朝大海春暖花开

sql

<select id="getListByWhere" parameterType="com.jiyun.beans.Employee" resultMap="...

1262
来自专栏互联网杂技

修改数据表之添加主键约束

上一篇是对表中列的操作: 涉及到列的增删改查; ----- 而本篇是设置一个与另一个表的关系,还有列的默认值; ---- 1.一个表创建之后, 需要对这个表中的...

32811
来自专栏Netkiller

数据库记录安全解决方案

数据库记录安全解决方案 http://netkiller.github.io/journal/mysql.security.html 摘要 数据库记录防删除,放...

4106
来自专栏性能与架构

Mysql group by实现方式(一) - 临时表

当MySQL Query Optimizer无法找到可以利用的合适索引时,就不得不先读取需要的数据,然后通过临时表来完成GROUP BY操作 例如 EXPLAI...

2956
来自专栏Python

异常处理:1215 - Cannot add foreign key constraint

  最近在做新生入学系统,学生表中包括新生的班级,专业等信息,班级,专业就需要和班级表,专业表进行关联,但是在添加外键的过程中却出现了“Cannot add f...

23310
来自专栏西安-晁州

sqlserver - FOR XML PATH

FOR XML PATH 有的人可能知道有的人可能不知道,其实它就是将查询结果集以XML形式展现,有了它我们可以简化我们的查询语句实现一些以前可能需要借助函数活...

2250

扫码关注云+社区

领取腾讯云代金券