14.2.2.6 Locks Set by Different SQL Statements in InnoDB

原创 2015年11月20日 11:32:21

14.2.2.6 Locks Set by Different SQL Statements in InnoDB

锁定读,更新或者删除通常设置record locks 在每个index record,被扫描处理SQL语句。

不重要的是 是否有WHERE 条件在语句里 来排除记录。

InnoDB 不记住准确的WHERE 条件,但是只知道被扫描的索引范围。

locks 通常是 next-key locks 也堵塞inserts 到”gap” .

然而,gap locking可以被显示的关闭, 会导致next-key locking 不被使用

有关更多的信息,查看章节14.2.2.4, “InnoDB Record, Gap, and Next-Key Locks”. The transaction isolation level

also can affect which locks are set; see Section 13.3.6, “SET TRANSACTION Syntax”.

如果一个secondary index 被用于搜索和index record locks 设置是排它的,

InnoDB 也检索相应的clustered index records和加锁在上面。

共享锁和排它锁之间的差别在Section 14.2.2.1, “InnoDB Lock Modes”.描述

如果没有合适的索引对你的SQL语句,MySQL 必须扫描整个表来处理语句,

表的每个行都被锁住,从而阻止所有其他用户插入这个表。

重要的是创建一个好的索引,你的查询不必要扫描很多行。

对于 SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE,

locks 被获得的行, 并被期望释放行不符合结果集标准的(比如, 如果它们不满足其中给出的标准).

然后,在某些情况下,记录可能不被立即锁定 因为关系在结果集和它的原始源是丢失了在查询执行期间。

比如, 在一个UNION, 被扫描的(被锁定的) 记录 可能被插入到一个临时表在评估是否它们符合结果集。

InnoDB 设置具体的锁类型如下:

1.SELECT … FROM 是一个一致性读, 读取一个数据库的快照 ,不设置锁除非事务隔离级别设置为SERIALIZABLE。

对于SERIALIZABLE level, 搜索设置共享的next-key locks 在index records.

2.SELECT … FROM … LOCK IN SHARE MODE 设置共享的 next-key locks 在所有的index records

3.搜索遇到的Index records, SELECT … FROM … FOR UPDATE 堵塞其他的会话

做 SELECT … FROM … LOCK IN SHARE MODE 或者 读某个事务隔离级别。

一致性读会忽略任何锁在记录上。

  1. UPDATE … WHERE .. 设置一个排它的next-key lock 在每个搜索的记录

5.DELETE FROM … WHERE … 设置一个排它的next-key lock 在每个搜索的记录

  1. INSERT 设置一个排它锁在插入的行,这个锁是一个Index-record 锁,

不是一个next-key lock(也就是说, 这个不是一个区间锁)

不妨碍其他会话插入gap 在行插入前

在插入该行之前, 一个区间锁叫做一个插入意向区间锁被设置,

这个锁发出信号意图是插入以这种方式,多个事务插入相同的index gap 不需要相互等待

如果它们不插入相同的位置在区间里。

假设有Index records 值为4和7, 单独的事务尝试插入值5和6 每个锁区间是4和7

插入意向锁优先的的到一个排它锁 在插入的行,但是不堵塞其他因为行是不冲突的。

如果发生重复的错误,一个共享的锁在重复的index record 被设置。共享锁的使用可能导致死锁

有多个会话尝试插入相同的数据 如果其他的会话已经有一个排它锁。

这个发生在另外的会话删除记录,假设一个InnoDB 表t1 有下面的结构;

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

现在假设有3个会话按顺序执行下面的操作:

Session 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 1:

ROLLBACK;

session 1的操作需要一个记录的排它锁, session 2的操作和session 3 导致重复的行错误发生

它们都需要请求一个共享锁对于这条记录。 当session 1回滚时, 它释放它的记录上的排它锁

session 2和session 3请求的共享锁被授予。在这个时间点,session 2和session 3死锁:

都不能获得排它锁对于记录 因为共享锁被其他人持有。

一个相似的请求发生 如果表已经包含一个记录 值为1 ,3个会话按顺序执行下面的操作:

Session 1:

START TRANSACTION;
DELETE FROM t1 WHERE i = 1;
Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 1:

COMMIT;

SESSION 1 的操作需要一个记录的排它锁, session 2和session 3的操作都导致 重复键错误

它们都需要一个记录的共享锁。 当session 1提交时,它释放它的排它锁,session 2和session 3请求的共享锁被授予。

INSERT … ON DUPLICATE KEY UPDATE 不同于一个单独的插入 一个排它的next-key lock 相比一个共享锁

