MisakaTang's Blog

发表于 更新于 分类于

14.7 InnoDB锁和事务模型

为了实现大规模,繁忙或高可靠的数据库应用,从不同的数据库系统移植大量代码或调整MySQL性能,理解InnoDB锁和InnoDB事务模型是很重要的.

这个部分讨论了几个你应该要熟悉的和InnoDB锁和InnoDB事务模型相关的主题.

  • 14.7.1 InnoDB锁 描述了InnoDB中使用的锁类型.
  • 14.7.2 InnoDB事务模型 描述了事务隔离级别和每个锁的使用策略.同时也讨论了autocommit的使用,一致的无锁读和锁定读取.
  • 14.7.3 InnoDB中不同SQL语句设置的锁 讨论了InnoDB中为各种语句设置的特定锁类型.
  • 14.7.4 幻想行 描述InnoDB如何使用下一个键锁定来避免幻像行
  • 14.7.5 InnoDB中的死锁 提供了一个死锁示例,讨论了死锁检测和回滚,并提供了在InnoDB中最小化和处理死锁的技巧.

14.7.1 InnoDB锁

共享和独占锁(Shared and Exclusive Locks)

InnoDB实现了标准的行级锁,包括两种类型:共享锁和独占锁.

  • 共享锁(S锁)允许持有锁的事务读取行.
  • 独占锁(X锁)允许持有锁的事务更新或删除行.

如果事务T1持有行r的共享锁,之后来自不同事务T2对行r的锁的请求处理如下:

  • 可以立即授予T2一个共享锁.结果是T1和T2都持有对行r的共享锁.
  • 对T2事务的独占锁请求不能马上授予.

如果事务T1持有行r的独占锁,来自不同事务T2的获取锁的请求不能立即授予.相反,T2要等到T1释放对行r的锁(才能获得锁).

意图锁(Intention Locks)

InnoDB支持多粒度锁定允许行级锁和表级锁共存.例如,语句LOCK TABLES ... WRITE在指定表上获取一个独占锁.为了在多个粒度上实现加锁,InnoDB使用意图锁.意图锁是一个表级锁,用来指明下一个事务对表中的行所需要的所类型(共享或独占).有两种类型的意图锁:

  • 意图共享锁(IS锁)表示事务打算在表的行上设置共享锁.
  • 意图独占锁(IX锁)表示事务打算在表的行上设置独占锁.

例如,SELECT ... LOCK IN SHARE MODE设置一个IS锁,SELECT ... FOR UPDATE设置一个IX锁.

意图锁的规则如下:

  • 如果一个事务可以在表的行上获取共享锁,它一定要先在表上获取一个IS锁或更强的锁.
  • 如果一个事务可以在表的行上获取独占锁,它一定要先在表上获取一个IX锁.

表级锁类型兼容总结如下:

如果请求事务与现有锁兼容,则授予锁,但如果它与现有锁冲突则不授予.事务会等待直到冲突的锁被释放.如果加锁请求和现有的锁冲突会无法授予,因为这会导致死锁而发生错误.

意图锁不会阻止任何请求除了对全表加锁的请求(例如LOCK TABLES ... WRITE).意图锁的主要用途是展示某人正在对行加锁或将要对表中的行加锁.

意图锁的事务数据在SHOW ENGINE INNODB STATUS和InnoDB监视器输出显示类似下面的内容:

1
TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

记录锁(Record Locks)

记录锁是在索引记录上的锁.例如,SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;会阻止其他任何事务对t.c1=10的行的插入,更新或删除操作.

记录锁总是对索引记录加锁,甚至表定义中没有索引.在这种情况下,InnoDB创建一个隐藏的聚簇索引并用来作为记录锁的索引.

记录锁的事务数据在SHOW ENGINE INNODB STATUS和InnoDB监视器输出显示类似下面的内容:

1
2
3
4
5
6
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

间隙锁(Gap Locks)

间隙锁是一个锁定两个索引记录之间间隙的锁,或是锁定第一个之前或最后一个索引记录之后的间隙.例如,SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;会阻止其他事务插入15到列t.c1中,无论这一列中是否已经有这个值了,因为所有存在的值之间的间隙已经被锁定了.

