14.2.2.1 InnoDB Lock Modes

14.2 InnoDB Concepts and Architecture InnoDB的概念和体系结构

14.2.1 MySQL and the ACID Model
14.2.2 The InnoDB Transaction Model and Locking
14.2.3 InnoDB Multi-Versioning
14.2.4 InnoDB Redo Log
14.2.5 InnoDB Undo Logs
14.2.6 InnoDB Table and Index Structures
14.2.7 InnoDB Mutex and Read/Write Lock Implementation

实施一个大规模的,忙碌的 或者高可靠的数据库应用,从不同的数据库系统真实的代码,

为了调优MySQL 性能, 你必须了解交易的概念和锁它们关联的InnoDB storage engine.

在InnoDB 事务模型里,目标是将多版本数据库具有传统的2阶段锁定 组合做好的特性。

InnoDB 锁定行和运行沙村默认是非锁定的一致读,

在Oracle的风格,lock 信息在InnoDB是存储 因此空间有效的 ,锁升级是不需要的。

通常情况下,多个用户可以允许锁定每行记录在InnoDB表里,或者任何随机的行的子集,

不需要造成InnoDB 内存消耗。

在InnoDB，所有的用户活动 发生在一个事务里。如果自动提交时启用的,

每个语句形成一个单独的事务。默认的，

MySQL 对每个连接开启一个事务,因此MySQL 在每个语句后执行一个commit 如果语句没有返回错误

如果语句返回错误, commit 或者rollback 的行为取决于错误。见第14.18.4，“InnoDB错误处理”。

一个会话设置autocommit enabled 可以执行一个多版本事务通过启用一个显示的START TRANSACTION

或者BEGIN 语句和以COMMIT或者ROLLBACK 语句结束。

See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

如果autocommit mode 关闭 设置 autocommit = 0,

session总是打开事务。一个自动提交或者回滚语句 结束当前的会话和启动一个新的会话。

一个COMMIT 意味着当前事务做的改变是变为永久的,对其他会话可见。

一个ROLLBACK 语句,取消所有的当前事务做的修改。 COMMIT和ROLLBACK 释放所有的InnoDB 锁

SQL:1992 事务隔离级别的术语,默认InnoDB 是REPEATABLE READ.(重复读隔离)

InnoDB 提供全部的4种事务隔离级别 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

一个用户可以改变隔离级别对于单个事务或者对于所有的随后的连接 使用SET TRANSACTION statement.

设置server 默认的隔离级别对于所有的连接, use the –transaction-isolation option on the command line or in an

option file.

在行锁级别,InnoDB通常使用 next-key locking. 这个意味着 除了index records，

gap 间隙锁

InnoDB 可以锁定间隙在一个索引记录前来堵塞其他会话的插入。

14.2.2.1 InnoDB Lock Modes

InnoDB 实现标准的 row-level locking 这里有2种类型的锁,shared (S) locks 和排它锁(X)locks.

关于 record, gap, and next-key lock types 的信息, see Section 14.2.2.4, “InnoDB Record, Gap, and Next-Key Locks

”.

1. 一个A shared (S) lock 允许 事务持有锁来读取记录

2.一个exclusive (X) lock 允许事务持有锁来更新或者删除记录

如果事务T1 持有一个shared (S) lock 在记录r上, 然后从另外的一个事务T2 请求 对于一个lock 在记录r 处理如下:

1. T2的请求 一个S锁 可以理解被授权, 因此, T1和T2 持有S锁在记录r上.

2. T2请求一个X锁不能被立即授予

如果一个事务T1 持有一个 exclusive (X) lock在记录r上, 从不同的事务T2 请求一个任意类型的锁在r上 不能被理解授权。

相反,事务T2 只能等待事务T1 释放记录r上的锁。

Intention Locks 意图锁

此外,InnoDB 支持多版本颗粒度锁 ,允许记录锁和整个表上的锁共存.

为了在多个颗粒度级别锁定,额外的锁的类型被称为intention locks

intention locks 是表锁在InnoDB 表明哪种类型的锁(shared or exclusive) 一个事务需要随后请求。

这里有2种类型的intention locks 用于InnoDB（假设事务T 已经请求一个表t上指定类型的锁)

1.Intention shared (IS): 事务T 倾向于设置S锁在表t的单独的记录

2.Intention exclusive (IX): 事务T 倾向于设置X锁 在那些记录

例如, SELECT … LOCK IN SHARE MODE 设置一个IS lock 和SELECT … FOR UPDATE sets an IX lock.

intention locking 协议如下:

1. 在一个事务可以获得S锁在表的一行记录, 它必须首先获得一个IS 或者 stronger lock on t.

2.在一个事务可以获得一个X 锁在一条记录,它必须首先获得一个IX 锁 on t

这些规则可以很方便的通过下面的锁类型的兼容性:

       X              IX              S         IS

X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

一个锁是被授予来请求事务 如果是和现在的锁兼容的, 但不和当前存在的锁冲突。

一个事务等待直到冲突锁被释放。如果一个锁请求和存在的锁冲突 ,不能被授权 因为它会导致死锁,或者产生错误

因此, intention locks 不堵塞任何除非 全表扫描请求(比如, LOCK TABLES … WRITE).

IX 和IS 锁的主要的目的是显示有人锁住一行,或者 想要尝试锁住一行。

Deadlock Example

下面的例子阐释使用一个错误可以产生当一个lock 请求会导致死锁。 例子涉及两个客户端,A和B:

首先, 客户端A 创建一个表包含一条记录,然后开始一个事务。

在事务中,得到一个S锁在记录上通过选择它在共享模式下:

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+——+
| i |
+——+
| 1 |
+——+
Next, client B begins a transaction and attempts to delete the row from the table:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

删除操作需要一个X锁, lock不能被立即授予 因为它是和在client A持有的S锁不兼容的,

因为请求在锁请求的队列上 。

最后,客户端A 尝试从表中删除记录:

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

死锁发生 因为客户端需要一个X锁来删除记录, 然而, lock请求不能被立即授予

因为客户端B已经请求了一个X锁,客户端B在等待客户端A 来释放S锁。

由于客户端A持有S锁不能被升级为X锁 因为B之前的额请求是X锁

结果,InnoDB 产生一个错误 然后释放锁,客户端返回错误。

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

在该点上, 其他客户端的锁请求可以被授权,然后它删除记录:

注意: