mysql5.7事务隔离级别

1.事务的4大特征(ACID)

ACID，是指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）

• Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read
  uncommitted）、读提交（read committed）、可重复读（repeatable
  read）和串行化（Serializable）。
• Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

2.MYSQL INNODB 4种隔离级别

事务隔离是数据库处理的基础之一。隔离是首字母缩写ACID中的I；隔离级别是在多个事务同时进行更改并同时执行查询时，微调结果的性能和可靠性、一致性和再现性之间的平衡的设置。

• READ UNCOMMITTED(未提交读)

  选择语句以非锁定的方式执行，但可能会使用行的早期版本。因此，使用这种隔离级别，这样的读取并不一致。这也被称为一个dirty
  read.(脏读)。否则，此隔离级别会像已提交的READ一样工作

  例如:
  创建表:

  CREATE TABLE `my_test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
  
  INSERT INTO `my_test` (`name`) VALUES ('1');
  

  设置sesion隔离级别为READ UNCOMMITTED

  set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  

  开启事务T1修改id=1的name=‘2’

  start TRANSACTION
  

  select * from my_test where id =1;
  

  

  update my_test set `name` = '2' where id = 1;
  

  开启事务T2查询id=1的name值

   ```sql
   start TRANSACTION
   select * from my_test where id =1;
   ```
  

  
  t2可以查询到t1未提交事务的值,如果t1回滚事务，t2读到数据就是脏读

• READ COMMITTED(已提交读)
  每个一致的读取，即使是在同一事务中，都会设置并读取自己的新快照。 对于锁定读取(SELECT
  with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE
  statements，InnoDB只锁定索引记录，而不是它们之前的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查

  由于间隙锁定已被禁用，因此可能会出现虚拟行问题，因为其他会话可以在间隙中插入新的行。以获取有关幻像行的信息

  READ COMMITTED隔离级别只支持基于行的二进制日志记录。如果使用 binlog_format=MIXED，服务器会自动使用基于行的日志记录

  使用已提交的READ还具有其他效果：

  • 对于更新或删除语句，InnoDB只对更新或删除的行的锁。在MySQL评估了where条件后，将释放不匹配行的记录锁。这大大降低了死锁发生的概率，但它们仍然有可能发生。
  • 对于更新语句，如果一行已经被锁定，InnoDB将执行“半一致”读取，将最新提交的版本返回给MySQL，以便MySQL可以确定该行是否匹配更新的where条件。如果该行匹配（必须更新），MySQL将再次读取该行，这次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
  set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
  START TRANSACTION;
  UPDATE t SET b = 5 WHERE b = 3;
  

  还假设第二个会话通过在第一个会话之后执行此语句来执行更新：

  # Session B
  UPDATE t SET b = 4 WHERE b = 2;
  

  当InnoDB执行每次更新时，它首先为其读取的每行获取一个独占锁，然后决定是否修改它。如果InnoDB不修改该行，则它将释放该锁。否则，InnoDB将保留该锁，直到事务结束。这将影响以下事务处理。

  当使用默认的(REPEATABLE READ)可重复读隔离级别时，第一个更新对其读取的每行获取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
  

  第二个更新块一旦尝试获取任何锁（因为第一次更新保留了所有行上的锁），并且直到第一次更新提交或回滚后才继续进行：

  x-lock(1,2); block and wait for first UPDATE to commit or roll back
  

  如果使用（READ COMMITTED）已提交读，第一个更新将获取其读取的每行的x锁，并释放未修改的行：

  x-lock(1,2); unlock(1,2)
  x-lock(2,3); update(2,3) to (2,5); retain x-lock
  x-lock(3,2); unlock(3,2)
  x-lock(4,3); update(4,3) to (4,5); retain x-lock
  x-lock(5,2); unlock(5,2)
  

  对于第二次更新，InnoDB执行一个“半一致”读取，返回它读取到MySQL的每一行的最新提交版本，以便MySQL可以确定该行是否匹配更新的where条件：

  x-lock(1,2); update(1,2) to (1,4); retain x-lock
  x-lock(2,3); unlock(2,3)
  x-lock(3,2); update(3,2) to (3,4); retain x-lock
  x-lock(4,3); unlock(4,3)
  x-lock(5,2); update(5,2) to (5,4); retain x-lock
  

  但是，如果where条件包含一个索引列，并且InnoDB使用该索引，则在获取和保留记录锁时只考虑该索引列。在下面的示例中，第一个更新获取并保留行where b = 2的锁。当第二个更新块试图获取相同记录上的x个锁时，因为它还使用了在b列上定义的索引。

  CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
  INSERT INTO t VALUES (1,2,3),(2,2,4);
  COMMIT;
  # Session A
  START TRANSACTION;
  UPDATE t SET b = 3 WHERE b = 2 AND c = 3;
  # Session B
  START TRANSACTION;
  UPDATE t SET b = 4 WHERE b = 2 AND c = 4;
  

  使用READ COMMITTED隔离级别的效果与启用已弃用的innodb_locks_unsafe_for_binlog变量相同，但有以下例外：

  • 启用innodb_locks_unsafe_for_binlog是一个全局设置，它会影响所有会话，而隔离级别可以为所有会话全局设置，或者为每个会话单独设置。
  • innodb_locks_unsafe_for_binlog只能在服务器启动时设置，而隔离级别可以在启动时设置或在运行时更改。
    因此，READ COMMITTED提供了比innodb_locks_unsafe_for_binlog更精细、更灵活的控制。

• REPEATABLE READ(可重复读)
  这是InnoDB的默认隔离级别。同一事务中的一致读取读取由第一次读取所建立的快照。这意味着，如果在同一事务中发出几个普通（非锁定）选择语句，则这些选择语句彼此也保持一致,参考mysql
  一致的非锁紧读取

  对于锁定读取（SELECT with FOR UPDATE or LOCK IN SHARE
  MODE）、更新和删除语句，锁定取决于语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。

• SERIALIZABLE（串行化）
  这个级别类似于可重复读取，但是InnoDB隐式地转换了所有的普通的选择语句来选择。如果禁用了autocommit（SELECT …
  LOCK IN SHARE
  MODE）。如果启用了自动提交，则选择为其自己的事务。因此，已知它是只读的，如果执行一致（非锁定）读取，则可以串行化，并且不需要对其他事务进行串行化。（要强制普通选择阻止其他事务已修改选定行，请禁用autocommit.。）