值班问题: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隔离级别,读不加锁就不再成立,所有的读操作,都是当前读。
上一篇:Git版本控制Windows版快速上手


下一篇:SqlServer2008 数据库同步的两种方式(Sql JOB)