mysql事务&&锁机制

一、mysql事务：

1、事务的几个基本概念：

• 事务是一组SQL语句的执行，要么全部成功，要么全部失败，不能出现部分成功，部分失败的结果。保证事务执行的原子操作。

• 事务的所有SQL语句全部执行成功，才能提交（commit）事务，把结果写回磁盘上。

• 事务执行过程中，有的SQL出现错误，那么事务必须要回滚（rollback）到最初的状态。【 回滚依靠的是redo log【重做日志】和undo log【回滚日志】】

注意：MyISAM是不支持事务的，InnoDB最大的特点就是：支持事务，支持行锁。

2、事务特性（ACID）:

• 事务的原子性（Atomic）：[事务执行本身] 事务是一个不可分割的整体，事务必须具有原子特性，及当数据修改时，要么全执行，要么全不执行，即不允许事务部分的完成。

• 事务的一致性（Consistency）：[事务对数据具有一致性] 一个事务执行之前和执行之后，数据库数据必须保持一致性状态。数据库的一致性状态必须由用户来负责，由并发控制机制实现。就拿网上购物来说，你只有让商品出库，又让商品进入顾客的购物车才能构成一个完整的事务。

• 事务的隔离性（Isolation）：[并发执行能力的体现，看隔离的级别] 当两个或者多个事务并发执行时，为了保证数据的安全性，将一个事务内部的操作与其它事务的操作隔离起来，不被其它正在执行的事务所看到，使得并发执行的各个事务之间不能互相影响。

• 事务的持久性（Durability）： 事务完成(commit)以后，DBMS保证它对数据库中的数据的修改是永久性的，即使数据库因为故障出错，也应该能够恢复数据（redo log保证数据库的永久性，mysql最重要的是日志，不是数据）

  注意：

  ACD是由redo log和undo log机制来保证的；I是由mysql的锁机制来保证的。

3、mysql四种隔离级别及并发时可能出现的问题：

查询事务的隔离级别：select @@tx_isolation;

设置事务的隔离级别：SET TX_ISOLATION=‘REPEATABLE-READ’;

查看MySQL是否自动提交事务：SELECT @@AUTOCOMMIT;

设置事务提交方式为手动提交方式：set autocommit=0;

四种隔离级别：

• READ-UNCOMMITTED：未提交读，会出现脏读（一个事务读取了另一个事务未提交的数据），基本不会遇到；

• READ-COMMITTED：已提交读，会出现不可重复读（一个事务的操作导致另一个事务前后两次读取到不同的数据）、幻读（一个事务的操作导致另一个事务前后两次查询的结果数据量不同），【Oracle默认】；

• REPEATABLE_READ：可重复读，可以保证能够再次读取相同的数据而不会失败，但幻读仍然会出现。【mysql默认】；

• SERIALIZABLE：串行化。单线程操作，可防止脏读，不可重复读和幻读，但并发能力差

4、锁机制：

1）表锁&行锁

• 表级锁：对整张表加锁。开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率高，并发度低。

• 行级锁：对某行记录加锁。开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高。

2）读锁&写锁

• 写锁，又称为X 锁，排它锁（Exclusive）；

• 读锁，又称为S 锁，共享锁（Shared）；

注意：SS可以兼容的，XS、SX、XX之间是互斥的

区分条件不同，有行级读/写锁、表级读写/锁

强制获取读锁、写锁：

select * from t_student where sno=8 lock in share mode;

select * from t_student where sno=8 for update;

【重要结论】：

InnoDB行锁是通过给索引上的索引项加锁来实现的，而不是给表的行记录加锁实现的，这就意味着只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。 （原理是在二级索引树上搜索了主键索引

InnoDB默认情况下是使用表锁，一旦使用索引项，就会使用行锁。

3）InnoDB 行锁的 3 种算法：

InnoDB存储引擎有3种行锁的算法，其分别是：

• Record Lock：单个行记录上的锁；

• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

