sqlalchemy limit_sqlalchemy 多线程下的事务隔离

事件经过

最近，为 saas 平台做商户余额提醒的时候，我查询了所有的商户信息，然后开启一个线程，把这个商户数据发送给这个线程，执行完我所需要的业务之后，对商户里面的数据进行修改，然后执行了 commit，令人奇怪的是，commit 失败了，没有任何的报错。
基本代码如下：

import TbMerchant, db, app
from concurrent.futures import ThreadPoolExecutor

def do_something(merchants):
    for merchant in merchants:
        # do merchant check
        merchant.limit += 1
    db.session.commit()


@app.route("/check_balance")
def start_job():
    merchants = TbMerchant.query.filter_by().all()
    executor = ThreadPoolExecutor(1)
    result = executor(do_something, merchants)
    return {}

排查

首先便想到了先把所有的 sqlalchemy 的所有 sql 打印出来app.config["SQLALCHEMY_ECHO"] = True

再次调用的时候，所有的 sql 就会打印出来，但是，我们发现，查询 merchants 的时候，有对应的 sql, 但是，代码执行到 db.session.commit 的时候，没有对应的 sql。
初步定位是线程传递 merchants 的问题，sqlalchemy 事务应该是不允许跨线程的。

解决方案

把 merchants 的查询放到新的线程里面去。
代码如下

import TbMerchant, db, app
from concurrent.futures import ThreadPoolExecutor


def do_something():
    merchants = TbMerchant.query.filter_by().all()      
    for merchant in merchants:
        # do merchant check
        merchant.limit += 1
    db.session.commit()


@app.route("/check_balance")
def start_job():
    executor = ThreadPoolExecutor(1)
    result = executor(do_something)
    return {}

当然，这只是展示一下解决方案，真实的代码肯定和这个差别极大，但是核心原理不变

深入原理

为什么，sqlalchemy 的事务不能跨线程呢？线程的数据应该共享才是，为了查询这个原因。我查询了文档和源码。
当我们生成一个 Session 对象的时候，这个对象并不是线程安全的，是线程本地(thread local storage)
官网是这样说的：

Users who are familiar with multithreaded programming will note that representing anything as a global variable is usually a bad idea, as it implies that the global object will be accessed by many threads concurrently. The Session object is entirely designed to be used in a non-concurrent fashion, which in terms of multithreading means “only in one thread at a time”. So our above example of scoped_session usage, where the same Session object is maintained across multiple calls, suggests that some process needs to be in place such that multiple calls across many threads don’t actually get a handle to the same session. We call this notion thread local storage, which means, a special object is used that will maintain a distinct object per each application thread. Python provides this via the threading.local() construct. The scoped_session object by default uses this object as storage, so that a single Session is maintained for all who call upon the scoped_session registry, but only within the scope of a single thread. Callers who call upon the registry in a different thread get a Session instance that is local to that other thread.

Contextual/Thread-local Sessions
也就是说，跨线程了，就不在 session 范围内了，也就无法提交了。
这样也避免了多线程、多进程情况下，把其他的数据污染了。