Reputation: 5462
In the following code, I'm trying to add a new row to TransferRequest
table if there is a row in the table that:
1- the value of partnerId
column is the same as what I expect
2- and the status of this row is now open
and after inserting I want to change the status of the filtered row to the booked
partner = db.session.query(TransferRequest).filter(TransferRequest.id == partner_id).one()
if partner.status == RequestStatus.open.value:
request = TransferRequest()
#set values
db.session.add(request)
db.session.commit()
db.session.query(TransferRequest). \
filter(TransferRequest.id == partner_id). \
update({"status": RequestStatus.booked.value,
"partnerId": request.id})
db.session.commit()
return "success"
else:
return "failure"
The problem here is that when I query for the item with specific id and check its status, after reaching the if
condition it is possible that someone else changes the status of the row from open
to booked
. In this case, I must not insert the row and must not do the update
command after the insertion.
I'm looking for a way that I can do all of these 3 queries in a transaction so that all the data situation lock from the first statement to the end statement. (or of-course any other solutions that can fix the problem)
Upvotes: 0
Views: 730
Reputation: 14769
I think the main problem in your code is using commit() twice. Which commits everything to the database (as a transaction) and then releases underlying transaction locks. The second thing is, I should also add with_for_update() to your select query, to lock specific rows. Finish off with a one-time commit to persist your changes and release locks. An example could be:
session = sessionmaker(bind=engine, autocommit=False)
partner = session.query(TransferRequest).with_for_update().filter(TransferRequest.id == partner_id).one()
if partner.status == RequestStatus.open.value:
request = TransferRequest()
#set values
session.add(request)
session.flush()
session.query(TransferRequest). \
filter(TransferRequest.id == partner_id). \
update({"status": RequestStatus.booked.value,
"partnerId": request.id})
session.commit()
return "success"
else:
return "failure"
Upvotes: 1