REPLACE 像INSERT 一样 如果没有一个冲突在一个唯一键,否则, 一个排它的next-key lock 是防止在需要替换的记录上。

INSERT INTO T SELECT … FROM S WHERE … 设置一个排它index record lock(不是一个gap锁)

在每个插入T表的行。如果事务隔离级别是 READ COMMITTED,

或者 innodb_locks_unsafe_for_binlog 是启用的 且事务的隔离级别不是SERIALIZABLE,

InnoDB 不搜索 S表 作为一致读(没有锁).否则, InnoDB 设置共享的next-key locks 在S表的记录上。

InnoDB 可以随后设置locks

CREATE TABLE … SELECT … 执行SELECT 的共享的next-key locks或者作为一致读

当SELECT 是用于构成REPLACE INTO t SELECT .. FROM s WHERE … or UPDATE t … WHERE col IN (SELECT … FROM s

…), InnoDB 设置共享的next-key locks

当初始化一个先前指定的AUTO_INCREMENT 列,InnoDB 设置一个排它的锁在索引相关的尾部

在访问auto-increment 计数器,InnoDB 使用一个特定的 AUTO-INC 表lock 模式

当lock 持续 知道当前SQL语句的尾部,

不是整个事务的尾部。 其他的sessions 不能插入 当 AUTO-INC 表被锁定

InnoDB 获取先前初始化的自增列的值 不需要设置任何锁

如果一个外键的约束是在表上定义,

LOCK TABLES 设置表锁, 但是它是更高的MySQL 层次 在InnoDB 层面之上

InnoDB 担心表锁 如果 innodb_table_locks = 1 (the default) 和 autocommit = 0,

14.5.3 Locks Set by Different SQL Statements in InnoDB

14.5.3 Locks Set by Different SQL Statements in InnoDB 通过不同的SQL语句设置的锁 在InnoDB中 一个锁定读, 一个UPDATE 或...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2016年10月28日 13:15
  • 209

ERROR 1222 (21000): The used SELECT statements have a different number of columns

1) ERROR 1222 (21000): The used SELECT statements have a different number of columns :     这是因为使用...
  • linshichen
  • linshichen
  • 2016年09月09日 09:37
  • 697

UNION关键字报错:ERROR 1222 (21000): The used SELECT statements have a different number of columns

ERROR 1222 (21000): The used SELECT statements have a differen number of columns
  • sinat_28978689
  • sinat_28978689
  • 2017年02月12日 17:36
  • 352

[Err] 1222 - The used SELECT statements have a different number of columns

1、错误描述[Err] 1222 - The used SELECT statements have a different number of columns2、错误原因3、解决办法...
  • you23hai45
  • you23hai45
  • 2015年11月16日 22:10
  • 6732

The used SELECT statements have a different number of columns 错误解决

昨天,遇到了一个sql异常错误信息:java.sql.SQLException: The used SELECT statements have a different number of colum...
  • u012561176
  • u012561176
  • 2017年08月04日 08:48
  • 427

14.3.3 Locks Set by Different SQL Statements in InnoDB 不同的SQL语句在InnoDB里的锁设置

14.3.3 Locks Set by Different SQL Statements in InnoDB 不同的SQL语句在InnoDB里的锁设置 locking read, 一个UPDATE...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2016年06月20日 10:37
  • 133

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes

1.版本 1)操作系统  cat /etc/issue Red Hat Enterprise Linux Server release 5.5 (Tikanga) Kernel \r ...
  • shaochenshuo
  • shaochenshuo
  • 2015年12月02日 19:16
  • 1438

InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes

启动WAMP Server的时候报如下的错误: 140618 23:12:32 [Note] Plugin 'FEDERATED' is disabled. 140618 23:12:32 Inno...
  • ccssddnnbbookkee
  • ccssddnnbbookkee
  • 2014年06月19日 00:02
  • 2184

OCP-1Z0-051 第158题 select... for update语句注意事项

一、原题 Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all t...
  • hollo_hhy
  • hollo_hhy
  • 2014年05月24日 12:40
  • 3433

Intrinsic Locks & Synchronized Statements

Java的同步建立在intrinsic lock(也称为monitor lock)的基础之上,intrinsic lock用于保证对指定对象状态的排他性访问及建立happens-before关系。每一...
  • u011491148
  • u011491148
  • 2013年07月24日 16:48
  • 564
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:14.2.2.6 Locks Set by Different SQL Statements in InnoDB
举报原因:
原因补充:

(最多只允许输入30个字)