关闭

14.2.2.6 Locks Set by Different SQL Statements in InnoDB

340人阅读 评论(0) 收藏 举报
分类:

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,

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1302206次
    • 积分:40277
    • 等级:
    • 排名:第99名
    • 原创:2861篇
    • 转载:14篇
    • 译文:0篇
    • 评论:51条
    文章分类
    最新评论