• Next-Key Lock∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

  【注意】

  Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。 ​ Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。 ​ 在InnoDB 默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking这种锁定算法。但是对于“唯一索引” ，比如主键的索引，next key lock会降级成行锁Record Lock ，即仅锁住索引本身，而不会锁住一个区间。

例如：

 表z的列b是辅助索引，若在会话A中执行下面的SQL语句：SELECT*FROM z WHERE b=3 FOR UPDATE 很明显，这时SQL语句通过索引列b进行查询，该列不是唯一属性，因此其使用传统的Next-Key Locking技术加 锁，并且由于有两个索引，其需要分别进行锁定。对于聚集索引（primay-key a），其仅对列a等于5的索引加 上Record Lock。而对于辅助索引b，其加上的是Next-Key Lock，锁定的范围是(1，3)。特别需要注意的是， InnoDB存储引擎还会对辅助索引下一个键值加上gap lock，即还有一个辅助索引范围为(3，6)的锁。

因此，若在新会话B中运行下面的SQL语句，都会被阻塞：

 第一个SQL语句不能执行，因为在会话A中执行的SQL语句已经对聚集索引中列a=5的值加上X锁，因 此执行会被阻塞。第二个SQL语句，主键插入4，没有问题，但是插入的辅助索引值2在锁定的范围(1， 3)中，因此执行同样会被阻塞。第三个SQL语句，插入的主键6没有被锁定，5也不在范围(1，3)之间。 但插入的值5在另一个锁定的范围(3，6)中，故同样需要等待。

4）、Next-Key Lock有什么作用？

mysql在默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking机制来避免Phantom Problem（幻读问题，也称不可重复读）。Phantom Problem是指在同一事务下，连续执行两次同 样的SQL语句可能导致不同的结果。（在 READ COMMITTED 事务隔离级别下会出现)

例如：

 在同一事务中，若此时执行语句： SELECT*FROM z WHERE b=3 FOR UPDATE 两次，中间间隔10秒时间执行。可以肯定的说，我们会得到第三行数据的结果，即 （5,3）。此时我们知道，会有一个 Record Lock锁定主键 5，还会有一个gap lock锁定 （1,3）和 （3,6）。 假设：我们分析下，若此时没有gap lock（1,3）和 （3,6），如果只有Record Lock锁定主键 5 会不会造成幻读。 分析：我们在第一次 select 完成之后，第二次select 之前，插入一天数据: INSERT INTO z SELECT 20,3; 这条数据是可以插入成功的，因为我们只有一个record lock 锁定了 主键5，对于新插入的数据主键为 20 ，可以插入，且 无重复。 插入完成后，第二次 select 得到了两个值，（5,3）（20,3）。这就造成了同一事物中，第一次读取和第二次读取的结果不一样，出现幻读。 如果有gap lock，插入就会被阻塞，不会出现幻读。

5、MVCC(一致性非锁定读)-多版本并发控制

“串行化”隔离级别，虽然不会出错，但是效率实在太低了。 避免使用！！

“可重复读”，虽然会出现幻读，但是也能忍受。但为了实现可重复读， 需要在事务中对读操作加锁，并且得持续到整个事务结束，效率也一般，可选择使用。

隔离级别在可重复读和读已提交情况下，有没有可能在在读的时候不用加锁，也能实现可重复读？

MVCC实现了保证可重复读并在读数据的时候不需要加锁操作但是在写数据的时候，MySQL还是要加锁的，防止写-写冲突。读写不互相等待，能极大地提高数据库的并发能力啊。

MVCC使得数据库读不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。 借助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本，保证了ACID中的I特性（隔离性)。

MVCC只在REPEATABLE READ和READ COMMITIED两个隔离级别下工作。其他两个隔离级别都和 MVCC不兼容 ，因为READ UNCOMMITIED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

6、InnoDB的MVCC实现逻辑：

6.1 事务id&回滚指针：

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的事务ID（DB_TRX_ID），一个保存了行的回滚指针（DB_ROLL_PT）。每开始一个新的事务，都会自动递增产 生一个新的事务id。事务开始时刻的会把事务id放到当前事务影响的行事务id中，当查询时需要用当前事务id和每行记录的事务id进行比较。

