(二)关于mysql事务的几件小事

零.MyISAM和InnoDB关于锁的区别

①MyISAM默认用的是表级锁，不支持行级锁。

②InnoDB默认用的是行级锁，也支持表级锁。

③共享锁和排它锁的兼容性

X	排它锁	共享锁
排它锁	冲突	冲突
共享锁	冲突	兼容

④使用场景 MyISAM A: 频繁执行全部count语句。 B: 对数据进行增删改的频率不高，查询非常频繁。 C:不需要支持事务。 InnoDB A:数据增删改查相当频繁。 B: 要求支持事务。

一.锁的分类

按所粒度分： 表级锁：整个表加锁 行级锁：对行数据加锁 页级锁： 介入表级个页级之间的锁，锁定位于一个存储快的相邻的几行数据。

按锁级别分： 共享锁：针对同一份数据，多个读操作可以同时进行而不会相互影响。 排它锁： 当前写操作没有完成前，它会阻止其他写锁和读锁。

按加锁方式分 自动锁：像意向锁、MyISAM的增删改查时加的锁就是自动锁，这是mysql自动加的锁。 显式锁：像select for update，lock这种我们现实加的锁就是显式锁。

按操作方式分 DML锁：对数据进行操作时加的锁。 DDL锁：对表结构进行变更加的锁。

按使用方式分 乐观锁：认为数据不会造成冲突，在提交时才进行判断，不使用数据库的锁机制，而是使用版本号或者时间戳实现。 悲观锁：对外界的影响处于保守状态，在处理中将数据锁定，往往依靠数据库提供的锁机制。全程使用排它锁锁定，先获取锁在执行。

三.数据库事务四大特性

1.原子性 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。 2.一致性 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。 3.隔离性 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。 4.持久性 一个事务被提交之后，它对数据库中数据的改变是持久性的，即使数据库发生故障也不应该对其有任何影响。

三.并发事务带来的问题

1.脏读 当一个事务正在访问数据并且对数据进行修改，而这种修改还没有提交到数据库中，这时另一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这时数据是"脏数据"，依据"脏数据"所做的操作可能是不正确的。 2.丢失修改 指在一个事务读取一个数据时，另外一个事务也访问了这个数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就会被丢失，称为丢失数据。 3.不可重复读 指在一个事务内多次读同一个数据。在这个事务还没有结束时，另一个事务也访问了该数据，那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，称为不可重复读。 4.幻读 幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

四.数据库事务隔离机制

1.READ-UNCOMMITTED(读取未提交) 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读、不可重复读。 2.READ-COMMITTED(读取已提交) 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读和不可重复读任有可能发生。 3.REPEATABLE-READ(可重复读) 对同一个字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复做，但幻读仍有可能发生。 4.SERIALIZABLE(可串行读) 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样的事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL InnoDB存储引擎的默认支持的隔离级别是REPEATABLE-READ(可重复读) 。可以通过@@tx_isolation命令查看。

这里需要注意的是：与SQL标准不同的地方在于InnoDB存储引擎在REPEATABLE-READ(可重复读) 事务隔离级别下使用的是Next-key Lock算法，因此可以避免幻读的产生。所以InnoDB存储引擎的默认支持的隔离级别是REPEATABLE-READ(可重复读) 已经可以完全保证事务的隔离性要求，即达到了SQL标准的SERIALIZABLE(可串行化)隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED；但是InnoDB存储引擎默认使用REPEATABLE-READ 并不会有任何性能损失。 InnoDB存储引擎在分布式事务的情况下一般会用到SERIALIZABLE隔离级别。

五.当前读与快照读

1.当前读

select... lcok in share mode,select.....for update,update,delete,insert等操作都是当前读。

2.快照读

不加锁的非阻塞读，select操作就是快照读，基于多版本操作。 快照读的实现： 1.依赖于数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段。 DB_TRX_ID:标志了最近一次操作这行数据的id。 DB_ROLL_PTR：回滚指针，直写入回滚段的rollback segment的undo日志记录。 DB_ROW_ID：表示行号，包含一个随着新行加入单调自增的记录(隐藏主键)。 2.undo日志 当我们对记录进行了变更操作时，就会产生undo日志，undo中记录的是老版数据，当一个旧的事务需要读取记录时，顺着undo链就可以读取到满足需要的老版本数据。分为insert undolog和update undolog，insert undolog只在事务回滚时被需要，并且在事务提交之后就可以被丢弃；update undolog会在对数据进行更新和删除时产生，不仅在事务混滚时需要，而且在进行快照读的时候也需要，因此不能随便删除。

image.png

3.read view 主要用于做可见性判断，当我去执行快照读select的时候，会针对我们需要读的数据去创建一个read view，决定当前能够读取到的数据是哪个版本；主要基于将数据的DB_TRX_ID与当前活跃的事务的ID进行对比，如果等于就根据undolog去取上层的数据，知道取到比他小的数据。

六.RR如何避免幻读

1.表象：快照读(非阻塞读)--伪MVCC 2.内在，next-key锁(行锁+gap锁) GAP锁 Gap锁锁定一个范围，防止出现幻读。 1.对主键索引或者唯一锁会有Gap锁？ 如果where条件全部命中，则不会用Gap锁，只会加行锁。 2.如果where条件全部不命中，则会用Gap锁。 3.如果where条件部分命中，则会用Gap锁。 4.Gap锁会用在非唯一索引或者不走索引的当前读中。 非唯一索引情况：

非唯一索引.png

如上图，操作数据9，会锁住(6,9],(9,11]这两个区间即(6,11]的区间会被加上Gap锁，不被允许操作，这样就保证了防止幻读的发生。gap锁还要和主键值搭配才能精确判断，比如(6,11]这个区间被锁住，但是6对应的主键是c，如果插入(a,6)这样的数据，是可以插入的，但是(d,6)这样的数据就是无法插入的。

不走索引： 不走索引会加表锁，也就是加全部的Gap锁，

image.png

七.锁的建议优化

1.尽可能让所有数据检索都用过索引来完成，避免无索引行锁升级为表锁。 2.合理设计索引，尽量缩小锁的范围。 3.尽可能减少检索条件，避免间隙锁(Gap锁)。 4.尽量控制事务大小，减少锁定资源量和时间长度。 5.尽可能使用低级别的事务隔离。