Flask-Sqlachlemy parallel requests avoid raise condition-CodePudding

I use one-to-many models of a user having two profile images. The idea is to send out an email once two images have been stored. For this two upload calls are send from the frontend to the backend in parallel.

The issue that I have is that this could lead to a raise condition where the two images are stored but the check for image count fails because each call runs in its own request scoped session/transaction.

My question is now how to reliability execute a image count check for parallel executed flask requests with Flask-Sqlalchemy

class User(Mixins, db.Model):
    __tablename__ = 'user'
    
    first_name = db.Column(db.String(1000), unique=False, nullable=True)
    surname = db.Column(db.String(1000), unique=False, nullable=True)
    email = db.Column(db.String(120, collation='NOCASE'), unique=True, index=True, nullable=False)
    
    images: {} = db.relationship('ProfileImage',
                                 back_populates="user",
                                 cascade='all,delete-orphan')

class ProfileImage(Mixins, db.Model):
    __tablename__ = 'profile_image'
    file_name = db.Column(db.String, unique=False, index=False, nullable=True)
    mime_type = db.Column(db.String, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = db.relationship("User", back_populates="images")

def file_upload(request, user_id: int, side: str) -> int:
    try:

        file = image_service.get_image_from_request(request, side)
        if file and is_valid_mime_type(file.get('mime_type', None)):
            user = user_service.load_user(user_id)
            image = ProfileImage(side=side,
                                 file_name=file.get('name', None),
                                 mime_type='image/jpg',
                                 created_at=datetime.utcnow(),
                                 )
            user.images.append(image)
            db.session.add(user)
            db.session.commit()
            if len(user.images) ==2:
                send_email(user)
            return CREATED
    except ValueError as e:
        current_app.logger.error(repr(e))
    return BAD_REQUEST

CodePudding user response：

If possible I would try to avoid this check. Note that it is also possible they would both send an email which is sometimes worse than not sending any email at all.

... But one way to force synchronization is to use ROW locks. You can't lock rows that are being inserted but you can lock the user's row. Then one image creating task is forced to wait on the other to finish. This can be hard to keep track of and you don't want to be locking all over the place so you should do this as a last resort but SELECT FOR UPDATE works like this:

# User's table row is locked until session is commited or rolled back.
user = session.query(User).filter(User.id == current_user_id).with_for_update().first()
# Or session.query(User).get(current_user_id, with_for_update=True)

# Manipulate the user's profile images.
user.images.append(new_profile_image)

# I'd use a direct query in case the user.images are not accurate for some reason.
image_count = session.query(func.count(ProfileImage.id)).filter(ProfileImage.user == user).scalar()
if image_count == 2:
    send_email(user)
# Release user row lock and commit changes.
session.commit()

This forces one writer to wait until the other writer commits. Read about the postgresql select for update row locking and sqlalchemy's with_for_update interface.