例如：

在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。

SELECT

InnoDB 会根据以下两个条件检查每行记录：

1. InnoDB只查找版本早于当前事务版本的数据行（也就是，行的事务编号小于或等于当前事务的事务编号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

2. 删除的行要事务ID判断，读取到事务开始之前状态的版本，只有符合上述两个条件的记录，才能返回作为查询结果。

INSERT

InnoDB为新插入的每一行保存当前事务编号作为行版本号。

DELETE

InnoDB为删除的每一行保存当前事务编号作为行删除标识。

UPDATE

InnoDB为插入一行新记录，保存当前事务编号作为行版本号，同时保存当前事务编号到原来的行作为行删除标识。

保存这两个额外事务编号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

6.2、undo log 与 read view ：

MVCC 在mysql 底层中的实现依赖的是 undo log 与 read view 。

1）undo log

根据行为的不同，undo log分为两种： insert undo log 和 update undo log

• insert undo log：

insert 操作中产生的undo log，因为insert操作记录只对当前事务本身课件，对于其他事务此记录不可见，所以 insert undo log 可以在事务提交后直接删除而不需要进行purge操作。

purge的主要任务是将数据库中已经 mark del 的数据删除，另外也会批量回收undo pages

数据库 Insert时的数据初始状态：

 

• update undo log：

  update 或 delete 操作中产生的 undo log。 因为会对已经存在的记录产生影响，为了提供 MVCC机制，因此update undo log 不能在事务提交时就进行删除，而是将事务提交时放到入 history list 上，等待 purge 线程进行最后的删除操作。

  数据第一次被修改时：

 

当另一个事务第二次修改当前数据：

 

为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式。

2）ReadView：

对于 RU(READ UNCOMMITTED) 隔离级别下，所有事务直接读取数据库的最新值即可，和 SERIALIZABLE 隔离级别，所有请求都会加锁，同步执行。所以这对这两种情况下是不需要使用到 Read View 的版本控制。

对于 RC(READ COMMITTED) 和 RR(REPEATABLE READ) 隔离级别的实现就是通过上面的版本控制来完成。两种隔离界别下的核心处理逻辑就是判断所有版本中哪个版本是当前事务可见的处理。针对这个问题InnoDB在设计上增加了ReadView的设计，ReadView中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids。

对于查询时的版本链数据是否看见的判断逻辑：

如果被访问版本的 trx_id 属性值小于 m_ids 列表中最小的事务id，表明生成该版本的事务在生成 ReadView 前已经提交，所以该版本可以被当前事务访问。

如果被访问版本的 trx_id 属性值大于 m_ids 列表中最大的事务id，表明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。

如果被访问版本的 trx_id 属性值在 m_ids 列表中最大的事务id和最小事务id之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

举个例子：

1）READ COMMITTED 隔离级别下的ReadView：

每次读取数据前都生成一个ReadView (m_ids列表)

时间	Transaction 777	Transaction 888	Trasaction 999
T1	begin;
T2		begin;	begin；
T3	UPDATE user SET name = ‘CR7’ WHERE id = 1;
T4
T5	UPDATE user SET name = ‘Messi’ WHERE id = 1;		SELECT * FROM user where id = 1;
T6	commit;
T7		UPDATE user SET name = ‘Neymar’ WHERE id = 1;
T8			SELECT * FROM user where id = 1;
T9		UPDATE user SET name = ‘Dybala’ WHERE id = 1;
T10		commit;
T11			SELECT * FROM user where id = 1;

【分析下上面的情况下的ReadView】

时间点 T5 情况下的 SELECT 语句：

当前时间点的版本链：

 

【此时 SELECT 语句执行，当前数据的版本链如上，因为当前的事务777，和事务888 都未提交，所以此时的活跃事务的ReadView的列表情况 m_ids：[777, 888] ，因此查询语句会根据当前版本链中小于 m_ids 中的最大的版本数据，即查询到的是 Mbappe。】

时间点 T8 情况下的 SELECT 语句：

当前时间的版本链情况：

 

