InnoDB事务

2019-12-04 本文已影响0人段一萌

事务

InnoDB事务模型目标：将 多版本数据库 的特性与传统的 两阶段锁定 相结合

事务隔离级别（Transaction Isolation Levels）

隔离是缩写ACID中的I；隔离级别是一种设置，用于在多个事务同时进行更改和执行查询时结果的 可靠性、高效性、一致性 和 可重复性 之间的平衡

REPEATABLE READ（默认级别）
- 同一事务中的一致性读，将读取第一次读取建立的快照；（一致性非锁定读取）
- 对于锁定读取（SELECT带有FOR UPDATE或LOCK IN SHARE MODE）， UPDATE和 DELETE语句，锁定取决于该语句使用的是具有 唯一搜索条件的唯一索引 还是 范围类型搜索条件 。
  - 对于具有 唯一搜索条件的唯一索引 ，InnoDB仅锁定找到的索引记录，而不锁定其前的间隙。
  - 对于其他 搜索条件 ，InnoDB 使用间隙锁或下一键锁定锁定扫描的索引范围，以阻止其他会话插入该范围所覆盖的间隙。
  例：从此表开始
  CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
  INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
  COMMIT;
  
  # Session A
  START TRANSACTION;
  UPDATE t SET b = 5 WHERE b = 3;
  
  # Session B
  UPDATE t SET b = 4 WHERE b = 2;
  
  当使用默认REPEATABLE READ 隔离级别时,第一个UPDATE将在读取的每一行上获取一个x锁，并且不会释放其中的任何一个：
  x-lock(1,2); retain x-lock
  x-lock(2,3); update(2,3) to (2,5); retain x-lock
  x-lock(3,2); retain x-lock
  x-lock(4,3); update(4,3) to (4,5); retain x-lock
  x-lock(5,2); retain x-lock
  
  注：请考虑为什么我们实际应用中强烈要求通过主键更新或删除？
READ COMMITTED
- 同一事务中，每个一致性读都将设置并读取其自己的新快照
READ UNCOMMITTED
- SELECT语句以非锁定方式执行，但是可能会使用行的早期版本。因此，使用此隔离级别，此类读取不一致。这也称为脏读。
SERIALIZABLE
此级别类似于REPEATABLE READ，
- 如果 autocommit禁用，InnoDB将所有普通SELECT 语句隐式转换为SELECT ... LOCK IN SHARE MODE。
- 如果 autocommit启用，则 SELECT是其自身的事务。
因此，它被认为是只读的，如果作为一致（非锁定）读取执行并且不需要阻塞其他事务，则可以序列化。

自动提交、提交与回滚

autocommit 0关闭，1开启（默认）；
开启事务 START TRANSACTION或 BEGIN
关闭或回滚事务 COMMIT或 ROLLBACK

一致性非锁定读取

一致性读意味着InnoDB数据库会在一个时间点使用多版本的快照。
一致性读不会在它访问的表上设置任何锁，因此其他会话可以在对该表执行一致性读的同时自由地修改那些表。

一致性读

一种读取操作，使用快照信息可基于某个时间点显示查询结果，而不管同时运行的其他事务执行的更改如何。如果查询的数据已被另一个事务更改，则将根据撤消日志的内容来重建原始数据。通过强制事务等待其他事务完成，该技术避免了一些可以减少并发性的锁定问题。

一致读取不适用于某些DDL语句：
- 一致读取无法解决问题DROP TABLE，因为MySQL无法使用已删除InnoDB的表并破坏该表。
- 一致读取无法解决问题 ALTER TABLE，因为该语句将创建原始表的临时副本，并在构建临时副本时删除原始表。当您重新发出事务中的一致读取时，新表中的行不可见，因为在获取事务快照时这些行不存在。在这种情况下，事务返回一个错误： ER_TABLE_DEF_CHANGED， “ 表的定义发生了变化，请重试交易 ”。

读取的类型因选择子句（如INSERT INTO ... SELECT，）中的选择而有所不同 UPDATE ... (SELECT)，并且 CREATE TABLE ... SELECT未指定FOR UPDATE或LOCK IN SHARE MODE：
- 默认情况下，InnoDB使用更强的锁定，并且SELECT部分的行为类似于 READ COMMITTED，其中每次一致的读取（即使是在同一事务中）也会设置并读取自己的新快照。
- 要在这种情况下，读一致性，使 innodb_locks_unsafe_for_binlog 选项和事务的隔离级别设置为 READ UNCOMMITTED， READ COMMITTED或 REPEATABLE READ（比其他任何东西 SERIALIZABLE）。在这种情况下，不会对从选定表读取的行设置锁定。

锁定读取

如果查询数据，然后在同一事务中插入或更新相关数据，则常规SELECT 语句不能提供足够的保护。

共享锁，SELECT ... LOCK IN SHARE MODE
排他锁，SELECT ... FOR UPDATE

注：只有在禁用自动提交时（以 START TRANSACTION 开始事务或通过设置 autocommit 为0，才可以进行锁定读取）

除非在子查询中也指定了锁定读取子句，否则外部语句中的锁定读取子句不会锁定嵌套子查询中表的行。例如，以下语句不会锁定t2 表中的行。

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

要锁定t2中的行，请向子查询添加锁定的子语句：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

示例

使用一致性读来查询表 PARENT并验证父行是否存在。可以安全地将子行插入表格 CHILD吗？

不可以，因为其他会话可能会在您SELECT和您之间的时刻删除父行 INSERT，而您却没有意识到。为避免此问题，请执行以下 SELECT使用LOCK IN SHARE MODE：
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

一个表中的整数计数器字段，该字段CHILD_CODES用于为每行数据分配唯一标识符 CHILD。 不要使用一致读取或共享模式读取来读取计数器的当前值 ，因为数据库的两个用户可能会看到该计数器的相同值，并且如果两个事务尝试使用以下方法添加行，则会发生重复键错误。

在这里，LOCK IN SHARE MODE这不是一个好的解决方案，因为如果两个用户同时读取计数器，则其中至少有一个在尝试更新计数器时会陷入死锁状态。
SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

幻影行

当同一查询在不同时间生成不同的行集时，在事务内就会发生所谓的幻像问题

例: 如果一个SELECT执行两次，但是第二次返回的行却不是第一次返回的行，则该行是“ phantom ”行

思考：REPEATABLE READ隔离级别时，哪种一致性读会出现幻读？

示例

假设id该child表的列上有一个索引，并且您想要读取并锁定该表中所有标识符值大于100的行，以期稍后更新所选行中的某些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

死锁

死锁是指由于每个事务都持有对方需要的锁而无法进行其他事务的情况。因为这两个事务都在等待资源变得可用，所以都不会释放它持有的锁。

避免死锁

为了减少死锁的可能性，请 使用事务 而不是LOCK TABLES语句；
保持用于插入或更新数据的 事务足够小 ，以使其长时间不保持打开状态；
当不同的事务更新多个表或大范围的行时，SELECT ... FOR UPDATE在每个事务中 使用相同的操作顺序；
在SELECT ... FOR UPDATE和 UPDATE ... WHERE 语句中 使用的列上创建索引。

注：死锁的可能性不受隔离级别的影响，因为隔离级别更改了读取操作的行为，而死锁则是由于写入操作而发生的