为什么在高并发的场景下，需要将 MySQL 的事务隔离级别设为读已提交？

2024 4 23 02:22 AM 253次查看

分类：数据库 标签：性能

为缩短篇幅，本文假定读者已知晓 transaction isolation level（事务隔离级别）的基础知识。

MySQL 的默认事务隔离级别是 repeatable read（可重复读），而在都市传说中，各个互联网大厂都会将其改为 read committed（读已提交），这是为什么呢？

从字面上理解，可重复读满足了一个事务在读取某行后，如果另一个事务修改了该行，再次读取它时，能保持第一次读到的值。可是，这又有什么意义呢？谁会在一个事务里多次读取同一行呢？
其实它的实现是这样：当事务开始后，从它第一次读取数据时，就创建了一个快照，之后都是对这个快照进行查询，直到这个事务结束。也就是说，可重复读的主要作用是让事务只访问这个事务和它之前已有的数据，而不是字面上的读同一行不会变。
而读已提交只需要在每次读取时创建一个快照，读取完这个快照就用不到了。
由此可见，可重复读需要维护一个较长的快照，这自然要消耗更多的资源。

不过现实中我们不会这样简单地使用事务。一个正常的事务如果要基于读取到的数据来修改，会使用 SELECT ... FOR UPDATE 的形式来加锁。
如果这里可以利用唯一索引的话，MySQL 会对唯一索引中满足条件的行添加行锁，否则需要加 gap lock 和 next-key lock，这两把锁会增加被锁定的范围。例如表 test 有一个被索引的列 a，有一行 a 为 100 的数据。当执行 SELECT * FROM test WHERE a = 1 FOR UPDATE 后，其他事务无法插入任何 a < 100 的数据，因为被这两把锁给锁住了。
而读已提交则不会添加这两种锁，并且当需要锁住的行不存在时，并不会对其加锁，而是允许其他事务插入。
由此可见，可重复读可能锁住了更多的数据，更容易造成死锁。

举个常见的例子，编写爬虫的时候，我们可能把任务的元数据存储在 task 表，任务的结果存储在 result 表，它们之间以主键 id 关联。
每个 worker 根据传入的 task_id 从 task 表中获取相应的数据，然后对 result 表中的对应行加写锁，再写入结果。这在没有并发的情况下是正常的。
然而如果有多个 workers 的话，假设 worker1 获取到了 task10， worker2 获取到了 task11，result 表已有 id 为 1 ~ 5 的数据。在可重复读的事务隔离级别下，worker2 执行的 SELECT * FROM result WHERE id = 11 FOR UPDATE 会对 id 为 6 ~ 10 的行添加 gap lock，而 worker1 需要对 id 为 10 的行添加行锁，这就形成了死锁。而读已提交的事务隔离级别并不会添加 gap lock，所以不会出现这种死锁。

下面再举个例子说明一下二者的性能差异。
表结构如下：
CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uni` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni` (`uni`)
)
测试代码：
import threading
import time

import MySQLdb


def insert():
    db = MySQLdb.connect(user='root', database='test')
    c = db.cursor()

    for i in range(1000):
        while True:
            try:
                c.execute("SELECT MAX(uni) FROM test")
                age = c.fetchone()[0] + 1
                c.execute("SELECT * FROM test WHERE uni = %s FOR UPDATE", (uni,))
                c.execute('INSERT INTO test(uni) VALUES (%s)', (uni,))
                c.execute('COMMIT')
            except Exception:
                time.sleep(0.001)
                c.execute('ROLLBACK')
            else:
                break

    c.close()
    db.close()


ts = []
for i in range(10):
    t = threading.Thread(target=insert)
    ts.append(t)

start = time.time()
for t in ts:
    t.start()

for t in ts:
    t.join()
print(time.time() - start)

这段代码的作用是起 10 个线程，分别插入 1000 条数据。在插入时，先尝试获取当前最大的 uni 值，然后将 max(uni) + 1 设为新插入的值，如果失败则重试。
在我的电脑上，如果用读已提交，大概 2.9 秒完成，而用可重复读则需要约 25 秒。
并且你可以看到我在出现异常时还加了句 time.sleep(0.001)，否则在可重复读的场景下可能永远都不会结束。

这是什么原因呢？
把异常日志打出来就明白了，读已提交是在执行 c.execute('INSERT INTO test(uni) VALUES (%s)', (uni,)) 时抛出了 MySQLdb.IntegrityError: (1062, "Duplicate entry '1' for key 'test.uni'")，可重复读是在执行 c.execute("SELECT * FROM test WHERE uni = %s FOR UPDATE") 时抛出了 MySQLdb.OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')。
前者在 SELECT ... FOR UPDATE 时并不会禁止其他事务插入，因此总有一个并发的事务会成功。后者则会禁止其他事务插入，然后并发的事务全部在等这个锁，MySQL 发现死锁后保留一个事务，将其他事务全结束；此时如果线程都短暂进行 sleep，则这个被保留的事务有可能成功，否则这些线程可能又和这个事务继续竞争锁，导致无限死锁。

综上所述，在高并发的场景下，可重复读很容易造成死锁，极大影响性能，因此建议设置为读已提交。但是这会导致无法锁住还不存在的行，因此要么就捕捉 IntegrityError 异常后重试，并容忍中间可能空缺的主键，要么就利用 Redis 等方案来加锁。

0条评论 你不来一发么↓

    想说点什么呢？