间隙可能跨越一个索引值,多个索引值甚至为空.

间隙锁是性能和并发之间权衡的一部分,用于一些事务隔离级别中.

使用唯一索引锁定行搜索唯一行的语句不需要间隙锁.(这不包括搜索条件仅包括多列唯一索引的一些列的情况;在这种情况下,会发生间隙锁定).例如,如果ID列有一个唯一索引,下面的语句只对id值为100的行加索引记录锁而其他会话是否在前面的间隙中插入行都没关系:

1
SELECT * FROM child WHERE id = 100;

如果id列没有被索引或有一个非唯一索引,上面的语句会在前面的间隙加锁.

值得注意的是,冲突锁可以通过不同的事务保持在间隙上.例如,当事务A在a间隙上持有一个共享间隙锁(gap S-lock)上时B事务可以在相同的间隙上持有独占间隙锁(gap X-lock).允许间隙锁冲突的原因是如果索引上的记录被清除,则必须合并由不同事务保留在记录上的间隙锁.

InnoDB中的间隙锁是”单纯抑制(purely inhibitive)”,也就是说其目的就是阻止其他事务插入数据到间隙.间隙锁可以共存.一个事务获取了间隙锁不会阻止其他事务在同一个间隙上获取间隙锁.在共享和独占的间隙锁上没有区别.它们彼此不冲突并且执行相同的功能.

可以明确禁用间隙锁.如果你改变事务隔离级别到 READ COMMITTED或启用 innodb_locks_unsafe_for_binlog系统变量(现已弃用)就会发生.在这种情况下,间隙锁对搜索和索引扫描禁用并且只用在外键约束检查和重复键检查.

使用 READ COMMITTED事务隔离级别或启用 innodb_locks_unsafe_for_binlog还有其他影响.MySQL求值WHERE条件之后会释放不匹配行的记录锁.对UPDATE语句,InnoDB会执行”半一致(semi-consistent)”读,这样就会将最新提交的版本返回给MySQL,以便MySQL确定该行是否与UPDATE的WHERE条件匹配.

Next-Key锁

next-key锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合.

InnoDB以这样的方式执行行级锁定:当搜索或扫描表索引时,会在遇到的索引记录上设置共享锁或排它锁.因此,行级锁实际上是索引记录锁.索引记录上的next-key锁也会影响索引记录前的”间隙”.也就是说,next-key锁是一个索引记录锁加上一个索引记录之前的间隙上的间隙锁.如果一个会话在索引的记录R上有一个共享或者独占锁,另一个会话不能在R的索引顺序前面的间隙里立即插入一个新的索引记录.

假设一个索引包含10,11,13和20.这个索引可能的next-key锁包含下面的范围,圆括号表示不包含边界,方括号表示包含边界:

1
2
3
4
5
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

对最后一个间隔,next-key锁在索引的最大值之上加锁并且这个”最大数”的伪记录比索引中实际存在的任何值都大.最大数不是一个真的索引记录,所以实际上,这个next-key锁只是对最大索引值之后加锁.

默认情况下,InnoDB在 REPEATABLE READ的事务隔离级别下操作.在这种情况下,InnoDB用next-key锁来搜索和扫描索引,这样可以防止幻影行(phantom rows).

next-key锁的事务数据在SHOW ENGINE INNODB STATUS和InnoDB监视器输出显示类似下面的内容:

1
2
3
4
5
6
7
8
9
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

插入意图锁(Insert Intention Locks)

插入意图锁是在行插入前由INSERT操作设置的一种间隙锁.该锁以这样的方式来表示插入的意图:插入相同索引间隙的多个事务不需要互相等待如果它们没有插入到间隙内的相同位置的话.假设有4和7两个索引记录值.两个事务分别要插入值5和6,在获得插入行上的独占锁之前,每个事务都使用插入意图锁锁定4到7之间的间隙,但是不要因为行是不冲突的就不互相阻塞.

