如何在SqlAlchemy中通过事务避免TransferRequest并发修改问题？

The issue you're facing is a classic race condition: between the time you query the TransferRequest row and update its status, another process could have already flipped the status from open to booked, leading to unintended inserts and invalid updates. To fix this, we need to make the entire sequence (check status → insert new row → update original row) atomic, using database row-level locking and a single transaction.

What's Broken in the Original Code? #

• The code commits twice, splitting the operation into two separate transactions—this creates a gap where other processes can modify the row.
• The status check happens after fetching the row, not as part of the initial query, leaving room for concurrent changes to slip through.

Solution: Row-Level Locking + Atomic Transaction #

We'll use SELECT ... FOR UPDATE to lock the target row the moment we query it, ensuring no other transaction can touch it until our operation finishes. We'll also wrap all changes in a single commit to guarantee atomicity.

Optimized Code #

from sqlalchemy.exc import NoResultFound

try:
    # Query the row with a lock, and include the status check directly in the query
    partner = db.session.query(TransferRequest).filter(
        TransferRequest.id == partner_id,
        TransferRequest.status == RequestStatus.open.value
    ).with_for_update().one()

    # Create and populate the new TransferRequest record
    new_request = TransferRequest()
    # Set required fields for new_request here (e.g., new_request.partnerId = partner.partnerId)
    db.session.add(new_request)

    # Update the original row's status and partnerId
    partner.status = RequestStatus.booked.value
    partner.partnerId = new_request.id

    # Commit all changes in one atomic step
    db.session.commit()
    return "success"
except NoResultFound:
    # Either the row doesn't exist, or its status was already changed from open
    db.session.rollback()
    return "failure"
except Exception as e:
    # Catch any other unexpected errors (e.g., database connection issues)
    db.session.rollback()
    # Log the error here if you need to debug later
    return "failure"

Key Improvements Breakdown #

• with_for_update(): This adds a row-level lock to the query. No other transaction can modify or lock this row until our transaction is committed or rolled back—eliminating the race window entirely.
• Status Check in Query: By including TransferRequest.status == RequestStatus.open.value in the filter, we ensure we only fetch rows that are still open. If the status was changed by another process, the query returns no results, and we exit cleanly.
• Single Commit: All changes (insert new row + update original row) are applied together. If any step fails, the entire transaction rolls back, keeping your data consistent.
• Error Handling: Proper rollbacks ensure the database session doesn't get stuck in an invalid state if something goes wrong.

内容的提问来源于stack exchange，提问作者Husein Behboudi Rad