值班问题：insert语句插入了两条数据？

上周值班，碰到这样的一个客户问题，表结构简化如下：

CREATE TABLE `aa` (
`c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT '0',
`c4` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

在使用如下的语法时，怎么会插入两条 c2 = 1007 and c3 = 1的记录？

insert into aa(c2,c4) select 1007, 8 from dual where not exists(select * from aa where c2 = 1007 and c3 = 1);

 

毫无疑问，先排查一下用户实例的binlog，发现确实一前一后有两条插入语句。

那会不会是MySQL的bug呢？即使是bug，也得要先复现出来吧。

 

如何复现呢？

session 1:

begin work;

insert into aa(c2,c4) select 1007, 8 from dual where not exists(select * from aa where c2 = 1007 and c3 = 1);

 

这时先不commit

 

session 2:

begin work;

insert into aa(c2,c4) select 1002, 5 from dual where not exists(select * from aa where c2 = 1002 and c3 = 0);

commit

 

session1:

commit

 

ok，有点眉目了。在这种情况下，是可以稳定复现的。用户反映他的业务是自动提交的。如果两个insert来自不同的服务器，第一次执行的时间很长还未提交，第二个就开始执行了。也是可能出现的。后来从SQL审计日志从验证了的确如此。

 

不过客户去复现时，却依然复现不出来。这时想到可能是隔离级别不一样。

果然，我是在Read committed的场景下复现，客户是在Repeatable read的场景下复现。

 

那么，为什么两个隔离级别会有不同的效果呢？

根本原因是RC隔离级别保证对读取到的记录加锁 (记录锁)；

RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (GAP锁)，不存在幻读现象。

具体是如何加锁，可以直接看本文最后一个部分。

 

解决办法：

1. 给（c2, c3）加唯一索引进行约束。（客户的应用场景不支持，因为业务只有c2=? and c3=0的状态只能出现一条，对于c3等于其他状态值，可以允许多条记录）

2. 将这个插入语句设置为 session级别的Repeatable read。这种方式对应用的改动最小。

 

如果设置为全局的Repeatable read隔离级别有什么问题？

1. 锁等待的范围扩大（增加了GAP锁），可能更大概率的出现死锁。

2. 在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。

 

 

一些基本知识点：

两段锁

数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

• 加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
• 解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

事务	加锁/解锁处理
begin；
insert into test .....	加insert对应的锁
update test set...	加update对应的锁
delete from test ....	加delete对应的锁
commit;	事务提交时，同时释放insert、update、delete对应的锁

这种方式虽然无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化（串行化很重要，尤其是在数据恢复和备份的时候）的。

 

事务的隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
• 提交读(Read Committed)：只能读取到已经提交的数据。针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

Read Uncommitted这种级别，数据库一般都不会用，而且任何操作都不会加锁，这里就不讨论了。

 

 

快照读VS当前读

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例： 

• 快照读：简单的select操作，属于快照读，不加锁。
  • select * from table where ?; 
• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
  • select * from table where ? lock in share mode;
  • select * from table where ? for update;
  • insert into table values (…);
  • update table set ? where ?;
  • delete from table where ?;

  所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。 

 

不同隔离级别，以及对于不同的索引情况会如何加锁？

delete from t1 where id = 10; 

• 组合一：id列是主键，RC隔离级别： 只需要将主键上id = 10的记录加上X锁即可
• 组合二：id列是二级唯一索引，RC隔离级别： 需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。
• 组合三：id列是二级非唯一索引，RC隔离级别：对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。
• 组合四：id列上没有索引，RC隔离级别： SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。
  • 聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。这个锁的效果和表锁有什么区别？rc隔离级别下，有区别，记录仍旧可以插入。rr下，功能上无区别。但是innodb不会主动升级表锁。
  • 为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面(innodb)就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。
  • 在5.6后支持了Index Condition Pushdown， 可以在innodb层进行过滤。
• 组合五：id列是主键，RR隔离级别：加锁与组合一[ id主键，Read Committed]一致。
• 组合六：id列是二级唯一索引，RR隔离级别： 与组合二[ id唯一索引，Read Committed]一致。
• 组合七：id列是二级非唯一索引，RR隔离级别：首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
• 组合八：id列上没有索引，RR隔离级别：在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。
• 组合九：Serializable隔离级别： 在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

 

 

转载于:https://www.cnblogs.com/yuyue2014/p/4747018.html