Mysql的一个并发问题

问题描述

• 有一个flask的http接口，会读取/更新一条记录，比如一个订单的状态。
  那么必须锁定，不允许多个请求同时进行。
  因为对mysql的锁了解不深，而且可能需要一次锁多个表的多个记录，所以借助外部的锁服务，在进行业务操作之前做一次性锁定。
  发现还是会出现数据混乱。

查原因

• 外部的锁服务是没问题的，业务操作确实是严格排队进行的，也就是原子的。
• 继续查，发现mysql读出来的数据概率性是老数据。虽然业务是严格排队进行的，前一个接口修改数据后已commit，后一个接口读mysql可能读出老数据。
• 这个就非常反常了。之前没仔细看mysql的文档，对事务相关的细节不清楚。
• 真正的原因是mysql的事务隔离和一致性读的机制。

事务隔离级别

• https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
  有四种隔离级别。mysql的innodb默认是REPEATABLE READ，我们默认也是用这个。先简单记住这个。

snapshot

• https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_snapshot
  某个时间点的数据快照

consistent read 一致性读

• https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_consistent_read
  有两大类读，一致性读和带锁的读(我不知道有没有其他的说法)。
  在REPEATABLE READ隔离级别下，一般的select默认都是一致性读。
• 一致性读，一定会借助一个snapshot，去读snapshot的数据。
• 在REPEATABLE READ隔离级别下，snapshot的时间点是事务开始后的第一个操作的时间点。
• 比如我起一个事务，随便读一次数据（不一定是后续实际要操作的数据），如果在这个时间点之后别的事务改了一个数据并commit，我再select是读不到最新值的，还是会返回第一次读到的老数据。这个就是问题的现象。
• 除非我commit一下，commit会刷新snapshot的时间点。这时再读就会返回新数据，可能是别的事务的更新，可能是我自己的更新。

解决办法

• 总结一下就是多个请求涌入，在排队之前都操作了数据库(获取用户信息等准备动作)，得到了相似时间点(数据尚未修改)的snapshot。导致后续不论别的请求如何修改数据，自己还是会读到老数据。

• 文档提到可以commit更新snapshot，或者换隔离等级，或者用显式的锁。隔离等级我们就先不折腾了。

• 最简单的解法，在获得外部的业务锁之后，进入业务逻辑之前，我们commit一下mysql，就解决了问题。
  也就是在接口排队成功时commit一下。之后读的数据一定是最新的且中途不会有重入。

for update的测试

• https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

• 外部锁服务和for update其实是差不多的等级。因为对mysql的锁没有很深的理解所以最开始的策略是尽量把锁剥离出来，不显式用mysql的锁。

• 如果用for update实现的话。需要把用到的数据先用for update选一下。也要考虑顺序的问题。select ... for update成功后，一直到commit之前，别的for update/lock in share mode的请求都会卡住。普通select可以执行。

• 一些简单测试

1
2
3
4
5
6
7
8

request.mysqldb_session.query(Test2).filter(Test2.id == 1)
原始sql

request.mysqldb_session.query(Test2).with_for_update().filter(Test2.id == 1)
后面跟 FOR UPDATE

request.mysqldb_session.query(Test2).with_for_update(read = True).filter(Test2.id == 1)
后面跟 LOCK IN SHARE MODE

• 超时情况
  先FOR UPDATE，里面sleep久一点。
  再请求一个 LOCK IN SHARE MODE或FOR UPDATE
  会出Lock wait timeout exceeded; try restarting transaction
  默认50s
  见 https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html
  貌似可以每次开始事务时设置一下innodb_lock_wait_timeout，略繁琐。