此时 SELECT 语句执行，当前数据的版本链如上，因为当前的事务777已经提交，和事务888 未提交，所以此时的活跃事务的ReadView的列表情况 m_ids：[888] ，因此查询语句会根据当前版本链中小于 m_ids 中的最大的版本数据，即查询到的是 Messi。

时间点 T11 情况下的 SELECT 语句：

当前时间点的版本链信息：

 

此时 SELECT 语句执行，当前数据的版本链如上，因为当前的事务777和事务888 都已经提交，所以此时的活跃事务的ReadView的列表为空 ，因此查询语句会直接查询当前数据库最新数据，即查询到的是 Dybala。

总结： 使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的 ReadView。

【REPEATABLE READ 隔离级别下的ReadView：】

在事务开始后第一次读取数据时生成一个ReadView（m_ids列表）

时间点 T5 情况下的 SELECT 语句：

当前版本链

 

再当前执行select语句时生成一个ReadView，此时 m_ids 内容是：[777,888]，所以但前根据ReadView可见版本查询到的数据为 Mbappe。

【时间点 T8 情况下的 SELECT 语句】：

当前的版本链：

 

此时在当前的 Transaction 999 的事务里。由于T5的时间点已经生成了ReadView，所以再当前的事务中只会生成一次ReadView，所以此时依然沿用T5时的m_ids：[777,888]，所以此时查询数据依然是 Mbappe。

【时间点 T11 情况下的 SELECT 语句：】

当前的版本链：

 

此时情况跟T8完全一样。由于T5的时间点已经生成了ReadView，所以再当前的事务中只会生成一次ReadView，所以此时依然沿用T5时的m_ids：[777，888]，所以此时查询数据依然是 Mbappe。

MVCC总结： 所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD 、REPEATABLE READ 这两种隔离级别的事务在执行普通的 SEELCT 操作时访问记录的版本链的过程，这样子可以使不同事务的 读-写 、 写-读 操作并发执行，从而提升系统性能。

在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔离级别的的一个非常大的区别就是它们生成 ReadView 的时机不同。在 READ COMMITTED 中每次查询都会生成一个实时的 ReadView，做到保证每次提交后的数据是处于当前的可见状态。而 REPEATABLE READ 中，在当前事务第一次查询时生成当前的 ReadView，并且当前的 ReadView 会一直沿用到当前事务提交，以此来保证可重复读（REPEATABLE READ）。

7、外键和锁：

7.1 外键的使用：

• 键主要用于引用完整性的约束检查

• 在InnoDB中，对于一个外键列，如果没有显示地对这个列加索引，InnoDB会自动对其加一个索引，因为这样可以避免加锁——这比Oracle数据库做得好，Oracle数据库不会自动添加索引，用户必须手动添加，这也导致了Oracle可能产生死锁。

7.2 外键和锁的工作原理：

• 对于外键值的插入或更新，首先需要查询父表中的记录，即SELECT父表。

• 但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是SELECT...LOCK IN SHARE MODE方式，即主动对父表加一个S锁。如果这时父表上已经有X锁，子表上的操作会被阻塞。 例如：

  # 创建一个父表parent
  create table parent(
      id int primary key
  );
   
  insert into parent select 1;
  insert into parent select 2;
  insert into parent select 3;
  ​
  #创建一个子表child。其中子表的第二个字段为外键，指向于parent表的id字段
  ​
  create table child(
      child_id int primary key,
      parent_id int not null,
      foreign key(parent_id) references parent(id)
  );
   
  insert into child select 1,1;

• 现在开启会话A，在会话A中删除parent表中id为3的记录（此时在id为3的记录上加了一个X锁），但是事务不提交：

• 此时开启一个会话B，想在会话B中向child表中插入一条语句（加S锁），由于第二个字段是外键，那么这条语句会被阻塞（因为parent中id为3的字段已经加了X锁）。

• 当我们的会话A提交之后，会话B的插入语句也会失败，因为会话A已经将parent表中id为3的字段删除了，因此会话B插入会失败，这样保证了父子表之间的数据完整性与一致性