python - 如果在 1/100 秒内请求相同的 Flask api，sqlalchemy 在插入后不会更新

Question

我只想调用flask api在数据库中插入记录，但是当我在1/100秒内的线程中请求它时(日志显示)，即使在我添加锁之后，它也不会在每次插入完成时更新:

测试.py:

@staticmethod
def test_withdraw(app, db, client):
    def withdraw_study(username, studyUID):
        json_data = {}

        json_data["WSIStudyUID"] = studyUID
        json_data['StudyDeliverType'] = 'WithDraw'
        json_data['UserName'] = username

        rsp = client.put(app.config['APPLICATION_ROOT'] + "/rambo/ChangeStudyDeliverType/", json=json_data, headers=admin_headers)
        assert rsp.status_code == 200

    import threading
    t1 = threading.Thread(target=withdraw_study, args=(user1[0],studyUID))
    t2 = threading.Thread(target=withdraw_study, args=(user2[0],studyUID))
    t3 = threading.Thread(target=withdraw_study, args=(user3[0],studyUID))
    t1.start()
    #pdb.set_trace() # if stop here, the db update successfully, t2 and t3 will return failed status as expected.
    t2.start()
    t3.start()

查看.py:

app.sem = threading.Lock()
class ChangeStudyDeliverType (Resource):
    def put(self):
        with app.sem:

            log.debug(f'====================lock status:{app.sem.locked()} {datetime.now()}')

            log.debug(f'start withdraw...UserName:{args["UserName"]}, datetime:{datetime.now()}')
            # check if study is withdrawed
            from sqlalchemy import create_engine
            from cronus.config import SQLALCHEMY_DATABASE_URI
            engine = create_engine(SQLALCHEMY_DATABASE_URI)
            Session = sessionmaker(bind=engine)
            sess = Session()
            p_case = sess.query(MyCase).filter( 
                MyCase.WSIStudyUID == study.WSIStudyUID, 
                MyCase.CaseStatus == 'WithDrawed').first()
            if p_case:
                errStr = f'WithDraw failed! Case is WithDrawed by {p_case.CaseStatusUserName}!'
                log.error(errStr)
                return ReturnStatus.Error(errStr)
            else:
                log.debug('Can withdraw!!!')

                mycase = MyCase(
                    WSIStudyUID = study.WSIStudyUID, 
                    User_Name = user.UserName,
                    CaseStatus = 'WithDrawed',
                    CaseStatusUserName = user.UserName,
                    CaseStatusUserAlias = user.UserAlias)

                if not mycase.isExist():
                    mycase.add() # called, include sess.commit()
                    study.update(CurrentOwnerUserName=user.UserName, ReadingStatus='In Progress')
                    sess.expire_all()
                    sess.flush()
                    log.debug(f'==================== withdraw complete {datetime.now()}')
                    time.sleep(10)  #
                    return ReturnStatus.Success()
                else:
                    return ReturnStatus.Error(f'MyCase is exist!')

日志:

2019-12-24 09:22:05 [thread:140217765848832] DEBUG Reqeust URL: http://localhost/cronus/cronus/rambo/ChangeStudyDeliverType/ [LarkLog.py:68]
--Return--
ImmutableMultiDict([])
2019-12-24 09:22:05 [thread:140217757456128] DEBUG -------------------------Receive New request: [PUT, from 127.0.0.1] ------------------------- [LarkLog.py:68]
2019-12-24 09:22:05 [thread:140217774241536] DEBUG Reqeust URL: http://localhost/cronus/cronus/rambo/ChangeStudyDeliverType/ [LarkLog.py:68]
2019-12-24 09:22:05 [thread:140217765848832] DEBUG request body:{'StudyDeliverType': 'WithDraw', 'UserName': 'CBTxmsnN', 'WSIStudyUID': 'ZoEpqgPcNQNqtbys'} [LarkLog.py:68]
ImmutableMultiDict([])
2019-12-24 09:22:05 [thread:140217757456128] DEBUG Reqeust URL: http://localhost/cronus/cronus/rambo/ChangeStudyDeliverType/ [LarkLog.py:68]
2019-12-24 09:22:05 [thread:140217774241536] DEBUG request body:{'StudyDeliverType': 'WithDraw', 'UserName': 'VLMSalDu', 'WSIStudyUID': 'ZoEpqgPcNQNqtbys'} [LarkLog.py:68]
2019-12-24 09:22:05 [thread:140217765848832] DEBUG
 [LarkLog.py:68]