下面的例子展示了一个事务在获得插入记录的独占锁之前获取一个插入意图锁.这个例子涉及2个客户端,A和B.

客户端A创建了一个包含2个索引记录(90和102)的表并且开始了一个事务对ID大于100的索引加了独占锁.这个独占锁包含了记录102之前的间隙:

1
2
3
4
5
6
7
8
9
10
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

客户端B开始一个事务并向间隙中插入记录.当其等待获取独占锁的时候获得了一个插入意图锁.

1
2
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

插入意图锁的事务数据在SHOW ENGINE INNODB STATUS和InnoDB监视器输出显示类似下面的内容:

1
2
3
4
5
6
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

自增锁(AUTO-INC Locks)

自增锁是由插入到具有自增(AUTO_INCREMENT)列的表中的事务所采用特殊的表级锁.在最简单的情况下,如果一个事务正在往一个表里插入数据,其他事务必须等待对该表执行插入以便第一个事务插入的行能得到连续的主键值.

innodb_autoinc_lock_mode配置选择控制了自增锁使用的算法.允许你在可预测的自动增量值序列和插入操作的最大并发之间进行权衡.

空间索引谓词锁(Predicate Locks for Spatial Indexes)

InnoDB支持包含空间列的空间(SPATIAL)索引.

为了处理涉及空间索引的操作的锁定,next-key锁在REPEATABLE READ或 SERIALIZABLE事务隔离级别上运行不是很好.多维数据中没有绝对排序概念,因此不清楚哪个是”下一个(next-key)”主键.

为了支持具有空间(SPATIAL)索引的表的隔离级别,InnoDB使用谓词锁.空间索引包含最小边界矩形(MBR)值,因此InnoDB通过在用于查询的MBR值上设置谓词锁来强制对索引进行一致读取.其他事务无法插入或修改与查询条件匹配的行.

发表于 更新于 分类于

14.6.6 Redo日志

redo 日志是一个基于磁盘的数据结构,在崩溃恢复期间用于纠正由不完整事务写入的数据.在正常操作期间,redo日志对更改由SQL语句或低级API调用产生的表数据的请求进行编码.未完成更新数据文件的修改会在以外关闭自动重启的初始化期间和连接接受之前完成.

默认情况下,redo日志在磁盘上由两个名为ib_logfile0和ib_logfile1的文件表示.MySQL以循环方式写入redo日志文件.redo日志中的数据根据​​受影响的记录进行编码;这些数据统称为redo.通过redo日志的数据传递由不断增加的LSN值表示.

改变InnoDB redo日志文件的数量或大小

为了改变InnoDB redo日志文件的大小或数量,执行下面的步骤:

  1. 停止MySQL服务并且保证关闭时没有错误.
  2. 编辑my.cnf改变日志文件配置.修改 innodb_log_file_size来改变日志文件的大小,修改innodb_log_files_in_group来增加日志文件的数量.
  3. 再次启动MySQL服务.

InnoDB如果检测到redo日志文件大小和 innodb_log_file_size不一样,会写下日志检查点,关闭和删除旧的日志文件,按照需要的大小创建新的日志文件并且打开新的日志文件(使用).

redo日志刷新的分组提交(Group Commit for Redo Log Flushing)

InnoDB和其他任何符合ACID标准的数据库引擎一样,在提交事务之前刷新事务的redo日志.InnoDB使用组提交功能将多个刷新请求组合在一起来避免每次只提交一个刷新.通过组提交,InnoDB会对日志文件发出一次写入操作来为同时提交的多个用户事务执行提交操作,从而显着提高吞吐量.

14.6.7 undo日志

undo日志是与单个读写事务关联的undo日志记录的集合.undo日志包含了如何撤销最近由事务引起的聚簇索引记录变化的信息.如果另一个事务需要将原始数据视为一致读取操作的一部分,没有修改的数据可以从undo日志记录中恢复.undo日志存在于undo日志段中,包含在回滚段之内.回滚段位于系统表空间,undo表空间和临时表空间中.

