CODEWITHSUNDEEP
pythonmysqlmultithreadingsqlalchemy
Kamil
Kamil

Reputation: 644

sqlalchemy not update after insert if request the same flask api in 1/100 seconds

I just want to call a flask api to insert reocord in db, but when I request it in threads in 1/100 second(log shows), it not update when each insertion has been completed even after I add a lock:

test.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()

view.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!')

log:

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]

You can see after add lock, first request insert successfully, and the second request start to query, but still cannot find the first insert record, I don't want to add a combine unique constrain on this table for some reason, is it have other solution?

If I add a pdb stop after t1.start(), the db update successfully, t2 and t3 will return failed status as expected.

Upvotes: 0

Views: 376

Answers (1)

steve
steve

Reputation: 2638

You need to call

sess.commit()

either after or instead of

sess.flush()

flush() records the operations you are doing to the database and keeps them in a pending state. commit() persist these operations to the database. flush() is always called automatically when commit() is called so you can do either substitute your call to flush() with a commit() or can can add a commit() right after your flush().

If you wanted to do multiple operations to the database you might use flush() after each operation to allow the database to validate the sequence of operations. In your case described above however it looks like you're only doing a single operation so I would simple replace flush() with commit().

Upvotes: 1

Related Questions