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

Question

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.

Related: [SQLAlchemy: What's the difference between flush() and commit()?](https://stackoverflow.com/questions/4201455/sqlalchemy-whats-the-difference-between-flush-and-commit) — Ilja Everilä, Dec 24 '19 at 07:42
Import statements should be at the top of the file eh, not buried inside functions and classes. — AMC, Dec 24 '19 at 15:09
Also the ChangeStudyDeliverType class contains just a single method, is that required to use the library? — AMC, Dec 24 '19 at 15:37

score 1 · Answer 1 · answered Dec 24 '19 at 05:09

1

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

answered Dec 24 '19 at 05:09

steve

2,488
5
26
39

the `mycase.add()` method include sess.commit() – Kamil Dec 24 '19 at 08:57
It looks like you are trying to add a record via ```mycase.add()``` and update a record via ```study.update()```. Which record is not being persisted to the db correctly, update, add, or both? Also you are not calling commit() on the above ```sess``` object. You are not passing the ```sess``` object when you are instantiating ```mycase``` nor when you call ```mycase.add()```. In addition you are not using a sqlalchemy ```scoped_session``` so if you are creating another session object in the ```mycase.add()``` method, that session will be different than the ```sess``` object here. – steve Dec 24 '19 at 23:48
You'll need to post your update and add methods for completeness. – steve Dec 24 '19 at 23:49
It is complcated to show my base db class which extended `db.model`, `sess` is a session I just created for using, and usually, inside `MyCase`, I always using a `db` object from `Sqlalchemy`, which commit for all derived class for `update()/add()/delete()` @steve – Kamil Dec 25 '19 at 02:31
`MyCase` is a table, `Study` is another table, there both inherit from `db.model` – Kamil Dec 25 '19 at 02:34
If I add a pdb stop after t1.start(), the db update successfully, t2 and t3 will return failed status as expected. – Kamil Dec 25 '19 at 03:09

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

1 Answers1