临时表空间中的undo日志用来修改用户定义的临时表中的数据的事务.这些undo日志不会被redo日志记录,因为它们不是崩溃恢复必要的日志.它们只用来在服务器运行时的回滚.这种类型的undo日志的好处是避免了redo日志执行需要的I/O.

InnoDB支持最大128个回滚段,其中的32个用于临时表空间.剩余的96个回滚段用于正常表的事务数据修改. innodb_rollback_segments变量定义InnoDB使用的回滚段的数量.

回滚段中的撤消槽数根据InnoDB页面大小而不同.

事务最多分配4个undo日志，每个日志对应于以下每种操作类型:

  1. 用户定义表(user-defined tables)上的INSERT操作.
  2. 用户定义表(user-defined tables)上的UPDATE和DELETE操作.
  3. 用户定义临时表上的INSERT操作.
  4. 用户定义临时表上的UPDATE和DELETE操作.

根据需要分配undo日志.例如,对常规表和临时表执行INSERT,UPDATE和DELETE操作的事务需要完全分配四个撤消日志.只对常规表执行INSERT操作的事务需要一个undo日志.

在常规表上执行的事务操作从分配的系统表空间或undo表空间回滚段中分配undo日志.在临时表上执行的事务操作从分配的临时表空间中分配undo日志.

分配给事务的undo日志在事务持续时间事务相关联.例如,分配给常规表上INSERT操作的事务的undo日志用于该事务执行的常规表上的所有INSERT操作.

鉴于上述因素,可以使用以下公式来估计InnoDB能够支持的并发读写事务的数量.

  • 如果每个事务都执行INSERT或UPDATE或DELETE操作,InnoDB能够支持的并发读写事务的数量是: 
    1
    (innodb_page_size / 16) * (innodb_rollback_segments - 32)
  • 如果每个事务分别执行INSERT和UPDATE或DELETE操作,则InnoDB能够支持的并发读写事务的数量是: 
    1
    (innodb_page_size / 16 / 2) * (innodb_rollback_segments - 32)
  • 如果每个事务对临时表执行INSERT操作,则InnoDB能够支持的并发读写事务的数量是: 
    1
    如果每个事务对临时表执行INSERT操作，则InnoDB能够支持的并发读写事务的数量是
  • 如果每个事务对临时表执行INSERT和UPDATE或DELETE操作,则InnoDB能够支持的并发读写事务的数量是: 
    1
    (innodb_page_size / 16 / 2) * 32

发表于 更新于 分类于

14.6.4 InnoDB数据字典(InnoDB Data Dictionary)

InnoDB数据字典由内部系统表构成,包含用于跟踪对象如表,索引和表的列的元数据.元数据实际位于InnoDB系统表空间内.由于历史原因,数据字典元数据在某种程度上与InnoDB表元数据文件(.frm文件)中存储的信息重叠.

14.6.5 双写缓冲区(Doublewrite Buffer)

双写缓冲区是位于系统表空间内的一块存储区域,InnoDB在页面被写到数据文件的正确位置之前会写入从InnoDB缓冲池中刷新的页面.只有在将页面刷新并写入双写缓冲区之后,InnoDB才将页面写到正确的位置上.如果在页面写入的过程中发生操作系统,存储子系统或mysqld进程崩溃,InnoDB可以在崩溃恢复之后从双写缓冲区中找到(之前写页面)的一个好的拷贝.

虽然数据总是要写两次,但是双写缓冲区不需要像两次写一样多的I/O负载或I/O操作.数据作为一个大的顺序块写入双写缓冲区,使用操作系统的单个fsync()调用.

在大多数情况下,默认启用双写缓冲区.设置 innodb_doublewrite=0来禁用双写缓冲区.

如果系统表空间(.ibd文件)位于Fusion-io设备上支持自动写入,双写缓冲区会自动禁用并且Fusion-io自动写会应用于所有的数据文件.因为双写缓冲区设置是全局的,所以对于没有存储在Fusion-io硬件设备上的数据文件也会禁用双写缓冲区.此功能仅在Fusion-io硬件上受支持,仅适用于Linux上的Fusion-io NVMFS.要充分利用此功能,建议设置innodb_flush_method为O_DIRECT.

0%