数据库存数据时，逻辑上防重了为啥还会出现重复记录？

在很多异常情况下，比如高并发、网络糟糕的时候，数据库里偶尔会出现重复的记录。

假如现在有一张书籍表，结构类似这样

+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+

在异常情况下，可能会出现下面这样的记录

+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  2 | 人类简史     |
|  3 | 人类简史     |
+----+--------------+

但是，想了想，自己在处理相关数据的时候也加了判重的相关逻辑，比如，新增时当图书 name 相同时，会提示图书重复而返回。

初次遇到这个情况的时候，感觉有点摸不着头脑，后面想了想，还是理清了，其实这和数据库的事务隔离级别有一定关系。

先简单说下数据库事务的 4 个隔离级别，然后重现下上述问题，最后说说解决办法。

1 数据库事务的 4 个隔离级别

1.1 未提交读

顾名思义，当事务隔离级别处于这个设置的时候，不同事务能读取其它事务中未提交的数据。

便于说明，我开了两个客户端（A 以及 B），并设置各自的隔离级别为未提交读。（并没有全局设置）

设置隔离级别命令

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

好了，开始。

Client A

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

mysql> insert into books(name) values('人类简史');
Query OK, 1 row affected (0.01 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  4 | 人类简史     |
+----+--------------+
2 rows in set (0.00 sec)

当 A 中的事务没有关闭的时候，我们去 B 中看下数据

Client B

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  4 | 人类简史     |
+----+--------------+
2 rows in set (0.00 sec)

B 中可以读取 A 未提交的数据，所谓未提交读就是这样。

最后，记得把各个事务提交。

Client A & Client B

mysql> commit;

1.2 提交读

不能事务可以读取其它事务中已经提交的数据。

篇幅问题，这里我就不贴出设置隔离级别的语句，测试某个隔离级别的时候，默认已经设置好该级别。

Client A

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

mysql> insert into books(name) values('人类简史');
Query OK, 1 row affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  5 | 人类简史     |
+----+--------------+
2 rows in set (0.00 sec)

A 没提交，在 B 里面去看下数据

Client B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

和预期一样，A 中未提交的数据在 B 中看不到。

A 中提交事务

Client A

mysql> commit;

在 B 中看下

Client B

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  5 | 人类简史     |
+----+--------------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

B 中能看到 A 中提交的数据。

1.3 可重复读

细心的朋友可能会发现一个问题，那就是在 B 中的同一个事务读同一个表，得到的结果却不一致，开始只有 1 条，后面有 2 条，而如果没有这个问题的话，也就是可重复读了。

我们来验证下

Client A

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

mysql> insert into books(name) values('人类简史');
Query OK, 1 row affected (0.01 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  6 | 人类简史     |
+----+--------------+
2 rows in set (0.00 sec)

Client B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

Client A

mysql> commit
Query OK, 0 rows affected (0.00 sec)

Client B

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

和预期一致。B 中事务没有受到 A 中事务的提交影响，读取的数据和事务刚开始的时候一致，books 中都只有一条数据，这就是可重复读。

当然，B 在自己的事务中做修改，肯定是可见的。

Client B

mysql> insert into books(name) value ('时间简史');
Query OK, 1 row affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
|  8 | 时间简史     |
+----+--------------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

1.4 串行化

这是隔离级别最严格的一级，在该级别中，不同事务中的读写会相互阻塞。

Client A

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

当 A 未提交的时候在 B 中对同一个表进行写

Client B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+
1 row in set (0.00 sec)

mysql> insert into books(name) value ('人类简史');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

由于不同事务中的读写相互阻塞，所以出现了上面超时的情况。

如果 A 中提交事务

Client A

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

那么在 B 中就能正常写了

Client B

mysql> insert into books(name) value ('人类简史');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

同理，在 A 中开启事务并向 books 中插入一条记录后不提交，B 中开启事务并对该表进行读操作，也会超时。当 A 中的事务提交后，B 中对 books 的读操作就没有问题了。

2 重现问题

由于 MySQL 的 Innodb 的默认事务隔离级别为可重复读，也就导致了判重逻辑可能会出现问题，我们来重现一下。

现在，数据库的数据是这样的

+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
+----+--------------+

后端逻辑类似这样的

try:
    book_name = '人类简史'
    book = get_by_name(book_name)
    if book:
        raise Exception(f'图书 {book_name} 已存在')

    # 新增操作
    # 其它操作

    db.session.commit()
    return {'success': True}
except Exception as e:
    db.session.rollback()
    return {'success': False, 'msg': f'新增图书失败 {e}'}

当两个用户输入书名「人类简史」并提交后，同时有两个线程执行这段逻辑，也就相当于上面两个客户端同时开启了事务，我们以这两个客户端来说明问题

Client A

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books where name = '人类简史';
Empty set (0.00 sec)

mysql> insert into books(name) values('人类简史');
Query OK, 1 row affected (0.00 sec)

A 中检测图书不存在，然后插入，但是由于「其它操作」由于网络或者其它原因太费时间，导致事务提交延迟。

这时在 B 中执行类似操作

Client B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from books where name = '人类简史';
Empty set (0.00 sec)

mysql> insert into books(name) values('人类简史');
Query OK, 1 row affected (0.00 sec)

由于事务隔离级别是可重复读的，B 中无法读取 A 中未提交的数据，所以判重逻辑顺利通过，也插入了同一本书。（也就是说隔离级别在提交读及以上都有可能出现这个问题）

最后 A 和 B 都提交后

Client A & Clinet B

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

就出现了重复记录了

+----+--------------+
| id | name         |
+----+--------------+
|  1 | 世界简史     |
| 12 | 人类简史     |
| 13 | 人类简史     |
+----+--------------+

3 怎么解决

3.1 数据库层面

从底层进行限制，对 name 添加唯一索引后，插入重复记录会报错，简单粗暴的解决了这个问题。

3.2 代码层面

加唯一索引能解决，但是总觉得代码不够完整，其实在代码层面也可以解决这个问题。

如果我们在接收请求的时候如果碰到关键参数相同的请求，我们可以直接拒绝，返回类似「操作进行中」的响应，这样也就从源头上解决了这个问题。

实现上面的思路也很简单，借助 redis 的 setnx 即可。

book_name = request.form.get('book_name', '')
if not book_name:
    reutrn json.dumps({'success': False, 'msg': '请填写书名'})

redis_key = f'add_book_{book_name}'
set_res = redis_client.setnx(redis_key, 1)
if not set_res:
    reutrn json.dumps({'success': False, 'msg': '操作进行中'})

add_res = add_book(book_name)  # 添加操作

redis_client.delete(redis_key)
return json.dumps(add_res)

如果类似场景比较多，可以考虑把 redis 的操作封装成一个装饰器，让代码能复用起来，这里不再赘述。

4 小结

由于数据库隔离级别的原因，一些数据就算是逻辑上进行防重了，也有可能出现重复记录。解决这个问题，可以在数据库层面加唯一索引解决，也可以在代码层面进行解决。

原文博客：www.kevinbai.com
关注「小小后端」公众号，更多干货等着你喔！

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 213,099评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,828评论 3赞 387
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,540评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,848评论 1赞 285
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,971评论 6赞 385
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,132评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,193评论 3赞 412
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,934评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,376评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,687评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,846评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,537评论 4赞 335
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,175评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,887评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,134评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,674评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,741评论 2赞 351

推荐阅读更多精彩内容

  • 数据库存数据时，逻辑上防重了为啥还会出现重复记录？
    在很多异常情况下，比如高并发、网络糟糕的时候，数据库里偶尔会出现重复的记录。 假如现在有一张书籍表，结构类似这样 ...
    kevinbai阅读 438评论 0赞 0
  • 2018-06-25
    专业考题类型管理运行工作负责人一般作业考题内容选项A选项B选项C选项D选项E选项F正确答案 变电单选GYSZ本规程...
    小白兔去钓鱼阅读 8,983评论 0赞 13
  • 2017-08-14
    --- layout: post title: "如果有人问你关系型数据库的原理，叫他看这篇文章(转)" date...
    蓝坠星阅读 782评论 0赞 3
  • 用美味给妹纸说情话才是王道
    每个女孩上辈子都是馋嘴的猫， 软萌的爱吃小甜点， 御姐中意手切牛排， 女汉子逛遍夜市摊。 撒尿牛丸里面的汁儿， 爆...
    妮妮是个小妮妮阅读 383评论 0赞 1
  • 掬月
    小时候月亮是童心的向往那里有嫦娥白兔还有砍树的吴刚月亮是我小时最好的朋友哪里也躲不开它温暖的守望 长大后月亮是游子...
    辰州强哥阅读 389评论 1赞 1