2019-12-24 09:22:05 [thread:140217757456128] DEBUG request body:{'StudyDeliverType': 'WithDraw', 'UserName': 'wbjjixTN', 'WSIStudyUID': 'ZoEpqgPcNQNqtbys'} [LarkLog.py:68]
2019-12-24 09:22:05 [thread:140217774241536] DEBUG
 [LarkLog.py:68]
2019-12-24 09:22:05 [thread:140217757456128] DEBUG
 [LarkLog.py:68]
> /root/Cronus_temp/cronus_project/cronus/commons/util.py(345)p()->None
-> pdb.set_trace()
(Pdb) 2019-12-24 09:22:05 [thread:140217765848832] DEBUG ====================lock status:False 2019-12-24 09:22:05.373834 [rambo_res.py:4028]
2019-12-24 09:22:05 [thread:140217765848832] DEBUG ====================lock status:True 2019-12-24 09:22:05.375479 [rambo_res.py:4031]
2019-12-24 09:22:05 [thread:140217774241536] DEBUG ====================lock status:True 2019-12-24 09:22:05.378432 [rambo_res.py:4028]
2019-12-24 09:22:05 [thread:140217765848832] DEBUG start withdraw...UserName:CBTxmsnN, datetime:2019-12-24 09:22:05.382277 [rambo_res.py:4033]
2019-12-24 09:22:05 [thread:140217757456128] DEBUG ====================lock status:True 2019-12-24 09:22:05.388644 [rambo_res.py:4028]
2019-12-24 09:22:05 [thread:140217765848832] DEBUG Can withdraw!!! [rambo_res.py:4052]
2019-12-24 09:22:05 [thread:140217765848832] DEBUG ==================== withdraw complete 2019-12-24 09:22:05.518529 [rambo_res.py:4066]
2019-12-24 09:22:15 [thread:140217765848832] DEBUG response body: {'Status': 'Success', 'Message': ''} [LarkLog.py:68]
2019-12-24 09:22:15 [thread:140217774241536] DEBUG ====================lock status:True 2019-12-24 09:22:15.521476 [rambo_res.py:4031]
2019-12-24 09:22:15 [thread:140217774241536] DEBUG start withdraw...UserName:VLMSalDu, datetime:2019-12-24 09:22:15.527878 [rambo_res.py:4033]
2019-12-24 09:22:15 [thread:140217774241536] DEBUG Can withdraw!!! [rambo_res.py:4052]
2019-12-24 09:22:15 [thread:140217774241536] DEBUG ==================== withdraw complete 2019-12-24 09:22:15.615006 [rambo_res.py:4066]
2019-12-24 09:22:25 [thread:140217774241536] DEBUG response body: {'Status': 'Success', 'Message': ''} [LarkLog.py:68]
2019-12-24 09:22:25 [thread:140217757456128] DEBUG ====================lock status:True 2019-12-24 09:22:25.625992 [rambo_res.py:4031]
2019-12-24 09:22:25 [thread:140217757456128] DEBUG start withdraw...UserName:wbjjixTN, datetime:2019-12-24 09:22:25.633547 [rambo_res.py:4033]
2019-12-24 09:22:25 [thread:140217757456128] DEBUG Can withdraw!!! [rambo_res.py:4052]
2019-12-24 09:22:25 [thread:140217757456128] DEBUG ==================== withdraw complete 2019-12-24 09:22:25.693506 [rambo_res.py:4066]
2019-12-24 09:22:35 [thread:140217757456128] DEBUG response body: {'Status': 'Success', 'Message': ''} [LarkLog.py:68]

可以看到加锁后，第一个请求插入成功，第二个请求开始查询，但仍然找不到第一条插入记录，由于某种原因我不想在这张表上添加组合唯一约束，还有其他解决办法吗？

<小时/>

如果我在 t1.start() 之后添加 pdb stop，则数据库更新成功，t2 和 t3 将按预期返回失败状态。

Answer 1

您需要调用

sess.commit()

之后或代替

sess.flush()

flush() 记录您对数据库所做的操作并将它们保持在挂起状态。commit() 将这些操作持久保存到数据库。当调用 commit() 时，flush() 总是自动调用，因此您可以用 commit() 替换对lush() 的调用，或者可以在flush() 之后添加一个commit()。

如果您想对数据库执行多个操作，您可以在每个操作后使用flush()，以允许数据库验证操作顺序。然而，在您上面描述的情况下，您似乎只执行了一个操作，因此我将简单地将flush()替换为commit()。